Adding article without running the entire database snapshot in Transaction Replication:-

Adding article without running the entire database snapshot in Transaction Replication:-

If Transaction replication was already setup and If we want to add new articles, then a Snapshot has to be generated and applied to the Subscriber, but by default snapshot of all the existing and new articles will get generated and applied to the Subscriber, which is not what we want as it can take long time when the database is large and causes Subscriber to be unavailable while applying the Snapshot. There is a way in which we can avoid this behavior and just generate the Snapshot of the newly added articles and apply them to the Subscriber, which will not cause any problem to the existing articles which can be available during this process.

Let’s see how we can achieve this.

Run the below commands on the Publication database

1	sp_helppublication

If the columns “immediate_sync” and “allow_anonymous” are having a value of 1(Enabled) for both of them, the Snapshot of all the articles will be generated. As we do not want this behavior, we will change these values to 0(Disabled) for both the “immediate_sync” and “allow_anonymous” options.

We can disable these options by running below commands on Publication database

1 2 3 4 5 6 7 8 9 10 11

EXEC sp_changepublication @publication = 'your publication name', @property = 'ALLOW_ANONYMOUS' , @value = 'False' GO   EXEC sp_changepublication @publication = 'your publication name', @property = 'IMMEDIATE_SYNC' , @value = 'False' GO

Now, we can go ahead and add the new article to the publication from GUI, and then we need to start the Snapshot Agent, which will generate new Snapshot just for the new articles. If we have not run the snapshot agent then the newly added article will be in Subscribed state, but will not be active, Inorder to make this active, we need to run the Snapshot agent job.

We can check article status on Subscriber database by running the Subscriber database.

1	sp_helpsubscription