How to Measure SQL Server Transactional Replication Latency Using Trace Tokens


Dec 12, 2013

Introduction

Microsoft introduced Trace Tokens feature in SQL Server 2005 to measure SQL Server transactional replication latency. A trace token is nothing but a small amount of data which is written to the transactional log of the publication database which is marked for replication. Hence, trace tokens can help one measure the latency from Publisher to Distributor and from Distributor to Subscriber. There by, measuring the total latency time from Publisher to Subscriber. However, it is expected that Publisher, Distributor and Subscriber Servers are up and running and they are able to connect to each other.

Replication Monitor

Microsoft SQL Server Replication Monitor is a graphical tool which helps you to monitor the overall health of your replication environment. It is an excellent tool which can help you know the current status and performance of publications and subscriptions.

Where can I find Replication Monitor?

1. Using SQL Server Management Studio connect to SQL Server Instance

2. Expand Server Instance and right click Replication Node and select Launch Replication Monitor from the drop down list as shown in the snippet below.

How to Launch Replication Monitor in SQL Server

How to add a Publisher in Replication Monitor

1. In Replication Monitor, expand Replication Monitor on the left side pane and then right click My Publishers and select Add Publisher… from the drop down list.

2. In Add Publisher window click the Add button and select Add SQL Server Publisher… from the drop down list and specify the Publisher Server Name and connect to the instance.

How to Insert Trace Tokens to Measure SQL Server Transactional Replication Latency

1. In Replication Monitor expand Publisher as shown in the snippet below to view all the publications.

2. In the left side panel choose the publication for which you want to identify latency and then click Insert Tracer button on the right side panel.

How to Insert Tracer Token Using Replication Monitor in Transactional Replication to monitor latency

3. In the above snippet you could see that for the first subscription the total latency is 14 seconds and for the second subscription is 5 seconds. You could also see the latency between Publisher to Distributor and Distributor to Subscriber. If you find that SQL Server Transactional Replication is running slow then the following article has few recommendations to improve the performance Tips to Improve SQL Server Replication Performance.

References

Measure Latency and Validate Connections for Transactional Replication
Monitoring Replication with Replication Monitor

Share this Article


Geeks who read this article also read…




"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."






Follow @MyTechMantra on Twitter