SQL Server DBA / sqlLAB

SQL Server Replication

Replication is becoming increasingly common. Recently at work, it seems that nearly all new clients have replication as part of their environment, such that replication is becoming a part of our everyday life here and I can no longer leave it to the more experienced DBAs in the team. So this afternoon I have determined to do SQL Server Central’s Stairway to SQL Server Replication.

replication… reproduces or duplicates data.

This is an important definition, because many of the issues we see with replication occur when a client makes schema changes (adding new tables, deleting columns etc.). This definition is clear, it is data that is replicated, not structure. This is what allows there to be different indexes at the publisher and subscriber.

Replication components

Article: “corresponds to a single SQL Server object” e.g. tables, view, sp, functions. Properties can be set that control which elements of the object are replicated (i.e.the entire object, or only certain child objects)

Publication: “a group of articles that logically belong together”

Publisher: “The SQL Server instance that makes a publication available for replication.

Distributor: “the SQL server instance that keeps track of all subscribers and all published changes and ensures that each subscriber gets notified of each change.”

Subscriber: “The SQL Server instance that receives all the published information”


  • The definition of which subscriber is to receive the updates published in a publication. “Each subscription creates a link between one publication and one subscriber.”
  • push subscription: the distributor pushes out data to the subscriber
  • pull subscription: the subscriber polls the distributor for and changes.

Types of Replication

Snapshot replication:

  • “creates a complete copy of the articles… [using] SQL Server’s BCP utility”
  • High bandwidth and storage requirements.
  • Snapshot replication is used to “sync up all subscribers with the distributor … during the initial setup”

Transactional Replication:

  • Replication on a transactional basis.
  • Close to real-time synchronisation.
  • Lower overhead than snapshot replication.

Merge Replication:

  • Under merge replication, synchronisation is not necessarily continuous it can be scheduled.
  • Changes can be made at both the publisher and subscribers, and these changes “merged” into an eventually-consistent environment.

Setting Up Replication

Setting up replication is “Visual Studio Easy”. Simply follow your nose through the wizard and all will be well. There are 3 main steps to setting up replication, which are described below:

Step 1: Setup the Distributor

The distributor can be on any instance of SQL Server, but is commonly on the Publisher instance.

In the SSMS object explorer, right click the Replication folder and choose configure distributor. Follow this wizard.

Step 2: Setup Publications

In the object explorer, expand the replication folder and right click on “Local publications”. Choose “New publication” and follow the wizard. This involves 3 major components:

  1. define the articles (choose source database, select source objects (tables, views, sp…), set any additional properties (like filters)
  2. create a snapshot for initialising the replication environment
  3. define the distributor security (account that will be used to run the distributor jobs)

Step 3: Setup Subscriber

On the publisher instance, expand the replication folder, right click the “Local Subscriptions” folder and select “New Subscription”. Follow the wizard which involves:

  1. selecting the publications, (define source instance and source publications)
  2. select the database to act as the subscriber (there is an option here to create a new database, if ti doesn’t already exist)
  3. configure the distributor security – in my example run I set this to run at the distributor instance I think.
  4. initialise the subscription immediately, or set a schedule for when you want it to start to initialise

In classic wizard-style, it is never “that easy”. In my ExampleProject, the subscription database was not updated with the publications after initialisation. I opened the Replication Monitor and saw that there was an error with the Log Reader Agent. Checking the SQL Error Logs, the following error was recorded:

Figure 1: SQL Agent Error Log - setting up replication

Figure 1: SQL Agent Error Log – setting up replication

Replication-Replication Transaction-Log Reader Subsystem: agent <agent_name> failed. The process could not execute “sp_replcmds” on <instance_name>”

My initial thoughts were that the distributor did not have permissions to the ReplData folder. I explicitly added permissions on this folder, and waited a couple of minutes to see if the initialisation process was successful. No luck.

After a quick Google search, I found that people were having this issue when the database owners were not set appropriately. I reviewed the database owners and found that the publication database had a local user as owner. After changing this is ‘sa’, replication kicked in and all has been well since.


The increasing use of replication has motivated me to roll up my sleeves and get hands-on. In this first post, I have defined the terminology and setup replication in a sqlLab. Like most things SQL Server, careful thought must be given to the permissions and accounts. This will have a direct impact on the seamless operation and management of replication.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s