SQL Server 2005 has lot of new and improved components, and the Tracer Token in replication is one of them. It allows us to validate connections and helps in measuring latencies between the publisher, distributor and subscriber(s). This functionality allows an accurate calculation of latencies that ultimately helps in finding which subscriber take more time to receive a change from the publisher than expected.
You may be wondering whether it puts an extra load on your system or if it will slow down replication? The answer is NO because it only writes very small amount of data in transaction log of the publication database.
I used this feature recently in a company where they had transaction replication failures almost every night. The SQL Server wasreporting following errors:
Query timeout expired
The agent failed with a 'Retry' status. Try to run the agent at a later time.
I did not want to just setup just any value for the QueryTimeout without knowing what it should be. So, I setup the Tracer Token feature and ran it for two days. Afterwards I knew what value to use and configured the system accordingly. Today, it is the fourth week using that setting, and it has not failed yet.
This is a wonderful new feature SQL Server 2005 introduced, and here is how you can implement it and start using it today.
Method #1. (Replication Monitor)
Launch Replication Monitor, and then Expand the Server => Select Publication
Click on the Tracer Token Tab in right pane and then click on Insert Tracer as shown below
After SQL Server sends the token through the replication process, it will display Latencies as shown below:
The latency will be recorded when you click on Insert Tracer. Every time you click on Insert Tracer, a new date/time will be added in Dropdown box (Time Inserted). Hence, you can view your latency at any given time using this dropdown box provided you had Inserted a Tracer Token.
The Publisher to Distributor column displays the time elapsed between a transaction committed at the Pulisher and the corresponding command entered in the distribution database.
The Distributor to Subscriber column display the time elapsed before the commit of the transaction at the subscriber.
Limitation of using Replication Monitor
There is a limitation in setting up the Tracer Token using Replication Monitor. The Tracer Token has to be inserted manually and It limits you to viewing one value at a time.
To overcome this limitation, we have another method to accomplish same thing.
Method #2. (Thru SQL Server Agent)
You can automate your tracer token insert using this method. You need to follow the steps below:
- Create a SQL Server Agent Job on Publisher Server
- Add a Job Step with the following T-SQL command:
sp_posttracertoken 'xyz' -- substituting xyz for the name of your publication
- Schedule this Job to run at whatever interval you want to insert a token into your replication process.
How to analyze these tracer values?
This data is stored in the distribution database. There are a few system defined stored procedure available to view the recorded data. They are:
- sp_helptracertokens - returns data for every tracer token inserted at publisher for specified publication
- sp_helptracertokenhistory - returns tokenid specific data
But, I have simplified a way to get the data by writing a query mentioned below:
use distribution
GO
select publisher_commit,
distributor_commit,
datediff(ss, publisher_commit, distributor_commit) 'Latency bw Pub and Dis',
subscriber_commit,
datediff(ss, distributor_commit, subscriber_commit) 'Latency bw Dis and Sub'
from MSTracer_tokens join MSTracer_history
on tracer_id = parent_tracer_id
The result of this query will have the following data:
- Date and Time when transaction committed at publisher
- Date and Time when Command for same transaction inserted at Distributor,
- Time (in Seconds) Elapses to commit data between publisher and distributor
- Date and Time when transaction committed at Subscriber
- Time (in Seconds) Elapses to commit data between Distributor and Subscriber.
Conclusion
Tracer Token is excellent feature to measure latencies and validate connections that can be used to validate or set Agent profile settings and/or identify timeout related problem that can cause replication to fail.
