SQL Server Reference Guide

Replication is a feature included with all editions of SQL Server from version 2000 and higher (to some degree) that allows you to copy data from one database to another. In this feature overview I’ll explain (at a high-level) the concepts, how the process works, and explain a practical example of the way I’ve implemented it in the past. In future tutorials I’ll walk through setting up replication from one server to another that you can test using only a single system.

The basic process for setting up replication is to define the way you want to move the data, the servers that send and receive it, and then the data you want to copy. In this tutorial, we’ll examine the process for planning the replication setup, called a replication schema.

Concepts Overview

Microsoft uses a publisher-subscriber metaphor to describe their implementation of replication. In other words, think of a magazine and someone who buys the magazine. There are several parties involved in making the magazine, getting it to the reader, and reading it. This is a pretty common way of replicating data, whether a database or some other function. Here is the structure Microsoft uses boiled down to a single sentence:

A Publisher has a Publication which is composed of Articles that are sent (picked up) by the Distributor to one or more Subscribers which have Subscriptions.

Yes, it’s kind of simple, but keeping it simple like that will help you understand replication when things get a bit more...complicated. I’ll use that sentence to guide you through the rest of this article.

The Microsoft documentation starts out by planning the physical layout of the process (servers-out), then the data. I normally ask questions that begin the plan at the other end (data-in), and then decide the physical layout based on the answers. It really doesn’t matter which route you choose, so long as you create your plan before you start setting up the servers. Microsoft provides a full set of wizards to guide you through the entire setup process on all parts of the replication schema, but you should understand what you want to accomplish before you run them.

I’ll begin by defining some of the terms I introduced in that paragraph earlier. Along the way I’ll add a few more terms. You can install all of these functions on one system, but in production you should have multiple servers involved in each part of the process. You’ll want to pay attention to what purpose the server will be used for, and adjust the number of servers based on the load. For instance, in a large replication farm, you might have one server or even multiple servers whose only role is one function within the process. In smaller installations, one server might perform two or more roles.

The Publisher

Starting from the data you want to send, the first system in the chain is the Publisher. The Publisher is the SQL Server Database Server that has the data you want to send to other systems. As you progress through the Replication setup wizard, you’ll be asked which system will act as the Publisher. The Publisher has one or more databases that hold the data that you will send to another system.

In the magazine example, this is like the “head office” that has all of the articles for the magazine in it. Not everything a writer types goes in a particular magazine, but we’ll come to that.

Publications and Articles

The Publication is the grouping of data that you want to replicate. You’ll create a Publication during the second phase of the wizard, and once the Publisher is configured you’ll start the wizard by creating more Publications. A Publication is composed of one or more Articles. You’ll always work with the data from here on out at the level of the Publication.

Articles are the individual data parts that make up a Publication. An Article can be a table, or the results of a stored procedure or a view. In this way the Publisher holds the data, the Publication packages it up, and the Articles have the actual data inside. If you think of each of those things as a circle, with the Articles inside the Publication, you have the idea.

Remember that you send out a Publication to another server, not an Article. In other words, the Publication is the smallest unit of data that a receiving server works with.

In the magazine analogy, you can think of the Publication as an issue of the magazine, and the Articles as, well, articles in that magazine. Your readers buy an magazine (Publication), not an individual article (in printed magazines, anyway).

The Distributor

The Distributor is the next part of the process. This server (and remember that this function can be on the same box as the Publisher) does the work of transferring the Publication out. In large replication scenarios, this is often a separate server, or sometimes multiple servers. During the wizard process you’re asked to choose the server which will act as the Distributor.

The important thing to keep in mind is that the Distributor has a large requirement for two key components: drive space and network connectivity. The Distributor has to be able to transfer all of the data to the servers that want it, waiting until the last one requests and receives the data.

In the magazine example, you can think of this as the place where the magazine is dropped off to be picked up by trucks to take to various newsstands, airports, stores and so on. It’s like the in/out warehouse. Obviously, this building has to be large enough to hold all of the magazines that will move in or out.

The Subscriber and Subscriptions

The final server in the mix is the Subscriber. This server is the one that receives the data, using a Subscription to a Publication. There can be several Subscriber systems to one Publication, or a single system can have multiple Subscriptions.

