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

causing problems.

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

Online.

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!