Monday, August 31, 2015

How to generate smaller snapshots when adding new articles to SQL Server replication

We had SQL Server transaction replication configured in our environment.  I wanted to add new articles to the publication.

To do this efficiently, I wanted to initialize only the new articles.  I wanted to avoid taking a full snapshot of all the existing articles all over again.

I googled around and found a pretty simple way in which this can be achieved.

How Do They Do It!

This can be achieved in four simple steps.

Add the new articles to the transaction replication setup

To add new articles to the publication either you can use the Management Studio interface or use the following script

Turn off "allow_anonymous" and "immediate_sync" properties on the publication

To take a smaller and faster snapshot this is the single most important step.  We need to disable "allow_anonymous" and "immediate_sync" properties of the publication.  To do this use the following script

Start the Snapshot agent

We need to start the snapshot from the replication monitor as follows

Once this agent completes you will notice that it has created a snapshot with only the newly added article.  This is super fast and saves disk space as well.  This is how it looks after the Snapshot agent has finished running.

Turn on "allow_anonymous" and "immediate_sync" properties on the publication

This is the last and final step which reverses the Step 2.  The script to do this is also exactly opposite of what we did in Step 2

For bigger databases it saves a hell lot of time.  I hope this saves you some time as it did for me!

Have some Fun!