March 14, 2014 at 10:01 am
I was wondering if anyone here had any favorite tool for monitoring replication between SQL servers...
We have a database that we use to a very large list of parts, and the database is maintained on one server; and then a few specific tables are replicated to a copy of the database on another server via Transactional replication for reporting purposes. Lately we've been having some performance issues that seem to be related more to network activity than anything happening in SQL itself. That is, we're seeing a lot of latency even when the changes made on the publisher are very small, and there isn't much going on on either SQL instance.
And frankly, the Replication Monitor that comes with SQL Management Studio is a bit lacking. For example, I can open Replication Monitor and see the current latency, but not the history - and since this has been an intermittent problem, the history would be helpful.
I believe I can set replication to use verbose logging and find this information in the tables it creates in the distribution database; I was just wondering if anyone here knew of a more robust monitoring tool that would provide more functionality.
Thanks in advance.
March 17, 2014 at 5:34 am
One easy solution to build up a history is to script a job to run as frequently as you want to insert a tracer token, you can then view the tracer token history per publication in replication monitor giving you the latency at that time.
Another option is not create a custom job to log the number of undistributed commands in ms_repl_commands in the distribution database. Again over time this will give you an idea as to how many commands have yet to replicate at a specific point in time.
MCITP SQL 2005, MCSA SQL 2012
March 17, 2014 at 2:15 pm
RTaylor2208 (3/17/2014)
One easy solution to build up a history is to script a job to run as frequently as you want to insert a tracer token, you can then view the tracer token history per publication in replication monitor giving you the latency at that time.Another option is not create a custom job to log the number of undistributed commands in ms_repl_commands in the distribution database. Again over time this will give you an idea as to how many commands have yet to replicate at a specific point in time.
Thanks, will look into both of those.
So far, I've created a report with a chart showing the latency times recorded in MSdistribution_history which works fairly well, but that table doesn't seem to be updated very often.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply