Adding a table (article) to transactional replication without regenerating (recreating) the entire snapshot.

I’m using transactional replication to move data off of a production server and on to a reporting server to keep the load off production when writing real time reports. From time to time, I will get a request to add a table to the replication set so that it can be utilized in various reports. This has typically been done after work hours because adding an article meant recreating the snapshot, which sometimes took more than an hour. Then I started thinking there has to be a better way to add a table without the entire snapshot being recreated, and it turns out there is, I just had the replication setup incorrectly.

When setting up transactional replication thru the SSMS GUI, there are many options hidden from you or are worded in a way that is counter-intuitive (at least to me anyway). One of those options is “Create a snapshot immediately and keep the snapshot available to initialize subscriptions”.

At first glance, I thought “Sure I want to create the snapshot immediately after I create the publication since I want to transfer it to the new subscriber as soon as possible”. In the background this sets the @immediate_sync in the exec sp_addpublication to TRUE so that ALL the synchronization files are created or recreated EACH time the snapshot agent is ran. After the publication has been created you can confirm this by running:

   exec sp_helppublication ‘<publication_name>’

Look at the immediate_sync column, you should see a 1.

Now, how do we fix it to allow for the addition of a new table (article) without having to generate the entire snapshot? We have to change 2 things, immediate_sync and allow_anonymous on the publication.

NOTE: You must also have an independent, stand-alone distribution agent for the publication.

exec sp_changepublication

@publication = '<publication_name>',

@property = 'allow_anonymous', @value = 'False'

exec sp_changepublication

@publication = '<publication_name>',

@property = 'immediate_sync', @value = 'False'

After these options have been set on the publisher, you will be able to add an article via the GUI or script and transfer it over to the subscriber without generating the entire snapshot. From now on, after a table (article) is added and the snapshot agent is ran, the snapshot will only contain the new table that has been added. If you choose to REINITIALIZE your subscription before you create the snapshot or have new subscriptions waiting to be initialized, the ENTIRE snapshot with all the articles will still be created.