แก้ไข

แชร์ผ่าน

Programmatically Monitor Replication

  • บทความ

Applies to: SQL Server Azure SQL Managed Instance

Replication Monitor is a graphical tool that allows you to monitor a replication topology. You can access the same monitoring data programmatically by using Transact-SQL replication stored procedures or replication management objects (RMO). These objects 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.

  • Monitor the status of tracer tokens.

In this topic:

Transact-SQL

Replication Management Objects (RMO)

Transact-SQL

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 monitor Merge Agent sessions

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

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

  3. Repeat step 2 for each session of interest.

To monitor Merge Agent sessions for pull subscriptions from the Subscriber

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

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

  3. Repeat step 2 for each session of interest.

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

Replication Management Objects (RMO)

To monitor a subscription to a merge publication at the Subscriber

  1. Create a connection to the Subscriber by using the ServerConnection class.

  2. Create an instance of the MergeSubscriberMonitor class, and set the Publisher, Publication, PublisherDB, SubscriberDB properties for the subscription, and set the ConnectionContext property to the ServerConnection created in step 1.

  3. Call one of the following methods to return information on Merge Agent sessions for this subscription:

  4. (Optional) Call RefreshSessionSummary to refresh the data for the MergeSessionSummary object passed as mss, or call RefreshSessionSummary to refresh the data in the DataRow object passed as drRefresh.

  5. Using the session ID obtained in step 3, call one of the following methods to return information on the details of a particular session:

To monitor replication properties for all publications at a Distributor

  1. Create a connection to the Distributor by using the ServerConnection class.

  2. Create an instance of the ReplicationMonitor class.

  3. Set the ConnectionContext property to the ServerConnection created in step 1.

  4. Call the LoadProperties method to get the properties of the object.

  5. Execute one or more of the following methods to return replication information for all Publishers that use this Distributor.

To monitor publication properties for a specific Publisher at the Distributor

  1. Create a connection to the Distributor by using the ServerConnection class.

  2. Get a PublisherMonitor object in one of these ways.

  3. Execute one or more of the following methods to return replication information for all publications that belong to this Publisher.

To monitor properties for a specific publication at the Distributor

  1. Create a connection to the Distributor by using the ServerConnection class.

  2. Get a PublicationMonitor object in one of these ways.

  3. Execute one or more of the following methods to return information about this publication.

To monitor transactional commands that are waiting to be applied at the Subscriber

  1. Create a connection to the Distributor by using the ServerConnection class.

  2. Get a PublicationMonitor object in one of these ways.

  3. Execute the TransPendingCommandInfo method, which returns a PendingCommandInfo object.

  4. Use the properties of this PendingCommandInfo object to determine the estimated number of pending commands and the length of time it will take to complete the delivery of these commands.

To set the monitor warning thresholds for a publication

  1. Create a connection to the Distributor by using the ServerConnection class.

  2. Get a PublicationMonitor object in one of these ways.

  3. Execute the EnumMonitorThresholds method. Note the current threshold settings in the returned ArrayList of MonitorThreshold objects.

  4. Execute the ChangeMonitorThreshold method. Pass the following parameters:

    • metricID - an Int32 value that represents the monitoring threshold metric from the following table:

      Value Description
      1 expiration - monitors for imminent expiration of subscriptions to transactional publications.
      2 latency - monitors for the performance of subscriptions to transactional publications.
      4 mergeexpiration - monitors for imminent expiration of subscriptions to merge publications.
      5 mergeslowrunduration - monitors the duration of merge synchronizations over low-bandwidth (dialup) connections.
      6 mergefastrunduration - monitors the duration of merge synchronizations over high-bandwidth (LAN) connections.
      7 mergefastrunspeed - monitors the synchronization rate of merge synchronizations over high-bandwidth (LAN) connections.
      8 mergeslowrunspeed - monitors the synchronization rate of merge synchronizations over low-bandwidth (dialup) connections.

    • enable - Boolean value that indicates whether the metric is enabled for the publication.

    • thresholdValue - integer value that sets the threshold.

    • shouldAlert - integer that indicates whether this threshold should generate an alert.