http://www.sqlservercentral.com/blogs/sql-geek/2017/07/10/add-new-articles-to-existing-transactional-replication-without-initializing-old-articles/

Printed 2017/07/26 05:38PM

Add new articles to existing Transactional Replication without initializing old articles

By Dharmendra Keshari, 2017/07/10

Suppose, you have Transactional Replication configured in your production environment. There is a business requirement to add a new article to the existing publication, and you would like to initialize only the newly added article instead of all articles in the publication.

To achieve the above requirement, I will be using below-mentioned Publisher Server, Publication Database, and Subscriber Server to demonstrate it.

Publisher Server: MAWSQLTEST2A\TEST2012K
Publication Database: Forest
Subscriber Server: MAWSQLTEST1B\TEST2012L

Adding a new article

   Note: only article with Primary key can be included into the publication

    Follow the link Capture Important Parameters of the SQL Server Replication to all the columns definitions.

/*-------------------------------------------
----Run this query on the Publisher Server
--------------------------------------------*/
USE --<Select Publisher Database>
SELECT
DB_NAME() PublisherDB,
sp.name as PublicationName,
sp.immediate_sync,
sp.allow_anonymous,
OBJECT_SCHEMA_NAME(sa.objid, db_id()) as ArticleSchema,
sa.name as ArticleName,
s.status [subscription status],
CASE s.status
       WHEN 0  THEN 'Inactive'
       WHEN 1  THEN  'Subscribed (Not Published)'
       ELSE 'Active (Published)'
END AS [subscription status desc],
sa.pre_creation_cmd,
CASE sa.pre_creation_cmd
       WHEN 0  THEN 'none'
       WHEN 1  THEN 'drop'
       WHEN 2  THEN 'delete'
       ELSE 'truncate'
END AS pre_creation_cmd_desc,
UPPER (srv.srvname) AS SubscriberServername,
case s.sync_type
             when 2 then
                           case s.nosync_type
                                  WHEN 3 THEN 5
                                  WHEN 2 THEN 4
                                  WHEN 1 THEN 3
                                  else 2
                END
                     ELSE s.sync_type
END as sync_type,
 
case s.sync_type
              when 2 then
                 case s.nosync_type
                    WHEN 3 THEN 'initialize with lsn' 
                    WHEN 2 THEN 'initialize with backup'
                    WHEN 1 THEN 'replication support only'
                    else CAST (2 AS VARCHAR (2)) +'none'
                 END
                       when 1 THEN 'automatic'
              ELSE CAST (s.sync_type AS VARCHAR)
END as sync_type_desc
FROM dbo.syspublications sp
JOIN dbo.sysarticles sa ON sp.pubid = sa.pubid
JOIN dbo.syssubscriptions s ON sa.artid = s.artid
JOIN master.dbo.sysservers srv ON s.srvid = srv.srvid
go

The output of the above query:

Click on the image to zoom it

We have successfully initialize only the newly added article of the publication instead of a total snapshot of all existing articles in the publication!!!

The post Add new articles to existing Transactional Replication without initializing old articles appeared first on .


Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.