Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

How do I monitor problems with replication? - SQL Server Knowledge Sharing Network (@sqlmaster)

How do I monitor problems with replication?

When you setup a program or application then you have equal responsibility to monitor the process/problems too.

Similar to this monitoring a replication topology is an important aspect of deploying SQL Server replication. As the process is distributed and it is essential to track activity and status across all computers involved in replication. We have bunch of tools that can help the user/DBA/Developer to monitor replication, they are:

  • Replication Monitor: most important tool for monitoring replication, presenting a Publisher-focused view of all replication activity. For more information, see Monitoring Replication with Replication Monitor.

  • SQL Server Management Studio:provides access to Replication Monitor. It also allows you to view the current status and last message logged by the following agents and allows you start and stop each agent: Log Reader Agent, Snapshot Agent, Merge Agent, and Distribution Agent.

  • TSQL and RMO: both interfaces allow you to monitor all types of replication from the Distributor. Merge replication also provides the ability to monitor replication from the Subscriber.

  • Alerts for replication agent events: provides a number of predefined alerts for replication agent events, and you can create additional alerts if necessary. Alerts can be used to trigger an automated response to an event and/or notify an administrator. For more information, see Using Alerts for Replication Agent Events.

  • System Monitor: a best tool for monitoring performance, providing a number of counters for replication. For more information, see Monitoring Replication with System Monitor.
If you observe all these aspects are documented in BOL thoroughly with a code example too. In general within any organisation having a good list of tools.
Also it is a good practice to send replication events (like any other events of interest) to the eventlogs and then use tools that monitor
the required logs to send notification. FOr a better consistency of alerting and notification you must test them thoroughly.
From BOL:

To monitor Publishers, publications, and subscriptions from the Distributor

  1. At the Distributor on the distribution database, execute sp_replmonitorhelppublisher. This returns monitoring information for all Publishers using this Distributor. To limit the result set to a single Publisher, specify @publisher.

  2. At the Distributor on the distribution database, execute sp_replmonitorhelppublication. This returns monitoring information for all publications using this Distributor. To limit the result set to a single Publisher, publication, or published database, specify @publisher, @publication, or @publisher_db, respectively.

  3. At the Distributor on the distribution database, execute sp_replmonitorhelpsubscription. This returns monitoring information for all subscriptions using this Distributor. To limit the result set to subscriptions belonging to a single Publisher, publication, or published database, specify @publisher, @publication, or @publisher_db, respectively.

To monitor transactional commands waiting to be applied at the Subscriber

  1. At the Distributor on the distribution database, execute sp_replmonitorsubscriptionpendingcmds. This returns monitoring information for all commands pending for all subscriptions using this Distributor. To limit the result set to commands pending for subscriptions belonging to a single Publisher, Subscriber, publication, or published database, specify @publisher, @subscriber, @publication, or @publisher_db, respectively.

 

To monitor merge changes waiting to be uploaded or downloaded

  1. At the Publisher on the publication database, execute sp_showpendingchanges. This returns a result set showing information on changes that are waiting to be replicated to Subscribers. To limit the result set to changes that belong to a single publication or article, specify @publication or @article, respectively.

  2. At a Subscriber on the subscription database, execute sp_showpendingchanges. This returns a result set showing information on changes that are waiting to be replicated to the Publisher. To limit the result set to changes that belong to a single publication or article, specify @publication or @article, respectively.

To view and modify the monitor threshold metrics for a publication

  1. At the Distributor on the distribution database, execute sp_replmonitorhelppublicationthresholds. This returns the monitoring thresholds set for all publications using this Distributor. To limit the result set to monitor thresholds to publications belonging to a single Publisher or published database or to a single publication, specify @publisher, @publisher_db, or @publication, respectively. Note the value of Metric_id for any thresholds that must be changed. For more information, see Setting Thresholds and Warnings in Replication Monitor.

  2. At the Distributor on the distribution database, execute sp_replmonitorchangepublicationthreshold. Specify the following as needed:

    • The Metric_id value obtained in step 1 for @metric_id.

    • A new value for the monitor threshold metric for @value.

    • A value of 1 for @shouldalert for an alert to be logged when this threshold is reached, or a value of 0 if an alert is not needed.

    • A value of 1 for @mode to enable the monitor threshold metric or a value of 2 to disable it.
The problem is many users who ask the questions doesn't know how to get information out of BOL, so it is better to do this one time job of download the latest version and search for information.
More to come.......
Published Saturday, November 7, 2009 4:44 PM by ssqa.net

Comments

# Ssqa publication | Dimitribiriouk

Pingback from  Ssqa publication | Dimitribiriouk

Wednesday, November 28, 2012 10:09 PM by Ssqa publication | Dimitribiriouk