SQL Server Replication: LEVEL 2 - The Role of the Distributor

Level 2: The Role of the Distributor

The Distributor is the core component of SQL Server’s replication process. The distributor controls and executes the processes that actually move the data from one server to another. It stores information about publications and subscriptions in the distribution database. Several agents facilitate the actual data movement.

Terminology

In the Stairway you will find the terms “distributor”, “publisher” and “subscriber”. They always refer to a SQL Server instance together with the machine it is on. So if you see the terminology “database on the subscriber” it refers to a database on the instance that plays the role of the subscriber. If you see the terminology “file on the distributor” it describes a file on the machine that hosts the instance of SQL Server that is used as a distributor in the context.

The databases that are involved in a replication setup are referred to as “publication database”, “distribution database” and “subscription database” depending on the role of the SQL Server instance on which each is installed.

“Publication”, “distribution” and “subscription” are terms that are defined by replication. They are not used to refer to database objects or databases. Instead you will find a phrase like “a table that is part of the publication”.

Replication agents or SQL Agent Jobs are also always either called agent or job, so the term distributor never refers to the distribution agent or the SQL Agent Job that is used to execute the distribution agent. Rather, the term “distributor” always refers to an instance of SQL Server or to the machine running the instance.

The Distribution Database

When distribution is set up, SQL Server creates a distribution database. The distribution database is classified as a system database, so to find it in object explorer you need to drill down into the “System Databases” subfolder of the databases folder.

A distributor is a SQL Server instance, and can have multiple distribution databases installed. Each publisher, however, is tied to a single distribution database.

The distribution database contains metadata about all publications and articles of the publishers that are associated with the particular distribution database. It also contains metadata about the progress and state of the replication process. In the case of transactional replication it additionally contains all commands that need to be replicated, including all the data necessary to re-execute those commands.

For that reason the distribution database can become quite large, so make sure you have enough room on your hard drive.

The Snapshot Folder

While snapshot replication, as such, is not part of this Stairway, to understand the role of the snapshot folder we need to dip into that area for a little bit.

The snapshot folder is used by snapshot replication to hold a snapshot of the replicated tables. Every time snapshot replication runs, a snapshot of all replicated tables is saved to the snapshot folder. This is accomplished using the SQL Server BCP utility.

The snapshot folder can be a local folder on the distributor or a network share anywhere on the network. The windows account that is used to execute the snapshot agent needs to have write permission on this folder / share. Most other agents need to be granted read access to this folder.

Snapshots are used primarily by snapshot replication, but the other types of replication also use snapshots to do the initial synchronization of the relevant tables. If the databases involved in replication should ever get out of sync, a re-synchronization can be triggered at any time (see level 9 for details). That level would make use of snapshots again.

The Agents

The entire replication process is actually not executed by SQL Server itself. Replication instead consists of several independent processes that are called replication agents. If you follow the standard setup for replication, as described in this Stairway, these processes are executed and controlled by SQL Server Agent Jobs. However, you do not have to follow that pattern; the agents are independent, so you could even execute them from the command line.

With the exception of a pull subscription, all agents reside on the distribution server. Each pull-subscriber executes its own distribution agent, to pull the data from the distributor. The term “pull subscription” was briefly introduced in level 1 and will be explained in more detail in level 4.

Best Practices

Often replication is setup following the “local distributor” model. In the local distributor model all parts of the distributor live and execute on the publisher. It is easier to start out using this model so you often find it in use. However, it takes a measurable toll on the publication server. If that performance impact becomes too big to tolerate, it makes sense to move distribution to its own server. With the distributor with all its processes being a separate machine, there is only minimal impact on the publisher.

If you have a lot of publishers that all use the same distributor, the distribution database might become a bottleneck. To alleviate this you should install one distribution database per publishing SQL Server instance.

Removing Replication

To be able to follow the examples in the next sections, you need to start out with servers that do not have any replication installed. If you have an old setup of replication on your server – maybe because you followed the steps in level 1 – you need to get rid of it now. Make sure that the replication setup on the server you are working on is truly not needed before you continue. It can be very time consuming to reestablish an accidentally dropped replication setup.

