Have you got transactional replication in your SQL environment? And do you need to add a new table to your publication, but can’t afford the time necessary to create a full new snapshot? Then this article is a blow-by-blow how to just for you! Sometimes it is just easier to reinitialise the whole subscription, but at 11 PM that’s the last thing you want to do. Thankfully, adding a single table is easier than I thought.
Setup – My Test Replication Environment
First, I will show you what happens if you just go and add this table to the publication articles…
Full Snapshot Example
Now, right click your publication. Go to “View Snapshot Agent Status” and start the snapshot agent. If you head to the replication monitor you will see the following:
Look closely at this screenshot. The nice thing is that only the weapons data has been applied to the subscription, you can see this in the replication monitor. However, the Snapshot Agent Monitor clearly shows that a snapshot of 3 articles was generated. And if you look in your snapshot folder, you can see that all three articles are there – and these files are bcp files and contain the table data.
The good news, is that even though all the articles where part of the snapshot, only the weapons table is actually pushed over. So if you have a local snapshot folder, this isn’t a terrible solution. But, if your snapshot folder is on a remote drive then you are potentially going to push a lot of data over the network.
Let’s fix this so that the full snapshot is not necessary.
Step 1 Change the publication properties
All you have to do is disable 2 publication properties: allow_anonymous and immediate_sync. Here’s how:
exec sp_changepublication @publication=N'testWOM', @property=N'allow_anonymous', @value='false'; go exec sp_changepublication @publication=N'testWOM', @property=N'immediate_sync', @value='false'; go
Step 2 Add your article
Now go back to the properties of the publication and add in a new article. This time, we will add the dragons table.
Step 3 Start the snapshot agent
Once you have added your article, right click the publication again and select “View Snapshot Agent Status” and start the snapshot agent. Fire up the replication monitor and head to your snapshot folder. You will see a very different scenario this time:
And that’s all there is to it. Very quick, low impact and makes for a happier change team in the small hours of the morning.
Update – What Didn’t Work
Thought I should add a note about what didn’t work when I was trying to do this. Using the GUI was the key. When I tried to add the article to the publication via the stored procedures I got a number of different errors.
1) if I did not drop the subscription, then I would get an error when I went to run the snapshot agent. It didn’t not pick up that there had been changes to the publication. There is probably a way around this, but I didnt’ find it.
2) when I did drop the subscription, then add the article to the publication, then recreate the subscription -> a full snapshot was generated. No good.