How to monitor the Health of SQL Server Replication
Today someone asked me how to “roll their own” SQL Replication Monitor to keep an eye on the health of SQL Server Replication. You’ve seen from my previous post I usually use a TracerToken job to monitor SQL Transactional Replication and query against MSmerge_sessions table for Merge Replication. However, you can also create your own “Replication Health Check” stored procedures which execute the same queries used by SQL Replication Monitor.
The SQL Books Online covered these in great detail. Search on INDEX for these stored procedures. The BOL topic include syntax and output.
-
- sp_replmonitorhelppublication
- sp_replmonitorhelppublisher
- sp_replmonitorhelpsubscription
- sp_replmonitorsubscriptionpendingcmds
Or use the links below as a starting point to read more about writing your own code..
How to: Programmatically Monitor Replication (Replication Transact-SQL Programming)
— and for the coders —
How to: Programmatically Monitor Replication (RMO Programming)
If you have sample code or links to related topic, COMMENT this blog posting and I’ll your information on this blog.
You may also want to take a look at NMSSQLReplication (github.com/…/NMSSQLReplication) which is a .Net component abstracting the stored procedures away into .Net classes. The lightweigt MSSQL ReplicationMonitor Service (github.com/…/MSSQLReplicationMonitorService) is based on this component which makes the replication information available over HTTP so you can easily integrate the replication status into your monitoring system of your choice.