SQL Server Transactional Replication Internals

SQL Server Transactional Replication is one of the most common Replication techniques used to share, copy, or distribute data to multiple destinations. In this article, we will discuss Replication, various Replication Types, and pay special attention to the Transactional Replication work.

Table of contents

• What is SQL Transactional Replication?
• SQL Server Replication Architecture & Terminologies
• Types of Replication in SQL Server
• SQL Server Transactional Replication
• How to Configure Transactional Replication via SSMS
• Transactional Replication Architecture
• SQL Server Distribution Database
• SQL Server Replication Agents
• Snapshot Agent
• Log Reader Agent
• Distribution Agent
• Replication Agent Profiles
• Replication Maintenance Jobs
• Replication Latency and Tracer Tokens
• Tablediff Utility
• SQL Server Agent Alerts
• Conclusion

What is SQL Transactional Replication?

Replication is the SQL Server technology for Copying or Distributing data from one database to another while maintaining Data consistency.

Replication can be used to transfer data from one database to another

• on the same instance or another instance in the same server;
• or across Servers in a single location or multiple locations.

First, we should go through the Replication Architecture and understand the Replication terminologies.

SQL Server Replication Architecture & Terminologies

• The Publisher is the Source Database instance that publishes the data changes that can be distributed to another database. Data from a Single Publisher can be sent to a Single Subscriber or multiple Subscribers.
• Subscriber is the Destination Database Instance where we distribute the data changes captured from the Publisher Database. Subscriber can be either a Publisher Instance or another instance in Publisher Server/ another Server in the same location/distant location. Before the data changes are distributed to the Subscriber Database instance, these data are stored in the Distributor.
• The Distributor is a Database that stores change logs captured from Publisher databases. When a Server is enabled as a Distributor, it will create a system Database named the distribution database.

By the location of distribution databases, they can be classified as either local or remote distributors.

– Local Distributor is a distribution database residing on the Publisher database instance.
– Remote Distributor is a distribution database residing either in the Subscriber database instance or in any other SQL Server instance apart from the Publisher database instance.

The deciding factor is where to place the Distribution database on the Publisher instance (another instance). it depends on the Server resources available to handle the Data Distribution load.

According to the way of how the data will be sent from the Distribution database to the Subscriber instance, it can be classified into either Push or Pull Subscriptions.

– Push Subscription means that the Distribution database takes responsibility to push the data to the Subscriber database instance.
– Pull Subscription means that the Subscriber database instance takes responsibility to pull the available data from the Distribution database and apply it to the Subscriber database.

• Articles are the fundamental unit of Replication. It indicates any data changes on this database object or article that will be replicated from Publisher to Subscriber. The Article can be a Table, View, Indexed View, Stored Procedure, or the User Defined Function.
• Publications are a collection of one or more Articles from the database in Publisher.
• Subscription defines what Publication will be received. Also, it determines from which Publication and on what schedule the data is replicated. Subscription can be either Push or Pull (from Publication to Subscription).
• Replication Agents are standalone programs responsible for tracking changes and distributing data across the Publisher to Distributor and Subscriber. All Replication Agents execute as Jobs under SQL Server Agent. Thus, it can be administered via SSMS under SQL Server Agent Jobs or Replication Monitor. The following types of Replication Agents are available:
• Snapshot Agent – Used by almost all types of Replication. The Snapshot Agent runs from Server holding the distribution database. It prepares the Schema and initial data of all Articles included in a Publication on a Publisher. Also, it creates the Snapshot files in a snapshot folder and records Synchronization details in the Distribution database.
• Log Reader Agent – Used by Transactional Replication. The goal is to read the data changes of articles enabled for Replication from the Publisher Database Transaction Logs and stored to the Distribution Database. the Log Reader Agent runs from the Distributor Server.
• Distribution Agent – Used by Transactional and Snapshot Replication. It applies the initial Snapshot files and incremental or available pending transactions from the Distribution database to the Subscriber database. The Distribution Agent runs from the Distributor Server for Push Subscriptions and the Subscriber Server for Pull Subscriptions.
• Merge Agent – Used by Merge Replication only. It applies the initial snapshot files and reconciliation of differential or incremental changes across the Publisher or Subscriber. The Merge Agent runs on the Distributor Server for Push Replication and from the Subscriber Server for Pull Subscriptions.
• Queue Reader Agent – Queue Reader Agent is used by Transactional Replication with queued update option. It moves changes from Subscriber to Publisher. The Queue Reader Agent runs from the Distributor Server.
• Replication Maintenance Jobs – As explained earlier, all Replication Agents are standalone programs set up while configuring Replication. They run as jobs under SQL Server Agent Jobs. Few significant jobs to be noted are Distribution Clean Up: Distribution, Agent History Clean Up: Distribution, and Expired Subscription Clean Up.

