SQL Server DBA / sqlLAB

Replication – adding a new table without a full snapshot

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

wom_initialAs usual, I 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.

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

 

 

 

 

 

Full Snapshot Example

add_weaponsLet’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.

 

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:

full_snapshot

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:

partial_snapshot

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s