SQL Server Performance

Monitoring replication

Discussion in 'SQL Server 2005 Replication' started by Fais, Apr 14, 2009.

  1. Fais New Member

    Hi All,
    I configured Transactional replication on my staging server and will be configuring transactional replication on Production as well.
    I would like to know , what are some of the best strategies to Monitor replication .
    Gurus : Please share your thoughts, links,websites, codes.
  2. techbabu303 New Member

    Here are some of things you can keep eye on apart from knowing the replication is not GA solution which is a common myth.

    1> Use replication monitor provided SQL server Management studio to(SMO) view the history of transactions.

    2> Check the Job activity monitor for any failure in replication jobs this also included with SMO.

    3> Do network test periodically since transaction replication works between servers with ping response < 10 ms , any latency can cause issues. Network congestion can increase latency, which can increase the recovery time.

    Latency is the time elapsed between committing a transaction at the primary database and committing that transaction at the standby database.

    4> Merge replication and snapshot replication do not affect transaction log size, but transactional replication can. Also, the transaction log can't be truncated unless the log reader agent has marked it as read,which means the log might potentially balloon in size.Moral of stroy is monitor log size periodically

    5> Sometimes under extreme high load you will get deadlocking between the log reader agent and the distribution clean up agent. In this case stopping the log reader agent to let the distribution clean up agent do its job will alleviate the problem. It is recommended that in this case you use a remote distributor.


Share This Page