Adding New Articles to Transactional Replication without Generating Snapshot of All Articles

SQLServerF1

In Transaction replication which was already setup and If we want to add new articles, then a new 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

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

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.

sp_helpsubscription

Do not follow these steps directly on production environment, please test the solution on test server to get comfortable and test if it is working as expected.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Leave a Reply

Your email address will not be published. Required fields are marked *