Types of Replication in SQL Server

Now when we know the terminology, let’s dive into the types of Replication.

1. Transactional Replication. As the name suggests, every transaction or data change within the transactional scope on Publisher will be sent to Subscriber in near real-time with minor delays depending upon the network bandwidth and server resources. Transactional Replication uses the Log Reader Agent to read the data changes from the Transactional Logs of the Publisher database. It also uses the Distribution Agent to apply changes to Subscriber. Occasionally it may use Snapshot Agent to take initial Snapshot data of all Replicated articles. Transactional Replication Publication may fall under the below categories:
   • Standard Transactional Replication – Subscriber is a read-only database from the Transactional Replication perspective. Any changes performed by anyone on the Subscriber database will not be tracked and updated to the Publisher Database. The Standard Transactional Replication is often referred to as Transactional Replication.
   • Transactional Replication with Updatable Subscriptions is an Enhancement of Standard Transactional Replication that tracks the data changes taking place on Subscribes. Whenever data changes are initiated on an Updateable Subscription, they will be first propagated to Publisher and then to other Subscribers.
   • Peer-to-Peer Replication is an Enhancement of the Standard Transactional Replication. It propagates transactionally consistent changes in near real-time across multiple server instances.
   • Bidirectional Replication is an Enhancement of the Standard Transactional Replication that allows two servers (limit to only 2 Servers and hence named Bidirectional) to exchange the data changes across each other with any server acting as a Publisher (to send changes to another Server) or as a Subscriber (to receive changes from another server).
2. Merge Replication – Supports capturing data changes that take place across both Publisher and Subscriber and distributes it to the other Server. The Merge Replication requires the ROWGUID column on the Table Articles involved in Merge Replication. It uses Triggers to capture the data changes across Publisher and Subscriber. Also, it delivers the changes across Servers when both Publisher and Subscriber are connected to the network. Merge Replication uses the Merge Agent to replicate the data changes across Publisher and Subscriber.
3. Snapshot Replication – As the name indicates, Snapshot Replication doesn’t rely on either Transactional Logs or Triggers to capture the changes. It takes a snapshot of articles involved in Publication and applies it to the Subscriber with the records available at the time of the snapshot. The Snapshot Replication uses the Snapshot Agent to take a snapshot of the Publisher and uses the Distribution Agent to apply these records to Subscriber.

SQL Server Transactional Replication

Transactional Replication is typically preferred in scenarios where the OLTP Publisher database has heavy Data INSERT/UPDATE and/or DELETE activities.

Since the Publisher server instance has huge DISK IO happening, generating Reports may cause severe blockings. It also can impact the Server performance. Hence, another Server with near real-time data is good for offloading the Reporting requirements.

One of the fundamental requirements for Transactional Replication is that the tables replicated should have a Primary Key available.

We can summarize how Transactional Replication works. Have a look at the below Transactional Replication Architecture diagram taken from the official Microsoft documentation.

The Publication is created on the Publisher database that comprises the list of Articles for replicating to the Subscriber database.

Transactional Replication will typically be initialized from Publisher to Distributor via the Snapshot Agent or Full Backups. The Snapshot Agent is supported via Replication Configuration Wizard. The Full Backup is supported via TSQL Statements to initialize the Transactional Replication.

The Log Reader Agent scans the Transactional Log of the Publisher database for tracked Articles. Then it copies the data changes from the Transactional Log to the Distribution database.

The Distribution database can be either in Publisher or Subscriber; it can also be or another independent SQL Server instance.

Note also the following things:

• Log Reader Agent runs continuously from the Distributor Server to scan for new commands marked for Replication. However, if you don’t wish to run continuously and want to run on a schedule instead, we can change the Log Reader Agent SQL Job that will be created out.
• Log Reader Agent picks up all records marked for Replication from the Transactional Log in batches and sends them to the Distribution database.
• Log Reader Agent picks up only Committed transactions from the Transactional Log of Publisher Database. So, any long-running queries on the Publisher database can directly impact Replication as it is waiting for the active transaction to complete.

The Distribution Agent picks up all undistributed new commands from the Distribution database and applies them to the Subscription database via either Push or Pull Mechanism. As mentioned earlier, if Push Subscription Distributor takes ownership to apply the changes from Distribution database to Subscriber whereas in Pull Subscription Subscriber database takes ownership to fetch the changes from Distribution database to Subscriber.

Once the records are distributed successfully from the Distribution to Subscriber database, they will be marked as Distributed and marked for deletion from the Distribution database. One of the Key Replication Maintenance jobs named Distribution Clean Up: Distribution job runs once every 10 minutes to delete the distributed records from the Distribution database to maintain the Distribution database size under control.

