In this article

This topic describes how to measure latency and validate connections for transactional replication in SQL Server 2017 by using Replication Monitor, Transact-SQL, or Replication Management Objects (RMO). Transactional replication provides the tracer token feature, which provides a convenient way to measure latency in transactional replication topologies and to validate the connections between the Publisher, Distributor and Subscribers. A token (a small amount of data) is written to the transaction log of the publication database, marked as though it were a typical replicated transaction, and sent through the system, allowing a calculation of:

How much time elapses between a transaction being committed at the Publisher and the corresponding command being inserted in the distribution database at the Distributor.

How much time elapses between a command being inserted in the distribution database and the corresponding transaction being committed at a Subscriber.

From these calculations, you can answer a number of questions, including:

Which Subscribers take the longest to receive a change from the Publisher?

Of the Subscribers expected to receive the tracer token, which, if any, have not received it?

To use tracer tokens, you must use certain versions of Microsoft SQL Server:

The Distributor must be Microsoft SQL Server 2005 or later.

The Publisher must be SQL Server 2005 or later or be an Oracle Publisher.

For push subscriptions, tracer token statistics are gathered from the Publisher, Distributor, and Subscribers if the Subscriber is Microsoft SQL Server 7.0 or later.

For pull subscriptions, tracer token statistics are gathered from Subscribers only if the Subscriber is SQL Server 2005 or later. If the Subscriber is SQL Server 7.0 or Microsoft SQL Server 2000, statistics are gathered only from the Publisher and Distributor.

There are also a number of other issues and restrictions to be aware of:

Subscriptions must be active to receive a tracer token. A subscription is active if it has been initialized.

Reinitialization removes any pending tracer tokens for the relevant subscriptions.

Subscribers only receive tracer tokens that were created after their initial synchronization.

Tracer tokens are not forwarded by republishing Subscribers.

After failover to a secondary, Replication Monitor is unable to adjust the name of the publishing instance of SQL Server and will continue to display replication information under the name of the original primary instance of SQL Server. After failover, a tracer token cannot be entered by using the Replication Monitor, however a tracer token entered on the new publisher by using Transact-SQL, is visible in Replication Monitor.

Using SQL Server Replication Monitor

To insert a tracer token and view information on the token

Expand a Publisher group in the left pane, expand a Publisher, and then click a publication.

Click the Tracer Tokens tab.

Click Insert Tracer.

View elapsed time for the tracer token in the following columns: Publisher to Distributor, Distributor to Subscriber, Total Latency. A value of Pending indicates that the token has not reached a given point.

To view information on a tracer token inserted previously

Expand a Publisher group in the left pane, expand a Publisher, and then click a publication.

Click the Tracer Tokens tab.

Select a time from the Time inserted drop-down list.

View elapsed time for the tracer token in the following columns: Publisher to Distributor, Distributor to Subscriber, Total Latency. A value of Pending indicates that the token has not reached a given point.

Note

Tracer token information is retained for the same time period as other historical data, which is governed by the history retention period of the distribution database. For information about changing distribution database properties, see View and Modify Distributor and Publisher Properties.

Using Transact-SQL

To post a tracer token to a transactional publication

(Optional) At the Publisher on the publication database, execute sp_helppublication (Transact-SQL). Verify that the publication exists and that the status is active.

At the Publisher on the publication database, execute sp_posttracertoken (Transact-SQL), specifying @publication. Note the value of the @tracer_token_id output parameter.

To determine latency and validate connections for a transactional publication

Post a tracer token to the publication using the previous procedure.

At the Publisher on the publication database, execute sp_helptracertokens (Transact-SQL), specifying @publication. This returns a list of all tracer tokens posted to the publication. Note the desired tracer_id in the result set.

At the Publisher on the publication database, execute sp_helptracertokenhistory (Transact-SQL), specifying @publication and the tracer token ID from step 2 for @tracer_id. This returns latency information for the selected tracer token.

To remove tracer tokens

At the Publisher on the publication database, execute sp_helptracertokens (Transact-SQL), specifying @publication. This returns a list of all tracer tokens posted to the publication. Note the tracer_id for the tracer token to delete in the result set.

Call the LoadProperties method to get the properties of the object. If this method returns false, either the publication properties in step 3 were defined incorrectly or the publication does not exist.

Call the PostTracerToken method. This method inserts a tracer token into the publication's transaction log.

To determine latency and validate connections for a transactional publication

Call the LoadProperties method to get the properties of the object. If this method returns false, either the publication monitor properties in step 3 were defined incorrectly or the publication does not exist.

Call the EnumTracerTokenHistory method. Pass a value of TracerTokenId for a tracer token from step 5. This returns latency information for the selected tracer token as a DataSet object. If all tracer token information is returned, the connection between the Publisher and Distributor and the connection between the Distributor and the Subscriber both exist and the replication topology is functioning.

Call the LoadProperties method to get the properties of the object. If this method returns false, either the publication monitor properties in step 3 were defined incorrectly or the publication does not exist.