跟踪复制延迟

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.

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.

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.

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.

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:

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.

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.

    本文转自 Fanr_Zh 博客园博客，原文链接：http://www.cnblogs.com/Amaranthus/archive/2011/04/20/2023037.html，如需转载请自行联系原作者