With the detailed explanation of concepts of Replication and Transactional Replication, we can get our hands on it by configuring Replication for AdventureWorks database and verifying Replication for every component discussed theoretically.

How to Configure Transactional Replication via SSMS

Transactional Replication configuration involves 3 major steps:

1. Configuring Distribution database
2. Publication Creation
3. Subscription Creation

Before trying to configure Replication, make sure that Replication Components are installed as part of SQL Server installation or use SQL Server media to install Replication Components, as they are necessary for the task.

In SSMS, connect to the Publisher Database Instance and right-click on Replication:

Distribution is not configured right now. Hence, we have the Configure Distribution option. We can either Configure the Distribution database using the Configure Distribution wizard or via the Publication Creation wizard.

To configure the Distribution database and Publication, follow the below steps:

Expand Replication and right-click on New Publication.

New Publication Wizard will launch. Click Next to see the Distributor configuration options.

By default, it chooses the Publisher server to hold the distribution database. If you wish to use a remote distribution database, choose the second option. Click Next.

The next option is for configuring the Snapshot Folder. Change it to the required folder. Otherwise, it will be created on the SQL Server installation folder path by default. Click Next.

Select the Publication Database (here it is AdventureWorks) and click Next.

Choose the Publication Type – Transactional Replication. Click Next.

 Choose Articles for this publication. For testing purposes, select all Tables and Views:

Before clicking on Next, expand tables once more to verify some issues.

Some tables are marked by red icons. When we click on those tables, we see the warning indicating that a table can’t be replicated because it doesn’t have a Primary Key, one of the crucial requirements for Transactional Replication. We will later go into more detail. Now, click Next.

A page with Article Issues related to dependencies will appear. Click Next.

The next option is to Filter Table Rows – since we are testing the basic replication, we can ignore it. Click Next.

Configure Snapshot Agent – ignore and click Next.

Agent Settings – click Security Settings to configure account to execute the Snapshot agent and Log Reader Agent under it.

Then, change the Snapshot Agent Process to run under SQL Server Agent Service Account.

Set the Log Reader Agent to Connect to Publisher > By impersonating the Process Account. Click OK.

The Agent Security will get updated.

Thus, we have configured the Distributor and all elements of the Publication like Articles, Snapshot Agent, Log Reader Agent, and Agent Securities. We have almost completed the Publication creation via Wizard.

If you need to study further on TSQL scripts used to create Publication, we can check the Generate a script file to create the Publication option. Otherwise, click Next.

Since I chose to save the file, the wizard allows me to set the Script file path and name. Provide these details and click Next.

The Wizard finally asks for the Publication Name, I’ve named it AdventureWorks_pub with the database name and keywords to indicate it as a publication for easier identification.

Verify all data provided on the Summary page and click Finish.

The Wizard will display the progress in Creating Publication. When it is complete, we’ll see the confirmation. Click Close.

To verify the successful creation of the Distributor (Distribution database), expand the system databases:

To verify the successful creation of Publication, expand Local Publication:

We have configured the Distribution Database and created the Publication database on the AdventureWorks database successfully. Now we can proceed with the Subscription creation

Right-click on the new Publication we have just created and select New Subscriptions:

The New Subscriptions Wizard will appear. To start the process, click Next.

The Publication page asks to ensure that both the Publication and Publisher databases are selected. Click Next.

Set the Distribution Agent to either Push or Pull Subscription. We are going to use the Publisher Server as the Subscriber, and that type won’t have any impact. Hence, we leave the default Push Subscription. Click Next.

Select the Subscribers (database). I’m selecting AdventureWorks_REPL restored from the same AdventureWorks Database backup. Click Next.

Set the Agent Security:

Since I’m going to do everything within a single Server, I’m using the Agent service account.

The next window presents the Distribution Agent Security values already configured. Click Next.

Synchronization Schedule – leave it to the default. Click Next.

Initialize Subscriptions – leave it with the default values. Click Next.

After you provide all the necessary details, you will be able to complete the process of creating the Subscription. Check the Generate Script file… option to study the scripts later and click Next.

Provide the path to save the files, click Next.

Have a look at the summary and check all the configured values. Once verified, click Finish.

The Subscription Creation is completed. Click Close.

Now we can see the Subscription displayed under our Publication.

Configure the Snapshot Agent

Our next step is to work on the Snapshot Agent to send the initial data from Publisher to Subscriber.

Before stepping into it, we need to notice the Replication Monitor. This critical tool is available in SSMS to view the Replication status at various levels, Server Level, Publisher Database level, Subscription level, and Replication Agents level.

Right-click on Replication/Local Publication/Local Subscription/Publication or the Subscription we created to launch the Replication Monitor as shown below:

