How to monitor latency in replication?

1.	How to monitor latency in replication?
Answer» There are three methods. Replication monitor Replication commands Tracer Tokens Replication Monitor: In the replication monitor from the list of all subscriptions just double click on the DESIRED subscription. There we find three tabs. Publisher to Distributor History Distributor to Subscriber History Undistributed commands Replication Commands: Publisher.SP_ReplTran: Checks the pending transactions at p Distributor.MSReplCommands and MSReplTransactions: Gives the transactions and commands details. Actual T_SQL data is in binary format. From the entry TIME, we can estimate the LATENCY. Distributor.SP_BrowseReplCmds: It shows the eaxct_seqno along with the corresponding T-SQL command sp_replmonitorsubscriptionpendingcmds: It shows the total number of pending commands to be applied at subscriber along with the estimated time. Tracer Tokens: Available from Replication Monitor or via TSQL statements, Tracer Tokens are special timestamp transactions written to the Publisher’s Transaction Log and picked up by the Log Reader. They are then read by the Distribution Agent and written to the Subscriber. Timestamps for each step are recorded in tracking tables in the Distribution Database and can be displayed in Replication Monitor or via TSQL statements. When Log Reader picks up Token it records time in MStracer_tokens table in the Distribution database. The Distribution Agent then picks up the Token and records Subscriber(s) write time in the MStracer_history tables ALSO in the Distribution database. Below is the T-SQL code to use Tracer tokens to troubleshoot the latency issues. –A SQL Agent JOB to insert a new Tracer Token in the publication database. USE [AdventureWorks] Go EXEC sys.sp_posttracertoken @publication = <PublicationName> Go –Token Tracking Tables USE Distribution Go –publisher_commit SELECT Top 20 * FROM MStracer_tokens Order by tracer_id DESC –subscriber_commit SELECT Top 20 * FROM MStracer_history Order by parent_tracer_id desc

Answer»

There are three methods.

Replication monitor
Replication commands
Tracer Tokens

Replication Monitor: In the replication monitor from the list of all subscriptions just double click on the DESIRED subscription. There we find three tabs.
- Publisher to Distributor History
- Distributor to Subscriber History
- Undistributed commands
Replication Commands:
- Publisher.SP_ReplTran: Checks the pending transactions at p
- Distributor.MSReplCommands and MSReplTransactions: Gives the transactions and commands details. Actual T_SQL data is in binary format. From the entry TIME, we can estimate the LATENCY.
- Distributor.SP_BrowseReplCmds: It shows the eaxct_seqno along with the corresponding T-SQL command
- sp_replmonitorsubscriptionpendingcmds: It shows the total number of pending commands to be applied at subscriber along with the estimated time.
Tracer Tokens:

Available from Replication Monitor or via TSQL statements, Tracer Tokens are special timestamp transactions written to the Publisher’s Transaction Log and picked up by the Log Reader. They are then read by the Distribution Agent and written to the Subscriber. Timestamps for each step are recorded in tracking tables in the Distribution Database and can be displayed in Replication Monitor or via TSQL statements.

When Log Reader picks up Token it records time in MStracer_tokens table in the Distribution database. The Distribution Agent then picks up the Token and records Subscriber(s) write time in the MStracer_history tables ALSO in the Distribution database.

Below is the T-SQL code to use Tracer tokens to troubleshoot the latency issues.

–A SQL Agent JOB to insert a new Tracer Token in the publication database. USE [AdventureWorks] Go EXEC sys.sp_posttracertoken @publication = <PublicationName> Go –Token Tracking Tables USE Distribution Go –publisher_commit SELECT Top 20 * FROM MStracer_tokens Order by tracer_id DESC –subscriber_commit SELECT Top 20 * FROM MStracer_history Order by parent_tracer_id desc

How to monitor latency in replication?

Discussion

No Comment Found

Related InterviewSolutions

Reply to Comment