Delen via

  • Artikel

Measuring Latency and Validating Connections for Transactional Replication

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 measure latency and validate connections for transactional replication

Tracer tokens can also be useful when quiescing a system, which involves stopping all activity and verifying that all nodes have received all outstanding changes. For more information, see How to: Quiesce a Replication Topology (Replication Transact-SQL Programming).

Considerations for Tracer Tokens

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.