The easiest way to remove replication is, to connect to the server in object explorer and right click on the “Replication” folder. In the context menu that opens select “Generate Scripts…” as shown in Figure 1.

Figure 1: Generate Scripts

You will then see a form, shown in Figure 2, which allows you to either script the creation or the deletion of replication components. Select “To drop or disable the components”. Select all components that are listed and then click “Generate Script” followed by “Open in New Query Window”. That will generate a script to drop all selected replication components.

Figure 2: Script to drop all components

If the setup you are trying to remove involves more than one SQL Server instance, you have to do this step on all instances involved. Start with the subscribers, followed by the publishers and finally the distributor. If any of the steps generate an error, make sure you are going through the involved instances in the right order. Regenerate the scripts before you execute them again. That should get you to a clean slate.

Setting up Distribution

To be able to show and introduce security best practices and point out the requirements for a full scale installation of replication, all examples from now on assume that the publisher, the distributor and the subscriber are three distinct machines. If you want to follow the examples provided, and I encourage you to do so, you need to identify the three servers now, as even this level about the distributor will require steps to be executed on the other machines.

If you read the first level of this Stairway, you will notice that a lot of the screen shots seem to be repeated here. However, although we are going through the same dialogs, there are some changes now that we are dealing with three separate machines.

To setup distribution begin by connecting object explorer in SQL Server management studio to your distributor instance. Right click on replication and select “Configure Distribution…” as shown in Figure 3.

Figure 3: Selecting "Configure Distribution..." on the Distributor

Next you will see the “Configure Distribution Wizard”. The first screen that requires a decision to be made is the “Distributor” screen as shown in Figure 4. We are trying to set up the distributor itself, so select the first option.

Figure 4: Selecting the Distributor

The next screen, shown in Figure 5, asks for the snapshot folder location.

The snapshot folder can be a local folder on the distributor, or a shared folder anywhere on the network. If you are planning to use pull subscriptions (which will be covered in level 4), the folder has to be a network share.

A best practice to follow here is to create a network share on the distributor. That gives direct and therefore fast access to most of the processes involved in replication but also allows for scenarios like pull subscriptions that require remote access.

In any case make sure, that the drive has enough room to hold a BCP copy of all replicated tables. The details on who needs to have which type of access to this folder / share will be shown later at the appropriate times.

Figure 5: The Snapshot Folder

The next screen in the wizard asks for details about the distribution database, as shown in Figure 6. You can provide a name and the folder location for the data and the log file.

Depending on the number of publishers using this distributor, the number of articles published and most importantly, the frequency of change amongst those articles, this database can become quite large and busy. Follow the standard best practices for placing and sizing of busy databases when picking a location for the distribution database files.

This wizard does not allow for the creation of multiple distribution databases. Detailed instructions about creating multiple distribution databases and why it might be a good idea to do so can be found in the section “Multiple Distribution Databases" below.

Figure 6: The Distribution Database

Your next step, shown in Figure 7, is to configure the publisher access. Every publisher needs to be registered with the distributor. This form shows a list of all publishers already registered with this distributor. Because we are just setting up this distributor, the list contains only the distributor itself. Click the add button and select “SQL Server Publisher…” to add your publisher. A standard connection dialog will allow you to pick your publishing server instance. You can also uncheck the distributor itself, as this server will be dedicated to be a distributor and will not have any publications.

If you added a server into the list that is not the distributor itself, the next screen – shown in Figure 8 – will allow you to specify a Distributor Password. This password is used to secure the communication between publisher and distributor. Every time you setup a publisher and connect it to the distributor, this password is required to be reentered. So make sure you store it somewhere safe. You also need to make sure – not different from any other password – to pick a secure password here.

Figure 7: Registering Publishers

Figure 8: The Distributor Password

Setting up Distribution on the Publisher

To connect a publisher to a distributor the publisher needs to be registered with the distributor. How to setup that link was shown in the previous section. But that alone is not enough. The distributor also needs to be registered with the publisher. The steps involved to establish that link are topic of this section.

First connect the object explorer in SQL Server Management Studio to the publishing instance. As before, right click on replication and select “Configure Distribution…” to open the “Configure Distribution Wizard”. This time on the “Distributor” Screen do not select the first option. Instead pick “Use the following server” and click the “Add” button as shown in Figure 9.

Figure 9: Use a remote Distributor

In the connection dialog connect to the distributor. The next screen, shown in Figure 10, now asks for the “Administrative Password”. That is the same password that we picked for the distributor earlier.

Figure 10: The Administrative Password

After this step the setup of distribution is complete.

Multiple Distribution Databases

If you want to use one distributor for several publishers it is a good idea to use a separate distribution database for each publisher. This not only reduces contention on the database, but it can also improve your ability to troubleshoot certain problems that might arise, and to recover from disaster.

To create an additional distribution database, connect to the distributor in the object explorer and open the replication context menu. Select “Distributor Properties…” to open the properties dialog. Here you see a list of all installed distribution databases. With the “New…” button beneath that list you open the “New Distribution Database” form, shown in Figure 11.

Figure 11: Creating a New Distribution Database

Here you can specify the name and the folder location for the new distribution database. You can also specify retention values. Their meaning is explained in level 4 of this Stairway. You can leave them at their default values for now.

After you crated a new distribution database, switch to the “Publishers” page of the “Distribution Properties” form. By clicking the “Add” button and selection “Add SQL Server Publisher…” you can register additional publishers with this distributor, as is shown in Figure 12.

Figure 12: Registering Publishers with the Distributor

After you have connected to the new publisher using the standard connect form that opened, it will be added to the list of registered publishers and shown in the Distributor Properties form. Now you can select the distribution database for the new publisher with the drop down menu that appears right next to the publishers name in the list, as you can see in Figure 13. Make sure you select the right database now, because once you confirmed by clicking “OK”, this selection cannot be changed anymore. The only way to change the distribution database for an existing publisher is to completely drop the entire set of publications on that publisher and then delete that publisher from the list of registered publishers on the distributor. After dropping everything you can start over and select a different distribution database this time.

Figure 13: Picking the Distribution Database

For more details on how to handle multiple distribution databases check out “Scaling out the distribution database” (http://www.sqlservercentral.com/articles/Replication/69663/).

Summary

In this Stairway level we went through preparing a SQL Server instance to be a replication distributor. We also prepared another SQL Server instance to use the first one as its distributor. The second instance will be setup to be our publisher in the next level of this Stairway.

At this point there is not really a lot to see on the distributor. None of the agents are running yet and nothing else would show any activity. There is now a distribution database on the server but it is well hidden amongst the system databases.

The only really visible change is a change in the context menu of the replication folder in Object Explorer, which now contains the entry “Distributor Properties…” instead of “Configure Distribution…”.

This might feel a little disappointing – spending this much effort and having nothing to show for it. However, keep in mind that the distributor is the most important piece of a transactional replication installation. Without it no data would move anywhere. The reward for this work will come soon so let us waste no time and continue by setting up the publisher. Level 3: Transactional Replication – Publisher, will tell you how.

SQL Server Replication: LEVEL 1 - Introduction to SQL Server Replication

Level 1: Introduction to SQL Server Replication

The word replication comes from the Latin word replicare which means to repeat. Replication describes the process of reproducing or duplicating (www.merriam-webster.com).

Replication in SQL Server does exactly that; it reproduces or duplicates data. Any time you need to create a copy of your data, or to reproduce a change to that data, replication can be used. That copy can be created in the same database or at a remote location on a separate server.

The copy can be continuously kept in sync with the source data, or synchronized at scheduled intervals. One-way synchronization, as well as bidirectional synchronization, is possible. Replication can even be used to keep several datasets in sync with each other.

In this first level, I'll tell you about the basic replication components and describe how they work together to allow you to replicate your data and your data changes. We'll also look at a detailed example of setting up a simple replication scenario.

Replication Components

SQL Server replication consists of three components: The Publisher, the Distributor and the Subscriber. These components act on articles that are defined within publications and subscriptions.

Article

For each SQL Server object that should be replicated, a replication article needs to be defined. Each article corresponds to a single SQL Server object, or a subset of an object. The objects that are replicated most often are tables, views and stored procedures. For a complete list of objects that can be replicated, check out Publishing Data and Database Objects in Books Online. The properties of an article determine whether the article contains the entire object, or if a filtered subset of the objects makes up the replicated article. With some restrictions, multiple articles can be created on a single object.

Publication

A group of articles that logically belong together can be combined into a publication. The publication has options defined that apply to all the articles in that publication. The main option defined by a publication is the type of replication that is to be used.

Publisher

The SQL Server instance that makes a publication available for replication is called the publisher.

The publisher monitors all articles for changes, and makes information about those changes available to the distributor.

Distributor

The distributor is the SQL Server instance keeps track of all subscribers and all published changes and makes sure that each subscriber gets notified of each change. Most of the changes are tracked in a distribution database. The distributor can be a separate SQL Server instance, but often the distribution service runs on the same machine as the publisher.

Subscriber

The subscriber is the SQL Server instance that receives all the published information through subscriptions.

Subscription

A subscription is the counterpart of the publication. A subscription defines which server (subscriber) is to receive the updates published in a publication. Each subscription creates a link between one publication and one subscriber. There are two types of subscriptions: push subscriptions and pull subscriptions. In a push subscription, the distributor directly updates the data in the subscriber database. In a pull subscription, the subscriber asks the distributor regularly if any new changes are available, and then updates the data itself.

Replication Types

There are three main types of replication available in SQL Server. They are snapshot replication, merge replication and transactional replication.

Snapshot Replication

Snapshot replication creates a complete copy of the replicated objects and their data each time it runs. It uses SQL Server's BCP utility to write the contents of each table into the snapshot folder. The snapshot folder is a shared folder location that has to be set up on the distributor when enabling replication. Each participant in a replication setup needs to have access to the snapshot folder.

Every time snapshot replication is run, everything is recopied from scratch, so it has high bandwidth and storage requirements. All other types of replication use - by default - a single replication snapshot to sync up all subscribers with the distributor only during the initial setup.

Transactional Replication

Transactional replication works, as the name suggests, on a transaction basis. Every committed transaction gets scanned for changes applied to replication articles. Scanning of the changes is done by a log reader agent, which reads through the transaction log of the publisher database. If there are changes affecting a published object, those changes get logged on the distributor in the distribution database. From there they make their way to the subscribers.

Transactional replication allows for close to real time synchronization and leaves only a small footprint on the publisher. While there are several options to allow for bidirectional data movement, transactional replication was originally designed to work one way only.

Merge Replication

Merge replication was designed from the beginning to allow for changes to the data to be made on the publisher as well as the subscriber side. Merge replication also allows for disconnected scenarios, where a subscriber might not be connected during the day. That subscriber would synchronize after reconnecting in the evening. If a row gets updated in two different places at the same time, a conflict occurs. Merge replication comes with several built in options to resolve those conflicts.

Setting up Transactional Replication

This section is a step by step guide, of how to setup transactional replication involving a single replicated table.

To setup replication, the distributor, the publisher and the subscriber need to be configured. Replication can be completely setup and controlled using T-SQL scripts. However, the necessary T-SQL involves the use of stored procedures which together have well over a hundred required parameters. So it makes sense to start out by using the SSMS-GUI. The example screenshots shown here were made on a single server (WIN2008A) with a SQL-Server instance (R2A) installed on it. This instance is a SQL-Server 2008R2 instance. However, you can follow the examples on SQL Server 2008 and 2005 as well.

Setting up distribution

The distributor is the core of transactional replication. It is required to be available when setting up all the other components, so it needs to be configured first.

Start out by opening SSMS and connecting to the SQL-Server instance that will contain your replication source data. While the distributor can be on its own SQL-server instance, in many cases it makes sense to have the publisher and the distributor live on the same machine, as will be assumed in this example.

After you connected to the server, right click on the "Replication" folder under your server and select "Configure Distribution..." as shown in Figure 1.

Figure 1: Configure Distribution

The Configure Distribution Wizard will greet you with its start-up screen as shown in Figure 2.

Figure 2: Configure Distribution Wizard

It is safe to ignore this screen and click "Next".

On the next screen (Figure 3) you will choose whether the distribution service should run on this server or whether you already have a configured distributor in your network. The machine on which the distribution service runs will also contain the distribution database. Leave this selection at the default, which is to install distribution on this server and click "Next".

Figure 3: Selecting the server for Distribution

This brings up the dialog in Figure 4, asking you to select the location for the snapshot folder.

Figure 4: The Snapshot Folder

The snapshot folder can be any location on your machine or in your network. It makes sense to create a network share on the distributor for it. This example uses "\\WIN2008A\ReplicationSnapshotFolder".
The setup of the snapshot folder requires appropriate rights to be granted as well. Without going into too much detail right now, grant write access to "Authenticated Users" (Figure 5) on the folder itself and read access to "Everyone" (Figure 6) on the share. More information about how to tighten security in this area will be given in a later
level in this Stairway.

Figure 5: Granting access to the Snapshot Folder

Figure 6: Granting access to the Snapshot Folder Share

After you setup your share and put the network path into the wizard's input field, press "Next" to go to the "Distribution Database" form shown in Figure 7.

Figure 7: The Distribution Database

Here you specify the name of the distribution database and also where its data and log files shall be located. Leave the defaults and click "Next" to advance to the "Publishers" screen (Figure 8).

On the "Publishers" screen you prepare potential publishers to be able to use this distributor. We will install our publisher on the same instance, so you can leave the default and "Next" to the final question (Figure 9).

Figure 8: Preparing Publishers

Figure 9: Wizard Actions

The final question is whether you want the wizard to execute your selections immediately or whether you want the wizard to create script that you will execute manually at a later time. Again, leave the default setting and click "Next" one last time.

Now you will see a list of actions as in Figure 10 that will take place. Click "Finish" to start the process.

Figure 10: Wizard Summary

Finally, the screen shown in Figure 11 gives information about progress and success of the replication configuration.

Figure 11: Execution Status

The First Publication

To create a publication, we need to first have a database containing a table to publish. Execute the SQL Script 1 to create a test database for the publication.

USE MASTER;
GO
EXECUTE AS LOGIN = 'SA';
GO
CREATE DATABASE ReplA;
GO
USE ReplA;

GO
IF OBJECT_ID('dbo.Test') IS NOT NULL DROP TABLE dbo.Test;
GO
CREATE TABLE dbo.Test(
 Id INT IDENTITY(1,1) PRIMARY KEY,
 Data INT CONSTRAINT Test_Data_Dflt DEFAULT CHECKSUM(NEWID())
);


GO
INSERT INTO dbo.Test DEFAULT VALUES;
GO 1000
USE MASTER;
GO
REVERT;
GO

Script 1: Create a test database for the publication

Now you are ready to setup a publication.

In the SSMS Object Explorer open the Replication folder and right click on "Local Publication". Select "New Publication..." in the drop down menu (Figure 12).

Figure 12: New Publication

The welcome page of the "New Publication Wizard" appears as shown in Figure 13.

Figure 13: Configure Publication Wizard

Click "Next".

In the "Publication Database" box (Figure 14) select the database ReplA that you just created and click "Next". The "Publication Type" screen (Figure 15) lets you choose which type of replication you want to use. Select "Transactional publication" and click "Next".

Figure 14: Select the Publication Database

Figure 15: Publication Type

Now you get to choose which articles should be part of this publication (Figure 16). Select the table dbo.Test and click "Next" again to move on to the "Filter Table Rows" dialog (Figure 17). Filters are an advanced topic that will be covered in a later
level in this Stairway, so for now just click "Next" without making a selection on this form.

Figure 16: Articles

Figure 17: Row Filters

The next three screens deal with the snapshot agent. On the first screen (Figure 18) select "Create a snapshot immediately" and click "Next". On the "Agent Security" Screen (Figure 19) click the "Security Settings" Button and pick "Run under the SQL Server Agent service account" on the form that opens (Figure 20).

Figure 18: Snapshot Schedule

Figure 19: Agent Security

Figure 20: Select an Account

Click "OK" on the form and then "Next" on the Agent Security screen.

That brings up the "Wizard Actions" form (Figure 21) for you to select "Create the publication". One last time of clicking "Next" brings you to the summary screen (Figure 22).

Figure 21: Wizard Actions

Figure 22: Wizard Summary

Here you need to pick a name for your publication. Type "MyFirstPublication" into the field and click "Finish" to start the process. Again, the last screen (Figure 23) shows progress information and a success status when the process is finished.

Figure 23: Execution Status

The first subscription

In most cases, the subscriber will be on a different machine but there are scenarios in which you want it to be on the same instance. To keep this example simple we are going to stay on the same instance as well. Use the code in Script 2: to create the database ReplB.

USE MASTER;
GO
EXECUTE AS LOGIN = 'SA';
GO

CREATE DATABASE ReplB;
GO
REVERT;
GO

Script 2: Creating the target database

We now go to the SSMS Object Explorer and right click on "Local Subscriptions" and select "New Subscriptions..." in the drop down menu (Figure 24).

Figure 24: Select New Subscription

The "New Subscription Wizard" (Figure 25) welcomes you and gives you another opportunity to practice pressing a "Next" button.

Figure 25: New Subscription Wizard

In the "Publication" form (Figure 26) select the publication you just created and click "Next". The "Distribution Agent Location" screen (Figure 27) allows you to select between push and pull subscription. Leave the default and move on to the "Subscribers" Screen (Figure 28).

Figure 26: Select the Publication

Figure 27: Push or Pull

Figure 28: Target Database

Here you need to select your server and pick the ReplB database.

The next screen allows you to setup "Distribution Agent Security" (Figure 29).

Figure 29: Distribution Agent Security

Click the small ellipsis button on the right side and select "Run under the SQL Server Agent service account" in the form that opens (Figure 30).

Figure 30: Select an Account

Clicking "OK" and then "Next" gets you to the "Synchronization Schedule" Screen (Figure 31). Pick "Run continuously" and move on to the "Initialize Subscriptions" screen (Figure 32). Leave the default, to initialize immediately and then "Next" to the "Wizard Actions" form (Figure 33).

Figure 31: Synchronization Schedule

Figure 32: Initialization

Figure 33: Wizard Actions

As before, leaving the default (Create the subscriptions) and clicking "Next" gets you to the summary screen (Figure 34) with a list of actions about to be performed. Click "Finish" to start the process and wait for the green success logo to appear on the final form (Figure 35).

Figure 34: Wizard Summary

Figure 35: Execution Status

Success!

Script1 created the table dbo.Test in ReplA and inserted 1000 rows into it. After the initial snapshot was transferred over to the subscriber you will find the dbo.Test table also in ReplB with all 1000 rows in it. A couple of minutes after you finished the setup you can run Script 3 to verify that replication pushed all the data to the subscriber as expected. This script joins the ReplA.dbo.Test and the ReplB.dbo.Test tables together to show which rows got replicated correctly. You can now run further tests of your own, you can inserting and updating rows in ReplA.dbo.Test and watch those changes magically appear in ReplB.dbo.Test as well.

SELECT TOP(20) A.Id AS [ReplA.Id],A.Data AS [ReplA.Data],B.Id AS [ReplB.Id],B.Data AS [ReplB.Data]

FROM ReplA.dbo.Test A
FULL OUTER JOIN ReplB.dbo.Test B
ON A.Id = B.Id
ORDER BY A.Id DESC

Script 3: Compare Publisher and Subscriber

Summary

Objects in a database on a SQL Server instance referred to as the publisher that are marked for replication are called articles. Articles are grouped together into publications. The subscriber gets updated with the changes that occur to the articles through a subscription. The data flows through the distribution database which resides on the distributor. Publisher, distributor and subscriber can be the same instance or separate instances on the same or on different machines. The source and the target database can be the same (if publisher and subscriber are in fact the same SQL Server instance), but the distribution database has to be separate.