SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Getting Ready to Replicate

By Andy Warren,

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!

Total article views: 13004 | Views in the last 30 days: 4
Related Articles

changed server name caused replication setup issue

changed server name caused replication setup issue


Common table in two databases/servers - Replication

Common table in two databases/servers - Replication


Replication of replicated database

Replication of replicated database to a distant server


Replication issue

Database 'DB_NAME' contains no objects that can be replicated with the selected publication type. Cl...


Changing Replication Passwords

A how-to guide for configuring replication agents after a password change

sql server 7