In the Replication Monitor, expand Publisher Server (RRJ) > Publication ([AdventureWorks]: AdventureWorks_pub) to display the Subscription details. Right-click on Subscription and select View Details.

As we can see, the information about the Initial Snapshot for our publication AdventureWorks_pub is not yet available. We’ll need to execute the Snapshot agent job to send initial data to the Subscriber database.

Keep this window open to see the progress of Snapshot after starting the Snapshot Agent job.

Right-click on Publication > View Snapshot Agent Status:

The agent has never been run message states that we have never executed the Snapshot Agent. Click Start.

While the Snapshot Agent is executing, you can watch the progress:

When all snapshots are created, it will produce the confirmation message:

We can see the Snapshot files created newly in the Snapshot folder for which we provided the path earlier.

After all snapshots are applied by the Distribution Agent to the Subscriber database, it will display the below status in the open Replication Monitor window:

Congrats! We have successfully configured Transactional Replication using Snapshot Agent.

Note: If we have a huge Publisher Database, creating snapshot might take a lot of time. Thus, it is recommended to use the full backup of the Publisher database instead of executing the Snapshot Agent – we’ll cover this issue in subsequent articles.

Verifying Replication Components

Every Replication Components can be verified by both SSMS GUI and TSQL queries. We’ll discuss it in further articles, and here we’ll quickly explain how to view the properties of the below components.

Publisher

In SSMS, right-click Replication > Publisher Properties > Publication Databases:

To view details about the Publisher, execute the below queries against the distribution database.

USE distribution
GO
exec sp_helpdistpublisher
GO
select * from MSpublisher_databases
GO

Subscriber

Subscriber info can be obtained with the below query in SSMS.

USE distribution
GO
exec sp_helpsubscriberinfo
GO
select * from MSsubscriber_info

Distributor

In SSMS, right-click Replication > Distributor Properties:

Click on Publishers to display the list of all Publishers using this Distribution database.

In SSMS, we can run the below query to obtain the same details.

USE distribution
GO
exec sp_helpdistributor
GO
exec sp_helpdistributiondb
GO	

Articles

Right-click on Publication > Publication Properties > Articles. You will see the list of all Articles available. The properties of Individual articles can be modified by clicking on Article Properties as well.

USE AdventureWorks
GO
-- To View all articles available under a Publication
exec sp_helparticle @publication = 'Adventureworks_pub'
GO
-- To View all article columns for a particular article available under a Publication
exec sp_helparticlecolumns @publication = 'Adventureworks_pub', @article = 'Address'
GO
USE distribution
GO
SELECT * from MSArticles

Publication

Right-click on Publication > Properties:

In SSMS, we can run the below query to view the Publication properties:

USE AdventureWorks
GO
exec sp_helppublication
GO
USE distribution
GO
SELECT * FROM MSPublications

Subscription

Right-click on Subscription > Subscription properties:

In SSMS, we can execute the below script to get the Subscription info:

USE AdventureWorks
GO
exec sp_helpsubscription
GO
USE distribution
GO
SELECT * FROM MSsubscriptions
GO

Replication Agents

Under SQL Server Agent Jobs, we can find the specific Jobs created for all Replication Agents:

In SSMS, we can execute the query to find out which job is the necessary Log Reader Agent Job, Snapshot Agent Job, and Distribution Agent Jobs. Besides, we can see the Distribution Agent Cleanup job and several other jobs related to Replication created while we were setting Publication and Subscriptions internally.

How Log Reader Agent Works

The Log Reader Agent reads all committed data from the Publisher database transactional logs and pushes it to the Distributor database. Even though Microsoft doesn’t provide an official way to read Transactional Logs, there are few undocumented functions like fn_dblog() and fn_dump_dblog() that can read the data from Log Files. However, these functions are undocumented and not covered by Microsoft support. Thus, we won’t explore them further.

How Distribution Agent Delivers the Data Changes to the Subscriber Database

Once the data is written to the Distribution database, we can read how that data is stored in distribution tables. For that, we apply the sp_browsereplcmds procedure – it fetches the records across the MSrepl_commands and MSrepl_transactions tables.

For learning purposes, let’s take a table with 3 columns named Person.ContactType:

The created Subscription will make 3 procedures for every article that is part of Publication in Subscriber database with the below naming conventions:

• dbo.sp_MSins_<SchemaName><TableName>
• dbo.sp_MSupd_<SchemaName><TableName>
• dbo.sp_MSdel_<SchemaName><TableName>

For the Person.ContactType Table article, we can see the below procedures created in the Subscriber database:

• dbo.sp_MSins_PersonContactType – INSERT new records captured from the Transaction Logs of Publisher database and then propagated to the distribution database.
• dbo.sp_MSupd_PersonContactType – UPDATE changes captured from the Transaction Logs of Publisher database and then propagated to the distribution database.
• dbo.sp_MSdel_PersonContactType – DELETE records captured from Transaction Logs of Publisher database and then propagated to the distribution database.

