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!