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!!!
8 thoughts on “Add new articles to existing Transactional Replication without initializing old articles”
thank you for providing such a wonderful information
Excellent Article and it helps me a lot. Thanks for sharing this document.
Good Article – very clear explanation
Good article
Thank you Veeru, Siva, Sandeep and Rajesh your great feedback!
is there any way without run snapshot agent the articles replicate to subscriber
Hi Dilip, you need to go with replication support only option.
It is a nice article, thanks for sharing.