The Subscription is the final end of replication. It’s the meta-data associated with the way the Subscriber will receive the data. Always remember that a Subscription is to a Publication, not an Article. In other words, the Subscriber doesn’t choose the data that is sent. It receives the entire Publication.

In the magazine example, this is the purchaser of the magazine. Might be a single person, or might be a newsstand.

Agents and Jobs

SQL Server 2000 uses many Agents, which are a combination of SQL Server Jobs and stored procedures, that watch the Publications and creates schedules to move the data along.

The Replication Process

With all of the servers in place, you need to understand a little about how they all fir together. The two main concepts here are the types of replication and the direction they flow. It’s here that the magazine analogy breaks down a little.

Replication Types

Now that you have the servers down, I’ll talk a bit about the types of replication. There are three types of replication: Snapshot, Transactional, and Merge.

The simplest type of replication is the Snapshot. This type takes an entire Publication sends it to another server’s database, overwriting the current data. It doesn’t overwrite the whole database, just the data in the Publication.

The next type of replication is Transactional. This type also takes a Snapshot, but after that replicates only the changed data from one system to another.

The final type, Merge (or peer to peer) replication, takes a comparison between two databases and then transfers the differences between them. You have the option of making one server the winner of any conflicts, or of having those conflicts stored so that you can resolve them manually. What is really happening here is that each system is both a Publisher and a Subscriber to each other – but that’s another aticle.

Replication Directions

You can set up the servers to either Push or Pull the data in the Subscription. This choice sets whether the Publisher decides when the data is to be sent (Push), or that the Subscriber decides when to ask for the data (Pull). This choice has implications for all the servers. For instance, if you choose to pull data only once in a while, the Distributor will have to hold that data until it’s asked for. That can add up to some serious storage and bandwidth transfer considerations.

Practical Application

Now that you understand all of the parts of the process and the choices for the types of data that go back and forth and who will send or pick up the data, it’s time to put it all together. You can read more about this in Books Online, but here is the process I’ve followed in the past.

First, I define what the data needs are. Next, I set consider the schedule I need for the data, and then who "owns" the data. Finally I decide the location and type of system that will receive the data. Here’s a typical example that I’ve implemented.

The sales team approached me with the need for product information to be stored on their laptops, using an application that allows them to review catalog records, and they told me that they didn’t need to update the information. They needed the data while they were on the road. They used the SQL Server Express engine with a local application our team wrote to see the data.

SQL Server Express can participate in all types of replication as long as it is the subscriber. SQL Server can also replicate to other types of database engines such as Oracle and Microsoft Access, but that’s another tutorial.

To help my sales team I first detailed the data they needed, and then located the server that had that data , designating it as the Publisher.

I then decided if they need only the data changes or the whole set of data each time. At first I thought they might just need the changes, but on investigation I found our catalog changed frequently, so I decide to use the Snapshot type of replication. That way they get the entire catalog every time they synch up. I verified that they didn’t need to edit the data, just that they needed to see it.

Since Snapshot replication sends everything, I verified that the data in the Publication was small enough to be able to transfer over what might be a poor connection. Since our entire catalog could be represented as one view that returns only 10,000 rows, Snapshot replication was the right choice.

Had the data set been quite large, I would have the clients synchronize the data on our LAN before they left, and I would have then chosen the Transactional type of replication since it sends only the changed rows.

Next I needed to detail the schedule that the sales force required. Since they were on the road most of the time, I never knew when they would be able to connect. This meant I had to set up their database as the Subscriber using a Pull Subscription. When they connected, we coded a button in their client program that allowed them to initiate the transfer.

Finally, I knew that the Publisher "owns" the data. This means that the client would not need to update the data, and their copy can be overwritten. If the needs had been different, say that they needed to update inventory, then I would have chosen Merge replication.

You can see from the simple exercise above that your plan can describe the servers, types of replication and so forth that you need to implement.

InformIT Articles and Sample Chapters

SQL Server 2005 and higher adds more capabilities to Replication. You can read about some of those in this section of Enterprise Data Management in SQL Server 2005.

Books and eBooks

I cover more information on SQL Server replication, along with an example in my book, Administrator's Guide to SQL Server 2005. The information largely still holds true for the later versions.

Online Resources

More information on SQL Server Replication from Microsoft is here.