How to: Programmatically Monitor Replication (Replication Transact-SQL Programming)
Replication Monitor is a graphical tool that allows you to monitor a replication topology. You can access the same monitoring data programmatically using replication stored procedures. These stored procedures enable you to program the following tasks:
- Monitor the state of Publishers, publications, and subscriptions.
- Monitor Merge Agent sessions at one or more Subscribers.
- Monitor transactional commands waiting to be applied at one or more Subscribers.
- Define the threshold metrics that determine when a publication requires intervention.
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 monitor Merge Agent sessions
At the Distributor on the distribution database, execute sp_replmonitorhelpmergesession. This returns monitoring information, including Session_id, on all Merge Agent sessions for all subscriptions using this Distributor. You can also obtain Session_id by querying the MSmerge_sessions system table.
At the Distributor on the distribution database, execute sp_replmonitorhelpmergesessiondetail. Specify a Session_id value from step 1 for @session_id. This displays detailed monitor information about the session.
Repeat step 2 for each session of interest.
To monitor Merge Agent sessions for pull subscriptions from the Subscriber
At the Subscriber on the subscription database, execute sp_replmonitorhelpmergesession. For a given subscription, specify @publisher, @publication, and the name of the publication database for @publisher_db. This returns monitoring information for the last five Merge Agent sessions for this subscription. Note the value of Session_id for sessions of interest in the result set.
At the Subscriber on the subscription database, execute sp_replmonitorhelpmergesessiondetail. Specify a Session_id value from step 1 for @session_id. This displays detailed monitoring information about the session.
Repeat step 2 for each session of interest.
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 복제 모니터에 임계값 및 경고 설정.
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.
참고 항목
작업
How to: Programmatically Monitor Replication (RMO Programming)
개념
Programming Replication Using System Stored Procedures