Script of the dbo.sp_MSins_PersonContactType Procedure

As you can see, it’s a straightforward INSERT statement that comes out of the distribution database:

ALTER procedure [dbo].[sp_MSins_PersonContactType]
    @c1 int,
    @c2 nvarchar(50),
    @c3 datetime
as
begin  
	insert into [Person].[ContactType] (
		[ContactTypeID],
		[Name],
		[ModifiedDate]
	) values (
		@c1,
		@c2,
		@c3	) 
end  
GO

Script of the dbo.sp_MSupd_PersonContactType Procedure

The script relies on the Primary Key values to identify the unique record for updating:

ALTER procedure [dbo].[sp_MSupd_PersonContactType]
		@c1 int = NULL,
		@c2 nvarchar(50) = NULL,
		@c3 datetime = NULL,
		@pkc1 int = NULL,
		@bitmap binary(1)
as
begin  
	declare @primarykey_text nvarchar(100) = ''
update [Person].[ContactType] set
		[Name] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [Name] end,
		[ModifiedDate] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [ModifiedDate] end
	where [ContactTypeID] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
		Begin
			if exists (Select * from sys.all_parameters where object_id = OBJECT_ID('sp_MSreplraiserror') and [name] = '@param3')
			Begin
				
				set @primarykey_text = @primarykey_text + '[ContactTypeID] = ' + convert(nvarchar(100),@pkc1,1)
				exec sp_MSreplraiserror @errorid=20598, @param1=N'[Person].[ContactType]', @param2=@primarykey_text, @param3=13233 
			End
			Else
				exec sp_MSreplraiserror @errorid=20598
		End
end 
GO

Script of the dbo.sp_MSdel_PersonContactType Procedure

This script relies on the Primary Key values to identify a unique record for deleting records from the Subscriber:

ALTER procedure [dbo].[sp_MSdel_PersonContactType]
		@pkc1 int
as
begin  
	declare @primarykey_text nvarchar(100) = ''
	delete [Person].[ContactType] 
	where [ContactTypeID] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
		Begin
			if exists (Select * from sys.all_parameters where object_id = OBJECT_ID('sp_MSreplraiserror') and [name] = '@param3')
			Begin
				
				set @primarykey_text = @primarykey_text + '[ContactTypeID] = ' + convert(nvarchar(100),@pkc1,1)
				exec sp_MSreplraiserror @errorid=20598, @param1=N'[Person].[ContactType]', @param2=@primarykey_text, @param3=13234 
			End
			Else
				exec sp_MSreplraiserror @errorid=20598
		End
end  
GO

This clearly explains why Transactional Replication enforces the Primary Key as a key requirement for tables to be added as part of Replication.

Now, let’s see the Transactional Replication in action. Let’s change some data in the Publisher database. For simplicity, I’ll take the same Person.ContactType table.

Executing the SELECT statement on the table yields 20 records:

Next, I INSERTed a sample record into the Person.ContactType table:

And, I UPDATE the newly inserted record:

DELETE the newly inserted record from the table:

We need to verify these transactions in Replication using sp_browsereplcmds

Changes from Person.ContactType were captured from the Transactional Logs of Publisher Database (AdventureWorks) and sent to the Distribution database in the same order. Later, it was propagated to the Subscriber Database (AdventureWorks_REPL).

Transactional Replication Architecture

Let’s start by looking into SQL Server Transactional Replication Architecture shown below from Microsoft documentation.

In the Publisher Database, create a Publication comprising the list of Articles (Tables, Views, etc.,) that you need to replicate to the Subscriber database. Once the Articles are enabled for Replication, any changes happening on these articles will be marked for Replication in the Transactional Logs of Publisher database.

SQL Server Transactional Replication can be initialized from the Publisher to Distributor and then to the Subscriber database via Snapshot Agent or Full Backups. The Snapshot Agent can perform the Initialization via the Replication Configuration Wizard. The Full Backup is supported via T-SQL statements only.

The Log Reader Agent scans the Transactional Log of the Publisher database to detect the tracked changes marked for Replication. It ignores other changes captured in the Transactional Logs and copies data changes from the Transactional Log to the Distribution database.

The Distribution database can be either in Publisher or Subscriber, or it can be in another independent SQL Server instance. Note the following things:

• Log Reader Agent runs continuously from the Distributor Server to scan for new commands marked for replication. However, if you don’t want to run continuously and want to run on a schedule instead, we can change the Log Reader Agent SQL Job that will be created out.
• Log Reader Agent picks up all records that are marked for replication from the Transactional Log in batches and sends it to the Distribution database.
• Log Reader Agent picks up only Committed transactions from the Transactional Log of Publisher Database. So, any long-running queries on the Publisher database can directly impact Replication as it is waiting for the active transaction to complete.

