In October I wrote Snapshot
Replication to discuss why snapshots are an option worth considering even
though they are a brute force solution compared to transactional or merge
replication. From what I've seen replication tends to be under used and viewed
as 'magic'. My hope is that by looking at what goes on behind the scenes we can
dispel the appearance of magic and once you see how and why it works, the
chances of using it to solve problems increases. Replication (or at least the
replication built into SQL) isn't the right answer for problems, sometimes DTS
or a custom replication scheme will make sense.
This week I'd like to walk you through how to set up replication on your
server. We won't actually get anything replicating yet, just do the set up part.
It's not complicated and the wizard does a ton of work for you, but there are
some points where you'll want to understand the implications before clicking
next - mainly so you don't end up having to redo something later. This article
has more images than usual since I thought seeing the wizard steps would be more
fun than just talking about them.
To get started, you'll need a version of SQL installed that supports being a
publisher (MSDE and personal edition do not). There's not much danger in trying
replication, but I still recommend you install two named instances on a test
machine so that you can add/remove replication several times without any fear of
For those of you who disdain GUI's you can do it all from Query Analyzer, but
as you'll see it's a one time chore and the wizard does it well. Let's get
started. Open Enterprise Manager (note: all examples done in SQL 2000, it is
similar in SQL 7), click Tools, Replication, Configure Publishing, Subscribers,
and Distribution, should see the following, click next.
The first decision point is whether or not to have the server act as it's own
distributor. Very generally speaking this is the right thing to do. In a very
high transaction environment you may want to place the distribution database on
a different server, maybe even devote a server to just handling that one
database. The distribution database is where SQL stores all the commands and
data (more or less, more on this later) to be sent to the subscribers.
The next option is the path for the snapshot folder. By default it creates a
folder called repldata and that is usually good enough. If you're going to FTP
the data to the subscribers you may want to change this to exist on the FTP
server to avoid granting FTP access to your data server. Snapshots are used by
all three types of replication and consist of script files to create the tables
and indexes, plus one bcp file per table. You don't have to do a snapshot - you
can do a restore on the subscriber instead - but most of the time it will be the
way to go. For now, use the default location.
Depending on the path you entered, you'll probably get this warning. The
reason is that all the subscribers will need access to the folder. As long as
the SQL agent account has access to the folder, you're in good shape. Don't
worry about it for now, just click Yes.
At this point you can really click No and finish, let SQL do the rest. I
recommend you go the long route, clicking Yes so you can configure everything.
Here is one big reason why. If you want to change the name of the
distribution database, now is the time to do it. You can do it later if you had
to, but why? Probably the name doesn't matter much to you (and really, I'd
encourage you to stick with Distribution, easier for the next DBA to figure out
what is where), but the file location does. For this example I'm running on a
test machine with only one drive, more typically you'd want to put the log file
on a separate drive.
In the next step you decide which servers can use the server you're working
on as a distributor. Naturally you'll need to enable the server itself. You can
enable other servers to use it too (if you're going to have other servers that
just publish and have distribution db elsewhere). This is easy to change later
on, just select the server you're using.
If you click the ellipsis you can configure the security account for the
publisher and change the snapshot folder. Impersonating the agent account is the
way to go, one less thing to break when passwords change (default setting).
The next step is to enable databases for either transactional or merge
replication. For now we'll select nothing, this is another step that is easy to
do afterward. Note also that nothing special is required for doing snapshot
replication, the reason is that no special objects need to be added to the
database to support it.
Next you get to enable subscribers. For this example I've used my second
named instance. Easy to change later on if you need to. You might want to set
the publisher to be a subscriber as well, this gives you the ability to do
replication entirely on the same server, perhaps replicating Northwind to
Northwinds2 so you can do testing on a single instance.
Again you have the option to configure some extra info for each subscriber. I
recommend sticking with impersonation on the first tab and going with the
defaults on the second tab. The scheduling part is easily changed later and for
smaller setups the defaults are perfect.
Click next and you're ready to finish. Do it!
The biggest change that happens now is the distribution database gets
created. When you create a new database it is really a template of model, for
the distribution database it makes a copy of distmdl.mdf and attaches it. Once
the wizard is one, you'll get one final dialog. If you check Enterprise Manager
you'll see a new node added for monitoring replication, we'll explore it further
in a follow up article. For now, just click ok.
Removing replication is a lot easier. Start the disable wizard, select Yes to
disable, click Finish, you're done!
If you're new to replication that might seem like a lot considering we're not
moving any data yet. Run through it a couple times, look at what gets changed,
run Profiler to watch the changes made. On my test machine it resulted in 50 or
so stored proc calls. Take a look at some of the stored procs called in Books
Next time we'll revisit snapshot replication before moving on the fun stuff!
If you've made it this far, please take another minute to rate the article and
add a comment using the link below. Thanks!