Have you got transactional replication in your SQL environment? Do you need to add a new table to your publication, but can't afford the time necessary to create a full new snapshot? Here is a step-by-step guide. Thankfully, adding a single table is easier than I thought.

Here we will use my playbox database, WorldOfMayhem. Currently, the players and the ticktick tables are replicated. We will add the dragons table and the weapons table.

Setup - My Test Replication Environment

First, I will show you what happens if you just go and add this table to the publication articles.

Let's add the weapons table. Right click your publication, go to articles and untick the option to only show included articles. We are going to tick the weapons table and then click OK. This is shown below.

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 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 were part of the snapshot, only the weapons table is actually pushed over to the subscriber. 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. This is the same as the procedure shown above for adding the weapons 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 Replication Monitor and head to your snapshot folder. You will see a very different scenario this time. There is only one table in the snapshot folder.

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 stored procedures I got a number of different errors.

1) using sprocs, 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.