If you haven't already, you might want to look at the two previous articles
I've written recently on replication (Snapshot
Replication and Getting
Ready to Replicate) that set the stage for this one. This week I'm going to
return to snapshot replication, discussing how to do a basic implementation and
pointing out some of the more interesting points along the way. Snapshot is a
great way to start learning replication before moving into the more complicated
areas of transactional and merge. For this article I'm going to replicate
Northwind from one named instance to another. You can of course replicate to
another server or even to a different db on the same server.
Just to be clear before I start, snapshot replication is generally a way to
provide a read only copy of the data to one or more subscribers. Updates on the
publisher are not sent over, you have to send a whole new snapshot to refresh
the subscriber. One possible use for this is lookup tables that rarely change.
Using Enterprise Manager (SQL2K), here is where we start:
Pick the database name that will be the source (though it doesn't matter, you
get the option to change a few clicks further along), then click Create
Which starts the publication wizard. Replication consists of two parts,
publications and subscribers. Publications define who and what data will be made
available to subscribers, while subscribers can then...subscribe!...to get a
copy of the publication. Really a pretty clear metaphor for what happens. I've
selected advanced options so we can see the additional choices you can make, for
a very basic publication you can leave it unchecked of course.
Told you! Get to pick the database again.
Here are our choices. We'll go with door #1, snapshot replication.
Now here are two VERY cool options we'll wait to explore further until next
time. While we think of a snapshot as being a read only copy (because changes
would be lost when the next snapshot is issued), by using one or both of these
options we can let users write to the subscriber, then have those changes posted
back to the publisher.
Another interesting option - we can use DTS to transform the data. Definitely
an advanced option, this can be used in any way you think to write code. A not
very good example might be that while you represent prefix, first name, and last
name as separate columns in your contact table on the publisher, you want to
treat it as one column on the subscriber, so you define a package to concatenate
and format the data as it's sent to the subscriber. Something else we'll return
to in a later article.
Here I usually only select what I need, in this case SQL2K. In a follow up
(sorry to keep saying that, but too much to cover in one article!) we'll send a
snapshot to Access to see how that works.
This is where we do the real work. You can select whichever objects you need
to send to the subscriber and in later steps, even apply filters. I don't
normally replicate stored procedures for snapshot replication (more useful in
transactional in my opinion) but if you need them on the subscriber (perhaps
because you're selected the updateable subscriber option a few steps back)
select them. Views are more likely to be needed.
A huge point in building your publication is to think about the impact that
running the snapshot will have on the publisher and the subscriber. Every time
you run the snapshot job it will lock the tables while it BCP's the data out. On
small tables your users will never notice, on large tables it can take long
enough to bring operations to a halt. It's nice to have one publication that
contains everything, but it's often more practical to define one publication for
all the small tables, separate pubs for each of the larger tables. Another twist
might be to put all the tables that change frequently in one pub, ones that are
more static in another. Issues caused by the table locking can be minimized by
running the snapshot off peak, but I'll warn you now - sooner or later you'll
need to do it during the day!
To keep this simple, I'm just going to replicate the categories table. Can't
see it in the image, but I've checked the box to the left of it. The button to
the right holds some interesting prospects as you'll see in the following image.
Destination owner is something you really want to consider. It
defaults to being whatever the owner is on the publisher. I keep all objects
owned by dbo so that works for me. Even if not owned by dbo, it might make sense
to preserve the same owner on the subscriber. Might not. If you need to change
it, this is the place.
Here is the other tab. The default behavior of a snapshot is to drop and
recreate the table each time along with the indexes and primary key. In a lot of
cases this makes sense, as you make schema or index changes on the publisher,
the next time you snapshot the subscriber gets the changes. Keeping the existing
table unchanged will save you some disk/network io if you KNOW the contents
won't change. State look up table maybe? Deleting data that matches the row
filter is interesting. This would let you push over the data you want, yet leave
other data in place. This can get a little weird if you ask me! Never had a need
for it yet. The delete all data option is nice if you want to preserve
schema/index changes made on the subscriber, I guess also saves the minor
overhead of dropping/creating the object each time.
I hardly ever write to a snapshot copy, so RI and triggers are usually not an
issue. If you're doing updates AND posting them back, they become much more
important. Converting user defined types to base types saves having to add your
user defined types to each subscriber. I'll confess to not using UDT's very
often, doesn't seem like it's much work to add them to the subscriber if you
Click Ok, Next, brings you to this warning window. I've selected at table
that contains an identity col AND make the publication not updateable. Identity
columns on updateable subscribers will give you a large headache, more next time
(or the time after maybe!). In this case clicking next is all we need to do.
A good name is worth a minute. If you only have one pub, I just use the name
of the database. If more than one, I might use 'Northwind-SmallTables' and 'Northwind-LargeTables',
something to let me see the purpose rather than the content. I rarely use the
description block and just leave the default. The option to list in Active
Directory is grayed out because I'm working at home (not on a domain). Listing
in AD is a gee whiz feature as far as I can tell! Most subscribers are either
set up by the DBA or told by the DBA what to click, not many head out to AD to
see what is available.
Just to keep it interesting we'll define some filters.
Both horizontal and vertical filters will be applied - configuring both the
select (defaults to all columns) and the where clause (defaults to all rows).
Columns are easy, just check/uncheck to meet your needs. Note that you have
to replicate the primary key. I don't know when it would make sense not to
replicate it, but as long as the subscriber is not updateable it wouldn't
matter. Just live with it!
Next is rows. For each table you need to filter, click the little box to the
Part of it pre-populated, I just typed in the 'categoryid > 5' part.
Click ok to see the updated display.
Books online gives the following reasons for allowing anonymous subscribers -
you have a large number of subscribers, you want to eliminate the extra overhead
of maintaining the subscriber info on the publisher or distributor, or you have
subscribers that will use the internet. Obviously if you allow anonymous
subscribers you're giving up control of who sees the data. Not always bad, but a
choice to be made carefully. We'll with named for now.
Almost done with this part, we can now configure how often the snapshot agent
(a SQL job) runs. The default is once a day. Remember the warning about table
locking, pick a time that will cause the least problems.
Click Next and you're almost...done! Click close and you'll have successfully
created your publication.
Back in Enterprise Manager you'll see that the database icon has changed,
plus you now have a Publication folder containing the Northwind publication we
You can now delete the publication by just right clicking it and selecting
delete. I know that seems like a lot and we still haven't pushed any data to a
subscriber, but once you run through it a couple times it becomes pretty fast
and intuitive. Next time we'll push this publication to a subscriber, then
explore some of the other options I had to gloss over this time. Too much
detail? Not enough? Rate the article and add a comment, contribute to the