SQL Server Reference Guide

I’ve covered the concepts of Microsoft SQL Server Replication in a previous overview, so if you’re not familiar with the various terms and processes regarding Replication, make sure you check that article out first (pun intended). I’ll use those terms and concepts in this tutorial, and I’ll show you how to build a working replication scenario from end-to-end in this article.

I’ll begin with the same definition I explained in the overview:

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

In this tutorial, I’ll show you how to set up replication, and then how you can watch the replicated data move from one system to another. A word of caution here, however. Setting up Replication makes changes to your system that cannot be undone without running a few stored procedures for cleanup. That’s not a bad thing; it just means that once your system is set up for Replication it will always be there unless you do some more work. That doesn’t mean that it will always replicate data somewhere, just that it can. The “plumbing,” so to speak, stays with the Instance of SQL Server from then on without extra steps.

This means that if you want to follow along with this example, you should ensure that you are using a test system that you don’t care about. This example sets up all parts of the Replication structure (Publisher, Distributor, Subscriber) on one system, so it’s a perfect place to use Virtual PC (which is free) from Microsoft to set up a test system. That way it isn’t on your domain, doesn’t affect your other clients and so on. You can also use that software to create “snapshots,” so that you can do this again and again. I cover that software for use with SQL Server in this article.

OK — you’re on a test system, that you don’t mind completely re-installing if you had to. I don’t think it will come to that, but I just want to emphasize that you don’t want to try this simple test using your production system. I’m going to set this up in such a manner that you can follow along using only one SQL Server 2008 Instance — Standard Edition or higher. I’ll set up a simple Transactional replication system, which means that as I add data to a table at the source (Publisher), the data (Article) will queue up and be sent (Distributor) to the destination (Subscriber). I’ll use a Push method, meaning that the Subscriber will get the data automatically without having to ask for it. There are, of course, lots of other arrangements, but this is what I’m after in this simple example.

The first step in setting up Replication is to ensure that you have the SQL Server Agent service running on all systems involved. In my case, that’s only one box, but in production all of the systems need to have Agent running. You can find that out by looking in SQL Server Management Studio at the SQL Server Agent node. If the Agent is running, you’ll see a green arrow next to the icon, and if not, you can right-click that node to start it.

Figure 1

The next thing you’ll need to do is ensure that you have network connectivity between the systems in the Replication schema — that means that the Publisher, Distributor and all Subscribers can all see each other on the network.

Finally, you need to make sure that the Publisher and Distributor have enough room to store the data that will be transferred. Depending on the kind of Replication you choose, you’ll need at least as much as the Articles being transferred available in addition to the database storage itself. I can’t emphasize this enough — over 80% of the problems I see with Replication involve the Agent service not running or a space issue.

I’ve done all that, and now I’m ready to start my example. First, I’ll need a database to hold the data I’ll replicate out. You can use any of the sample databases, but I’ll keep this simple and make a small table that holds only a few values so that I can watch it work as I go. Here’s the code I run on the system I designate as the Publisher (which is of course the same in this case as the Distributor and the Subscriber!):

/* Source Database */
CREATE DATABASE SourceDatabase;
GO
USE SourceDatabase;
GO

/* Table for source data */
CREATE TABLE SourceDataTable (c1 int PRIMARY KEY, c2 VARCHAR(25))
GO 

/* Destination Database */
CREATE DATABASE DestinationDatabase;
GO
USE DestinationDatabase;
GO

/* Table for source data */
CREATE TABLE DestinationDataTable (c1 int PRIMARY KEY, c2 VARCHAR(25))
GO 

/* Insert some data: Note the SQL Server 2008 syntax with repeating VALUES, won't work in lower versions */
USE SourceDatabase;
GO
INSERT INTO SourceDataTable VALUES (1, 'Thing One'), (2, 'Thing Two'), (3, 'Thing Three')
SELECT *
FROM SourceDataTable;
GO

Now I’m all set, and everything is ready for the Replication Wizards. I open SQL Server Management Studio (SSMS) and right-click the Replication Node and select Configure Distribution from the menu that appears:

Figure 2

That brings up a Welcome screen, so I click Next there to bring up the Distribution Node configuration panel.

Figure 3

Here I’m leaving the system as the test system I’m working with, but in practice it’s often best to separate out the Publisher, Distributor and of course the Subscribers. I click Next here to bring up the Agent configuration panel, which states not only that the Agent must be set to Auto-start, but it must have administrator privileges on my system.

Figure 4

It does, so I click Next here to bring up the panel that sets the location for the snapshot. As you’ll recall from the Overview article, all Replication needs a “starting point,” so it sends over a complete copy of whatever data you’ll replicate, known as a snapshot. Since I’m going to push out the data, I can leave this set to my local drive. If I wanted the clients to be able to ask for the data (called a Pull Subscription), I would have to place the snapshot on a network share that the Distributor and the Subscribers could all access.

