Category: replication

How to add article to replication with out reinitializing snapshot

Step1:

exec sp_changepublication
@publication = ‘replication_publisher’,
@property = ‘allow_anonymous’, @value = ‘False’

exec sp_changepublication
@publication = ‘replication_publisher’,
@property = ‘immediate_sync’, @value = ‘False’

By default when we create replication,the above highlighted properties will be true which means generate entire snapshot whenever snapshot agent runs.so change them to false.PFB link for more details

https://msdn.microsoft.com/en-us/library/ms188413.aspx

Step2:

next step is to add article through GUI or script.

Once you created table,to add from GUI,right click the publication properties and uncheck show only checked articles,so that all articles will appear

Capture

step3:

run snapshot agent( dont say reinitialize subscriptions,since it generates snapshot for all articles).Verify snapshot agent generated snapshot only for one article
select * from MSsnapshot_history

[100%] A snapshot of 1 article(s) was generated.

step4:

Once distribution agent runs,we will be able to see new article in subscriber

Note log reader also should be running ,otherwise we will get below error

The concurrent snapshot for publication ‘replication_publisher’ is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted,