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

• Open SSMS connect to your publisher Server (In my case, it is MAWSQLTEST2A\TEST2012K)

• Go to “Replication” folder & expand the replication folder after that expand the “Local Publications” folder

• Right click on the publication where you want to add the new article, and open the “Properties” (In my case, the publication is “ForestPub”)

• Click on the “Article” tab and Uncheck the option “Show only checked articles”, it allows to show all the articles which are not being published. In our example, we only have one article (Foresttbl1) that is already published.

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

• Select the article which you want to add in the publication. In our case, I am going to add the article “Foresttbl2” in the existing publication “ForestPub”

• Run the below query on the your Publisher Database and check;
  • The columns “immediate_sync” & “allow_anonymous” should contain the value 0 for the publication (ForestPub) where you added the new article
  • The column “subscription status” should contain the value 1 for the article which you added

    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

• Now, run the “Snapshot Agent” to initialize the newly added article. Form the below snapshot, you can see that only one article got initialized not both the articles.

• Let’s verify the subscriber side table creation time of the articles.

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 .