Monitor Replication 複寫監控

Monitor Replication 複寫監控

Measure Latency and Validate Connections for Transactional Replication

使用Tracer Token監控複寫延遲

保留延遲紀錄作法請參考我這篇

Programmatically Monitor Replication--Programmatically Monitor Replication

USE [distribution]
GO
sp_replmonitorhelppublisher
GO

sp_replmonitorhelppublisher (Transact-SQL)

USE [distribution]
GO
sp_replmonitorhelppublication @publisher = N'SQLDEV1'
GO

sp_replmonitorhelppublication (Transact-SQL)

USE [distribution]
GO
sp_replmonitorhelpsubscription @publisher = N'SQLDEV1', @publication_type = 0
GO

sp_replmonitorhelpsubscription (Transact-SQL)

USE [distribution]
 GO
 sp_replmonitorsubscriptionpendingcmds
 @publisher = N'SQLDEV1',
 @publisher_db = 'AdventureWorks2008R2',
 @publication = 'Orders',
 @subscriber = 'SQLDEVSUB1',
 @subscriber_db = 'AdventureWorks2008R2',
 @subscription_type = 0
 GO

Result Sets
Column name Data type Description
pendingcmdcount int The number of commands that are pending for the subscription.
estimatedprocesstime int Estimate of the number of seconds required to deliver all of the pending commands to the Subscriber.

sp_replmonitorsubscriptionpendingcmds (Transact-SQL)

Monitoring Replication with System Monitor (複寫相關效能計數器Performance Counter)
https://docs.microsoft.com/en-us/sql/relational-databases/replication/monitor/monitoring-replication-with-system-monitor

Agent	Performance object	Counter	Description
All agents	Microsoft SQL Server: Replication Agents	Running	The number of replication agents currently running.
Snapshot Agent	SQL Server: Replication Snapshot	Snapshot: Delivered Cmds/sec	The number of commands per second delivered to the Distributor.
Snapshot Agent	SQL Server: Replication Snapshot	Snapshot: Delivered Trans/sec	The number of transactions per second delivered to the Distributor.
Log Reader Agent	SQL Server: Replication Logreader	Logreader: Delivered Cmds/sec	The number of commands per second delivered to the Distributor.
Log Reader Agent	SQL Server: Replication Logreader	Logreader: Delivered Trans/sec	The number of transactions per second delivered to the Distributor.
Log Reader Agent	SQL Server: Replication Logreader	Logreader: Delivery Latency	The current amount of time, in milliseconds, elapsed from when transactions are applied at the Publisher to when they are delivered to the Distributor.
Distribution Agent	SQL Server: Replication Dist.	Dist: Delivered Cmds/sec	The number of commands per second delivered to the Subscriber.
Distribution Agent	SQL Server: Replication Dist.	Dist: Delivered Trans/sec	The number of transactions per second delivered to the Subscriber.
Distribution Agent	SQL Server: Replication Dist.	Dist: Delivery Latency	The current amount of time, in milliseconds, elapsed from when transactions are delivered to the Distributor to when they are applied at the Subscriber.
Merge Agent	SQL Server: Replication Merge	Conflicts/sec	The number of conflicts per second occurring during the merge process.
Merge Agent	SQL Server: Replication Merge	Downloaded Changes/sec	The number of rows per second replicated from the Publisher to the Subscriber.
Merge Agent	SQL Server: Replication Merge	Uploaded Changes/sec	The number of rows per second replicated from the Subscriber to the Publisher.