Simple steps to monitor Replication activities
There are few simple steps involved to monitor the replication actvites, as usual with Replication Monitor is useful to monitor the overall health of a replication topology. Replication Monitor provides detailed information on the status and performance of publications and subscriptions, allowing you to answer common questions, such as (BOL):
- Is my replication system healthy?
- Which subscriptions are slow?
- How far behind is my transactional subscription?
- How long will it take a transaction committed now to reach a Subscriber in transactional replication?
- Why is my merge subscription slow?
- Why is an agent not running?
So using this GUI it is easy to get an overview of the Replication Monitor Interface, still there is lot left to monitor activities such as viewing Publication & Subscription status, setting thresholds, warnings and monitoring performance. For the Transactional Replication validation of connections and measuring latency is more important.
The performance for the replication instance are handled by Cache Refresh which in turn handled by a SQL Server Agent job, the Replication monitoring refresher for distribution. The job runs continuously, but the cache refresh schedule is based on waiting a certain amount time after the previous refresh:
- If there were agent history changes since the cache was last created, the wait time is the minimum of: 4 seconds; or the amount of time taken to create the previous cache.
- If there were no agent history changes since the cache was last created (there could have been other changes), the wait time is the maximum of: 30 seconds; or the amount of time taken to create the previous cache.
So to get statsitics about replication counters using SP_REPLCOUNTERS is very helpful, in turn you can run a scheduled job to capture these counters to a table within your monitoring database is useful to analyze the historical trend. This statement returns replication statistics about latency, throughput, and transaction count for each published database.
Say you need to troubleshoot the issues within the replication that was found through the Repl.Monitor, SP_REPLCMDS is useful. This will generate commands for owner-qualified tables or not qualify the table name (the default). Adding qualified table names allows replication of data from tables owned by a specific user in one database to tables owned by the same user in another database.
Within the transactional replication SP_REPLDONE is important to track the log reader processed transactions, using this statement you can also perform a manual taks on the server that a transaction has been replicated (sent to the Distributor). ONce this is completed successful the publisher allows you to change the transaction marked as the next one awaiting replication. But only if you are confident in handling such transactions without any mismatch do not attempt to run this statement. Similarly SP_REPLTRANS is required within this process, that returns information about the publication database from which it is executed, allowing you to view transactions currently not distributed (those transactions remaining in the transaction log that have not been sent to the Distributor). Obviously the total transaction validation is performed on the LSN of that processes.
Rarely (I think) you may need SP_REPLFLUSH within the transactional replication to address the caching issues within the publisher & distributor, but never ever attempt to execute this procedure manually. As this should only be used for troubleshooting replication as directed by an experienced replication support professional.
Don't forget to visit the list of "How To..?" sections within the Books online to troubleshoot or monitor your replication setup.
