Transactional replication is a useful way to keep schema and data for specific objects synchronized across multiple SQL Server databases. Replication can be used in simple scenarios involving a few servers or can be scaled up to complex, multi-datacenter distributed environments. However, no matter the size or complexity of your topology, the number of moving parts involved with replication means that occasionally problems will occur that require a DBA’s intervention to correct.
In this article, I’ll show you how to use SQL Server’s native tools to monitor replication performance, receive notification when problems occur, and diagnose the cause of those problems. Additionally, I'll look at three common transactional replication problems and explain how to fix them.
A View into Replication Health
Replication Monitor is the primary GUI tool at your disposal for viewing replication performance and diagnosing problems. Replication Monitor was included in Enterprise Manager in SQL Server 2000, but in SQL Server 2005, Replication Monitor was separated from SQL Server Management Studio (SSMS) into a standalone executable. Just like SSMS, Replication Monitor can be used to monitor Publishers, Subscribers, and Distributors running previous versions of SQL Server, although features not present in SQL Server 2005 won’t be displayed or otherwise available for use.
To launch Replication Monitor, open SSMS, connect to a Publisher in the Object Explorer, right-click the Replication folder, and choose Launch Replication Monitor from the context menu. Figure 1 shows Replication Monitor with several registered Publishers added. Replication Monitor displays a tree view in the left pane that lists Publishers that have been registered; the right pane’s contents change depending on what’s selected in the tree view.
Selecting a Publisher in the tree view shows three tabbed views in the right pane: Publications, which shows the name, current status, and number of Subscribers for each publication on the Publisher; Subscription Watch List, which shows the status and estimated latency (i.e., time to deliver pending commands) of all Subscriptions to the Publisher; and Agents, which shows the last start time and current status of the Snapshot, Log Reader, and Queue Reader agents, as well as various automated maintenance jobs created by SQL Server to keep replication healthy.
Expanding a Publisher node in the tree view shows its publications. Selecting a publication displays four tabbed views in the right pane: All Subscriptions, which shows the current status and estimated latency of the Distribution Agent for each Subscription; Tracer Tokens, which shows the status of recent tracer tokens for the publication (I’ll discuss tracer tokens in more detail later); Agents, which shows the last start time, run duration, and current status of the Snapshot and Log Reader agents used by the publication; and Warnings, which shows the settings for all warnings that have been configured for the publication.
Right-clicking any row (i.e., agent) in the Subscription Watch List, All Subscriptions, or Agents tabs will display a context menu with options that include stopping and starting the agent, viewing the agent’s profile, and viewing the agent’s job properties. Double-clicking an agent will open a new window that shows specific details about the agent’s status.
Distribution Agent windows have three tabs: Publisher to Distributor History, which shows the status and recent history of the Log Reader agent for the publication; Distributor to Subscriber History, which shows the status and recent history of the Distribution Agent; and Undistributed Commands, which shows the number of commands at the distribution database waiting to be applied to the Subscriber and an estimate of how long it will take to apply them. Log Reader and Snapshot Reader agent windows show only an Agent History tab, which displays the status and recent history of that agent.
When a problem occurs with replication, such as when a Distribution Agent fails, the icons for the Publisher, Publication, and agent will change depending on the type of problem. Icons overlaid by a red circle with an X indicate an agent has failed, a white circle with a circular arrow indicates an agent is retrying a command, and a yellow caution symbol indicates a warning. Identifying the problematic agent is simply a matter of expanding in the tree view the Publishers and Publications that are alerting to a condition, selecting the tabs in the right pane for the agent(s) with a problem, and double-clicking the agent to view its status and information about the error.
Measuring the Flow of Data
Understanding how long it takes for data to move through each step is especially useful when troubleshooting latency issues and will let you focus your attention on the specific segment that’s problematic. Tracer tokens were added in SQL Server 2005 to measure the flow of data and actual latency from a Publisher all the way through to Subscribers (the latency values shown for agents in Replication Monitor are estimated). Creating a tracer token writes a special marker to the transaction log of the Publication database that’s read by the Log Reader agent, written to the distribution database, and sent through to all Subscribers. The time it takes for the token to move through each step is saved in the Distribution database.
Tracer tokens can be used only if both the Publisher and Distributor are on SQL Server 2005 or later. Subscriber statistics will be collected for push subscriptions if the Subscriber is running SQL Server 7.0 or later and for pull subscriptions if the Subscriber is running SQL Server 2005 or higher. For Subscribers that don’t meet these criteria (non-SQL Server Subscribers, for example), statistics for tracer tokens will still be gathered from the Publisher and Distributor. To add a tracer token you must be a member of the sysadmin fixed server role or db_owner fixed database role on the Publisher.
To add a new tracer token or view the status of existing tracer tokens, navigate to the Tracer Tokens tab in Replication Monitor. Figure 2 shows an example of the Tracer Tokens tab showing latency details for a previously inserted token. To add a new token, click Insert Tracer. Details for existing tokens can be viewed by selecting from the drop-down list on the right.