SQL Server Transactional Replication Internals – Part 2

SQL Server Transactional Replication is one of the most commonly used replication techniques used to copy or distribute data across multiple destinations. In the previous article, we discussed SQL Server Replication, Types of Replication, and the Basic Internals on how the Transactional Replication works. Now, we are going to dive into Advanced Internals of how SQL Server Transactional Replication works.

CodingSight - SQL Server Transactional Replication Internals - Part 2

Transactional Replication Architecture

Before we start, I recommend you to refresh your knowledge with my previous article here.

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

SQL Server Transactional Replication Architecture

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.

distribution database

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.

database properties

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:

distributor properties
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.

distribution database properties

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

Replication Agents

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.

Snapshot Agent

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

Job Properties

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

Job Properties

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.

Job Step Properties

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

MSsnapshot_history

It will match the View Snapshot Agent Status dialog window.

View Snapshot Agent Status

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

Job Step Properties - Run Agent

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.

Detect nonlogged agent shutdown

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:

Log Reader Agent

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.

Job Properties

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.

Job Properties

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.

Job Step Properties

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

Job Step Properties

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.

Job Step Properties

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:

Distribution Agent

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

Distribution Agent

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

Distribution Agent

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.

Distribution Agent startup message

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

Run Agent

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.

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.

Replication Agent Profiles

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

Profile Defaults

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:

Default Agent Profile

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

Default agent profile of the Snapshot Agents

Default agent profile for the Log 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.
Distribution Clean Up: Distribution
  • 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.
Agent History Clean Up: Distribution
  • 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.
Expired Subscription Clean Up

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.

Replication Monitor

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:

Tracer Tokens

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.

Replication Monitor

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!

Raja Jegan

Raja Jegan

Raja Jegan Ramesh is an experienced Database Architect with more than 15+ years of experience in and around various RDBMS technologies. Primarily focused on SQL Server and related areas, he handles other RDBMS platforms like Oracle, MySQL, etc., in his day to day work. During his spare time, he contributes in Experts-Exchange platform to help resolve day to day issues for several people which helps him encounter lot of scenarios related to SQL Server and how to handle those efficiently by working with other SQL Server experts as well.

Leave a Reply

Your email address will not be published. Required fields are marked *