Muokkaa

Jaa

Measure Latency and Validate Connections for Transactional Replication

  • Artikkeli

Applies to: SQL Server Azure SQL Managed Instance

This topic describes how to measure latency and validate connections for transactional replication in SQL Server 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?

In This Topic

Before You Begin

Limitations and Restrictions

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 Quiesce a Replication Topology (Replication Transact-SQL Programming).

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

  • The Distributor must be Microsoft SQL Server 2005 (9.x) or later.

  • The Publisher must be SQL Server 2005 (9.x) 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 (9.x) or later. If the Subscriber is SQL Server 7.0 or Microsoft SQL Server 2000 (8.x), 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

For information about starting Replication Monitor, see Start the Replication Monitor.

To insert a tracer token and view information on the token

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

  2. Click the Tracer Tokens tab.

  3. Click Insert Tracer.

  4. 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

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

  2. Click the Tracer Tokens tab.

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

  4. 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

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

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

  3. 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

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

  2. 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.

  3. 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

  1. 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.

  2. At the Publisher on the publication database, execute sp_deletetracertokenhistory (Transact-SQL), specifying @publication and the ID of the tracer to delete from step 2 for @tracer_id.

Example (Transact-SQL)

This example posts a tracer token record and uses the returned ID of the posted tracer token to view latency information.

DECLARE @publication AS sysname;
DECLARE @tokenID AS int;
SET @publication = N'AdvWorksProductTran'; 

USE [AdventureWorks2022]

-- Insert a new tracer token in the publication database.
EXEC sys.sp_posttracertoken 
  @publication = @publication,
  @tracer_token_id = @tokenID OUTPUT;
SELECT 'The ID of the new tracer token is ''' + 
    CONVERT(varchar,@tokenID) + '''.'
GO

-- Wait 10 seconds for the token to make it to the Subscriber.
WAITFOR DELAY '00:00:10';
GO

-- Get latency information for the last inserted token.
DECLARE @publication AS sysname;
DECLARE @tokenID AS int;
SET @publication = N'AdvWorksProductTran'; 

CREATE TABLE #tokens (tracer_id int, publisher_commit datetime)

-- Return tracer token information to a temp table.
INSERT #tokens (tracer_id, publisher_commit)
EXEC sys.sp_helptracertokens @publication = @publication;
SET @tokenID = (SELECT TOP 1 tracer_id FROM #tokens
ORDER BY publisher_commit DESC)
DROP TABLE #tokens

-- Get history for the tracer token.
EXEC sys.sp_helptracertokenhistory 
  @publication = @publication, 
  @tracer_id = @tokenID;
GO

Using Replication Management Objects (RMO)

To post a tracer token to a transactional publication

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. Create an instance of the TransPublication class.

  3. Set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the connection created in step 1.

  4. 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.

  5. 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

  1. Create a connection to the Distributor by using the ServerConnection class.

  2. Create an instance of the PublicationMonitor class.

  3. Set the Name, DistributionDBName, PublisherName, and PublicationDBName properties, and set the ConnectionContext property to the connection created in step 1.

  4. 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.

  5. Call the EnumTracerTokens method. Cast the returned ArrayList object to an array of TracerToken objects.

  6. 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.

To remove tracer tokens

  1. Create a connection to the Distributor by using the ServerConnection class.

  2. Create an instance of the PublicationMonitor class.

  3. Set the Name, DistributionDBName, PublisherName, and PublicationDBName properties, and set the ConnectionContext property to the connection created in step 1.

  4. 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.

  5. Call the EnumTracerTokens method. Cast the returned ArrayList object to an array of TracerToken objects.

  6. Call the CleanUpTracerTokenHistory method. Pass one of the following values:

    • The TracerTokenId for a tracer token from step 5. This deletes information for a selected token.

    • A DateTime object. This deletes information for all tokens older than the specified date and time.