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
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
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
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