The Distribution Agent picks up all undistributed new commands from the Distribution database and applies them to the Subscription database via either Push or Pull Mechanism.

• Push Subscription – the Distributor takes ownership to apply changes from the Distribution database to the Subscriber.
• Pull Subscription – the Subscriber database takes ownership to fetch changes from the Distribution database to the Subscriber.

Once the records are distributed successfully from Distribution to the Subscriber database, they will be marked as Distributed and also marked for deletion from the Distribution database.

One of the Key Replication Maintenance jobs is the Distribution Clean Up: The Distribution job runs every 10 minutes to delete distributed records from the Distribution database to maintain the size of the Distribution database under control.

Therefore, our goal for the current article is to explore the following topics:

• Distribution Database
• Replication Agents
  • Snapshot Agent
  • Log Reader Agent
  • Distribution Agent
• Replication Agent Profiles
• Replication Maintenance Jobs
• Replication Latency and Tracer Tokens
• TableDiff Utility
• SQL Server Agent Alerts

SQL Server Distribution Database

A distribution database is a system database created while configuring Replication. It is the heart of Replication since most of the process runs out of a distribution database.

Due to the nature of the distribution database, we can perform only limited operations on it, such as Backup and Restore. We can’t drop it directly like User databases.

For a huge database with lots of data being replicated, we need to take few special measures to improve Replication throughput performance:

By default, the distribution database is created on the default installation path configured in SQL Server. If not configured, it will be created on the C: drive or in the SQL Server Installation folders. We’d recommend you move the distribution database to a faster storage/ disk to improve the performance.

The Initial File Size and Autogrowth of the distribution database will be set according to the model database’s Initial File Size and Autogrowth settings. Configure the Initial File size to a better value like 10GB in case of the transactionally busy database replication. The Autogrowth properties should be up to 512 MB or 1 GB for both Data and Log files. Then, there won’t be much fragmentation to the Data and Log Files.

Configure the Daily or Routine Backup jobs to include the distribution database for reference purposes or troubleshooting in case of any data corruption or loss.

Configure the Daily Index Reorganization or Maintenance jobs to include the distribution database. The database involves huge data insertions into the MSrepl_transactions and MSrepl_commands tables.

Note: Continuous polling on these 2 tables and DELETE from them after sending data successfully to the Subscriber database increases the risk of fragmentation. Rebuilding these tables on a scheduled basis can improve the distribution database performance.

To view and modify any of the Distribution database attributes related to Replication, right-click on Replication > Distributor Properties:

Click on the ellipsis button on the right to view more details about the individual options listed out.

Pay attention, changing any parameters can impact the distribution database performance. Hence, implement changes only after you evaluate carefully all parameters you wish to modify.

Transaction Retention specifies how much data should be retained in the distribution database. The minimum and maximum values can be specified in hours or days.

The Transaction Retention value set as 0 hours indicates that once records are successfully replicated to the Subscriber database, they can be deleted from the distribution database. If you increase this value, the distribution database size will increase. Thus, we need to plan it accordingly.

History Retention specifies the retention period to store the Transactional Replication Performance History data. By default, it is 48 Hours.

To drop a distribution database, we need to Disable Publication associated with that particular distribution database and then drop it using one of the two methods. One is a combination of Disable Publishing and the Distribution wizard. Another one is using sp_dropdistributor or sp_dropdistributiondb procedures. The Wizard internally uses these 2 procedures to disable Distribution and drop the distribution database.

SQL Server Replication Agents

Replication agents are standalone programs responsible for tracking data changes from Publisher and propagating those changes to Distributor and Subscriber databases. They are executed as SQL Server Agent jobs.

First, let’s see the location of these standalone programs.

To configure Replication, we need to have the Replication components installed via the SQL Server installer. When done, we can see the Replication agent-related standalone programs available in the installation path:

C:\Program Files\Microsoft SQL Server\130\COM

In my case, the version of SQL Server version is 2016. Therefore, it is under 130 in the path.

For every Replication Agent, we can see the respective standalone program available:

• DISTRIB.exe – Distribution Agent
• Logread.exe – Log Reader Agent
• Qrdrsvc.exe – Queue Reader Service Agent
• Replmerg.exe – Merge Replication Agent
• Snapshot.exe – Snapshot Agent
• Tablediff.exe – Utility to Compare Tables. We can get into more details later in this article.

Now that we know what these standalone programs are responsible for and where they are located, we can understand how they are executed via SQL Server Agent Jobs.

