SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Snapshot Replication for the Beginner - Part 2

By Andy Warren,

This is a follow up to an earlier article called Snapshot Replication for the Beginner. This week we'll actually get some data pushed to a subscriber! At the end of the previous article we had completed construction of a publication containing one table, so we'll pick up from there.

Start by right clicking the publication and selecting properties.

That brings up the following dialog with a LOT of tabs.

A lot of this allows you to see/change options we selected when we built the publication. Worth discussing, but let's get some data moving first. Click on the Subscriptions tab. Then click Push New to start the Subscription Wizard. I've elected to look at advanced options as we go.

Here we need to pick a subscriber. I'm going to use EG\ONE which is the same as the publisher. If the subscriber is not listed, you can go stop, go back to the main replication menu in Enterprise Manager to add it, then restart the wizard.

Now we need to select the subscription database. I've already created NorthwindSnapshot, but you can do Create New if you need to. It's better to do the creation of the database before running the wizard so that you can control file placement - doing it here uses the defaults.

Next you have to set a schedule for the Distribution Agent. If you're doing transactional or merge you'll normally want it to run continuously, for snapshot it makes sense to set a schedule that would run slightly after the snapshot job runs. Having the job run more often won't hurt anything, it just checks, finds no data to push, ends gracefully. We'll go with the default schedule for now.

Clicking next brings us here. Because this is a brand new subscription, we want to initialize both schema and data. I didn't check it for the image, but you may want to also check the box to start the snapshot immediately. Whether you check it depends on what impact generating the snapshot will have on your server. As I mentioned in the previous article, doing a snapshot places an exclusive lock on each object during the BCP. If you don't check the box, it will occur at the next scheduled time for the snapshot job.

The next step is no big deal, can't generate snapshots or distribute if the agent isn't running!

Click finish to finish! Then click Ok to close the publication properties.

The process of creating publication and subscription has created two jobs.

You can see that SQL even has two special built in categories, REPL-Snapshot and REPL_Distribution. You can run these at any time, change the schedule, etc. Now if you look further down in Enterprise Manager to the Replication Monitor, you'll see your publication and the two jobs. Here the snapshot has run successfully, the distribution agent (EG\One:NorthwindSnapshot) has not yet run.

Right click the snapshot job, select agent history.

Here you can see the job ran once yesterday but there were no subscriptions, then again today where it actually generated a snapshot. In the next image we'll look at session details.

This should give you a better feeling of what really happens. The schema script and BCP data file are written out to the replication folder we specified earlier (see next image), then distribution db is updated so that when the distribution agent runs, it knows a subscriber is waiting on the snapshot and the data is available.

Note here that there are two script files (plain text, not Schedule+ !), one for the objects, another for the indexes.

Now back to EM, we'll run the distribution agent right from the Replication Monitor.

Once it completes, click Agent History to see what happened. Big point here (well, depending on your needs) is that the index file gets applied before the data is loaded. If you have a large amount of data it may make more sense to load the data then index it, especially if you're going to apply a different index plan.

Now if we check NorthwindSnapshot, we'll see the Categories table (the only one in the publication), plus a couple new system tables, MSreplication_subscriptions and MSsubscription_agents, both used to keep track of where/how the data  came from. More detail on them in a later article.

You can now run the snapshot and distribution agent as often as you need to push new data to the subscriber. If you reach a point where the subscription is required, start by making sure that both jobs are stopped (and better yet, disabled). You'll get ugly errors if you drop a subscription while the jobs are running. Back to the publication properties, click on Subscriptions, then highlight and delete the subscription.

You'll then get the following warning dialog.

Click Yes. This ONLY removes the subscription, not the objects/data you've previously replicated to the subscriber. It's up to you to do the clean up if you no longer need it.

So there you are. There are a bunch of bells and whistles (not complaining, they are handy), but snapshot replication is really just a way to automate the moving of objects and data from one server to another in just about the most efficient manner. When it comes to pure speed, BCP is the way to go!

So what do you think so far? Rate the article and throw in a comment, its good karma!

Total article views: 7703 | Views in the last 30 days: 4
Related Articles

Snapshot Agent Failure

Snapshot agent is not running


Replication: Distribution Agent Monitoring

Learn about the distribution agent and how to identify replication bottlenecks.


Snapshot agent never started

Transaction snapshot agent never started


A pull subscription to a Snapshot Publication (SS2005)

Creating an initial snapshot when I don't administer the Distribution Database


Snapshot Agent in Transactional Replication

Function of Snapshot Agent in Transactional Replication

sql server 7