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
-
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.
-
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.
-
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
-
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
-
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.
-
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
-
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.
-
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.......