Since we are dealing with SQL Server Transactional Replication, we will go through the Snapshot Agent, Log Reader Agent, and Distribution Agent jobs (the same logic applies to all other agents).

Snapshot Agent

The Snapshot Agent runs from the Server holding the distribution database. It prepares the Schema and initial data of all Articles included in a Publication on a Publisher, creates the Snapshot files in the snapshot folder, and records the Synchronization details in the Distribution database.

From the distribution MSSnapshot_agents table, we can identify the SQL Server Agent Job that does the Snapshot Agent activities. Every Publication involves a dedicated SQL Server Agent job that must take care of the Snapshot Agent Responsibilities.

Expand SQL Server Agent and open the job name mentioned above. It will display the details and the Category name – REPL-Snapshot

Click on the Step to see activities performed by the Snapshot agent.

Click on some individual step to view the info on the Snapshot agent job.

Step 1 logs an entry to the history table every time the Snapshot agent starts by using the sp_MSadd_snapshot_history procedure.

The table that holds the history of the details executed by the Snapshot agent is the MSsnapshot_history table in the distribution database.

It will match the View Snapshot Agent Status dialog window.

Move to Step 2 – Run Agent. It will start the Snapshot Agent job.

Under the Command, we couldn’t find any T-SQL statements or queries. There were only some parameters listed out. So, the answer lies in the highlighted section on the above illustration. It shows that the Job Step Type is Replication Snapshot which launches the snapshot.exe standalone program to perform the Snapshot Agent responsibilities.

For more details about snapshot.exe, refer to this MSDN article. We’ll also go into detail while troubleshooting the Replication-related issues later.

Finally, we are going to Step 3 – the last Job Step. It captures any unexpected shutdowns of Agent Jobs and logs them out.

Log Reader Agent

Whenever the Publication is configured on a database, any changes that happen to those Articles get marked for Replication in the Transaction Log. The Log Reader Agent reads those data changes via logread.exe and stores them to the Distribution Database via 2 separate processes:

• Read Transactional Logs – it uses the sp_replcmds extended stored procedure to scan for data changes from the Publisher. Since this stored procedure references DLL files, internals on how exactly Microsoft reads log files are not identified. However, we can try undocumented functions like fn_dblog() and fn_dump_dblog() to understand how the Transactional Log file works.

• Write to Distribution Database – it uses the sp_MSadd_replcmds stored procedure in the distribution database to write the binary data read from the Transactional Logs of the Publisher database. It writes the transaction details to the MSrepl_transactions table and individual commands to the MSrepl_commands table.

Only one Log Reader SQL Server Agent job is available for every Published database. You can identify its name as shown below:

Expand the SQL Server Agent and open up the above Log Reader Agent job to view the steps. It will display the job Сategory under Replication Log Reader.

Click on Steps to see individual steps performed by the Log Reader Agent. As with the Snapshot Agent job, we can see 3 equivalent steps for the Log Reader Agent job.

Step 1 calls the sp_MSadd_logreader_history procedure to log the Startup status history messages of the Log Reader Agent to the MSlogreader_history table.

Step 2 starts the Log Reader Agent process using the logread.exe standalone program.

You can find more details about logread.exe in the respective MSDN article. Later, we’ll also examine the critical configuration parameters of the Log Reader Agent.

Step 3 captures an abrupt shutdown of the Log Reader Agent job.

Distribution Agent

Distribution Agent (DISTRIB.exe) was used by Transactional and Snapshot Replication to apply the initial Snapshot files and incremental or apply available pending transactions from the Distribution database to the Subscriber database.

This Agent runs from the Distributor Server for the Push Subscriptions and the Subscriber Server for the Pull Subscriptions. To find the name of the SQL Server Agent Job that performs the Distribution agent responsibilities, we can execute the specific query as shown below:

Expand the SQL Server Agent job and open it to see more information and the category assigned to the Replication Distribution.

Click on Steps – you will see the steps similar to the previously exposed steps of the Snapshot and Log Reader Agent jobs.

Step 1 calls the sp_MSadd_distribution_history procedure to log the Startup status history messages of the Log Reader Agent to the MSdistribution_history table.

Step 2 starts the Distribution Agent process (DISTRIB.exe) with the default parameters.

For more details about DISTRIB.exe, turn to the MSDN article. Further, we’ll go through the critical configuration parameters of the Distribution agent in the next articles.

Step 3 captures details about the abrupt shutdown of the Distribution Agent job.

Replication Agent Profiles

From the Distributor Properties, we can get the option to view the Replication Agent Profiles. Leave the Agent Profiles to the default values and change only as required for troubleshooting purposes.

Click on Profile Defaults to view the default values configured for all Replication Agents available in the Server.

Select the Distribution Agents section and click the ellipsis button next to the Default agent profile to see the configured values. See the illustration below:

