How to Measure SQL Server Transactional Replication Latency Using Trace Tokens
Dec 12, 2013
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.
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 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.
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.
Geeks who read this article also read…
- Tips to Improve SQL Server Replication Performance
- Performance Dashboard Reports in SQL Server 2012
- How to Backup an Analysis Services Database Using SQL Server Management Studio
- How to Restore an Analysis Services Database Using SQL Server Management Studio
- Automate Backup of Analysis Services Database Using SQL Server Agent Job
- How to Attach Analysis Services Database in SQL Server
- How to Detach Analysis Services Database in SQL Server
- How to Backup Database in SQL Server
- How to Restore Database in SQL Server
- How to Attach Database Without a Transaction Log File in SQL Server
- New Features in SQL Server 2012 Reporting Services for Developers
- New Features in SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Read More SQL Server Articles…