Figure 5

I’ll leave this location as-is for this test, and click Next to bring up the Distribution database creation step. The distribution database will accept all of the data transfers (the individual Article rows) that will replicate down to the Subscribers.

Figure 6

I click Next there to bring up the panel that sets the servers that can use the Distributor I’m creating. Again, this is normally done against multiple servers, but in this case I’ll just leave this same server as the one that can Publish to this Distributor.

Figure 7

Clicking Next here brings me to the panel that will either do the work, save the steps to a script, or even both.

Figure 8

After I click Next and then Finish on the final panel, the system configures itself to be a Distributor, and to grant rights to the system I named to be the Publisher. It also configures the Agent as requested.

Figure 9

Now I’m ready to start replicating my data. I’ll stay in the Replication node in SSMS, and then right-click the Replication node again, and then select New and then Publication from the menu that appears.

Figure 10

You can also right click the Local Publication sub-nodes, and either one will bring up the welcome panel on the Wizard. I click next there to show the Publication database selection.

Figure 11

Here I select SourceDatabase and click Next.

Figure 12

Now I select the type of Replication I want. In this case, I want each line of data sent to the Subscribers just after I enter them, so I’ll pick Transactional Replication and then select Next.

Figure 13

In this panel I select the data I want to transfer — the Article. As you’ll recall from the Overview article, that can be a table, view, or even Transact-SQL code. I’ll keep it simple for this example, and send the entire table, which in this case happens to be duplicated on the Subscriber. That doesn’t have to be the case, but I’ll cover that in other tutorials. Clicking Next after those selections brings up the Filter panel.

Figure 14

This allows me to send only the rows that meet a certain criteria, but I want them all so I’ll just click Next to bring up the Snapshot Agent schedule.

Figure 15

I want the snapshot to be taken immediately, but in a production situation this might bog the system down so I might schedule it for later if that were the case. Click Next here brings up the panel that sets the security for the snapshot. I’ll enter my domain information.

Figure 16

After I fill in that information and click Next, I’m brought to the Wizard completion panel where I can set the action to happen or script again. I click Next there and then I’m brought to the summary panel.

Figure 17

Here I have to name the Publication so that I can track it later. I’ll call it TestReplication and then click Finish, and watch the progress.

The first half of the process is now complete. My Distributor is ready, the Publisher is set up to use it, and I have an Article that has been set up with a snapshot and a “watching” Agent job. All that I need now is a Subscriber to ask for the data.

I’ll create that on the same test system, but of course you’ll normally do this on another Instance of SQL Server. In SSMS, under the Replication and then Local Subscriptions node, I right click and select New Subscription from the menu that appears.

Figure 18

Click Next at the Welcome Panel brings up the selection for the Publication I want — note that I select the Publication, not the Articles.

Figure 19

After I make that selection, the Next button brings up the push or pull method of subscribing. Since I want the data as soon as it is entered, I make that selection.

Figure 20

Clicking Next on this panel brings up the databases where the data will end up. I make my selection for the DestinationDatabase.

Figure 21

Clicking Next here brings up the panel that sets the Distribution security, just as I did earlier. I’ll enter my domain credentials again.

Figure 22

Clicking Next here brings me to the scheduling tab. If I want the data to pile up at the Distributor and then go out periodically, I would select a schedule here, but I want the data immediately, so I leave the default selection.

Figure 23

Clicking Next on this panel brings up the selection for when I want the Subscription to start – and I want it now, so I leave the default selection and click Next.

Figure 24

I get the choice to do the previous actions or script them, and I click Next and then Finish on the summary panel. My subscription is now running.

To check that, there are several graphical tools in SSMS, but I’ll just perform a simple SELECT statement to see if the snapshot data went from the Publisher to the Subscriber. I’ll also enter some data and then watch it replicate as well:

/* Check the data to ensure the snapshot worked */
USE DestinationDatabase;
GO
SELECT *
FROM DestinationDataTable;
GO

/* Insert more values, watch the transfer */
USE SourceDatabase;
GO
INSERT INTO SourceDataTable VALUES (4, 'Thing Four'), (5, 'Thing Five'), (6, 'Thing Six')
SELECT *
FROM SourceDataTable;
GO
USE DestinationDatabase;
GO
SELECT *
FROM DestinationDataTable;
GO

There you have it — a simple Replication example. In future tutorials I’ll cover more complex, real-world examples, but this process should give you everything you need to begin experimenting with your own.

InformIT Articles and Sample Chapters

If you want to play with Replication in SQL Server 2005, check out the sample chapter Enterprise Data Management in SQL Server 2005

Books and eBooks

More information on Replication for SQL Server 2005 is in my book, Administrator's Guide to SQL Server 2005.

Online Resources

The official SQL Server Replication information from Microsoft is here.