View the Default agent profile of the Snapshot Agents reader Agent:

Default agent profile for the Log Reader Agent:

Replication Maintenance Jobs

Besides Replication Agents, we have Replication Maintenance Jobs.

These are SQL Server Agent jobs created while configuring the SQL Server Transactional Replication. They are available to ensure that the Transactional Replication is working correctly.

Some Jobs on Replication Maintenance are essential for Transactional Replication. Let’s review them.

• Distribution Clean Up: Distribution – executes the sp_MSdistribution_cleanup procedure to delete replication commands from the MSrepl_transactions and MSrepl_commands tables. Clean-up happens on Distribution database after commands are successfully sent to the Subscriber database based upon on the Transaction Retention period value configured in the distribution database. By default, this job runs every 10 minutes on the distribution database. Change these values only after in-depth evaluation.

• Agent History Clean Up: Distribution – executes the sp_MShistory_cleanup procedure in the distribution database to clean up historical records older than the History Retention period configured in that database. By default, it is configured for 48 days and executed every 10 minutes. If you want to change these values, consider all aspects carefully.

• Expired Subscription Clean Up – executes the sp_expired_subscription_cleanup procedure in the master database to drop those subscriptions that expired or were inactive for a long time. By default, this procedure executes once a day.

Replication Latency and Tracer Tokens

Replication Latency is the time required by the Replication process to track any changes happening on published articles from the Publisher database till it is delivered successfully to the Subscriber via Distributor.

Replication Latency is measured in milliseconds. The target value of 0 (real-time replication) to a very low value (ideal cases). It is one of the key measures to monitor the Replication performance.

We can verify the Replication Latency using Replication Monitor or the dedicated sp_replcounters procedure.

Since the Replication Monitor has the refresh rate, there might be slight deviations from the values observed. To overcome the slight deviations while calculating the Replication latency, Tracer Tokens come to our rescue.

Click on the Tracer Tokens tab (see the image above) to send a new set of test commands from the Publisher. Then, measure it when it reaches the Distributor database, and when it was sent to the Subscriber database. Click on Insert Tracer to send tracer tokens from the Publisher database:

Once the records are successfully received in Subscriber, we can track the total Replication latency for our current setup. In our case, it is 9 seconds: 4 seconds from Publisher to Distributor, and 5 seconds from Distributor to Subscriber.

Tablediff Utility

Tablediff utility(tablediff.exe) will be installed in the path C:\Program Files\Microsoft SQL Server\130\COM once we have the Replication Components installed out.

TableDiff utility compares 2 tables for non-convergence. It means that we can compare 2 tables and identify the differences between them. Then it synchronizes the Destination table compared to the Source table by generating dedicated INSERT/UPDATE/DELETE scripts. More details are available in the official documentation.

Since SQL Server Transactional Replication doesn’t care about manual changes on the Subscriber database, this utility can help synchronize these kinds of tables as and when required. However, it does not have a Wizard or UI – you can only access it via the Command Prompt or from batch files.

Other tools can make comparison and synchronization simpler. The dbForge Compare Bundle for SQL Server checks for the discrepancies in the databases and specific tables identifies and analyzes them. It also generates the necessary scripts to synchronize them. It offers a visual interface and a bunch of options to run the tasks fast and straightforward.

SQL Server Agent Alerts

All key components related to Replication Agents are residing as Jobs reside under the SQL Server Agent jobs. Hence, it is critical to monitor how SQL Server Agent jobs function continually to ensure that Replication works without any issues. The most common issues are below:

• Permissions issue executing any of the Replication Agent Jobs
• Permissions issue executing any of the Replication Maintenance Jobs.
• Permissions issue accessing Publisher or distribution or Subscriber database.
• SQL Server Agent not configured to start automatically on server restart.
• Several other Replication related Data issues like conflicts, missing data, and so on.

That’s why we should have a proper alerting mechanism in place to notify the DBA or other person about any issue immediately.

To alert DBAs or other people in case of any job failures or errors, we should configure the Database Mail to send email alerts. It allows the DBA to respond at once and fix the problem. We’ll discuss how to configure the Database Mail and Alerts in a separate article later.

While configuring Replication, SQL Server by default creates the below set of Alerts. You can configure them easily for the required criteria. It also ensures sending Notifications to required people for immediate action.

Conclusion

Thanks for going through another huge article about Replication. I hope it helped to clarify the internals of Transactional Replication and the details about Distribution Database, Replication Agents, and Standalone Programs responsible for those. We also identified the Replication Latency, Alerts, and Tracer Tokens.

Now we can dive deeper and learn how to treat and resolve Replication issues professionally. Stay tuned for the next article!

Tags: replication, sql server, transaction log Last modified: November 06, 2023