How to: Programmatically Monitor Replication (RMO Programming)

Replication Monitor is a graphical tool that allows you to monitor replication topology. You can access the same monitoring data programmatically by using Replication Management Objects (RMO). RMO classes enable you to program the following tasks:

• Monitor results of the last five Merge Agent runs at the Subscriber.
• Monitor the state of Publishers, publications, and subscriptions.
• Monitor transactional commands that are 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. For more information, see How to: Measure Latency and Validate Connections for Transactional Replication (RMO Programming).

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:

   • GetSessionsSummary - returns an array of MergeSessionSummary objects with information on up to the last five Merge Agent sessions. Note the SessionID value for any sessions of interest.
   • GetSessionsSummary - returns an array of MergeSessionSummary objects with information on Merge Agent sessions that have occurred during the past number of hours passed in as the hours parameter (up to the last five sessions). Note the SessionID value for any sessions of interest.
   • GetLastSessionSummary - returns a MergeSessionSummary object with information on the last Merge Agent session. Note the SessionID value for this session.
   • GetSessionsSummaryDataSet - returns a DataSet object with information on up to the last five Merge Agent sessions, one in each row. Note the value of the Session_id column for any sessions of interest.
   • GetLastSessionSummaryDataRow - returns a DataRow object with information on the last Merge Agent session. Note the value of the Session_id column for this session.

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:

   • GetSessionDetails - returns an array of MergeSessionDetail objects for the supplied sessionID.
   • GetSessionDetailsDataSet - returns a DataSet object with information for the specified sessionID.

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.

   • EnumDistributionAgents - returns a DataSet object that contains information about all Distribution Agents at this Distributor.
   • EnumErrorRecords - returns a DataSet object that contains information about errors stored at the Distributor.
   • EnumLogReaderAgents - returns a DataSet object that contains information about all Log Reader Agents at the Distributor.
   • EnumMergeAgents - returns a DataSet object that contains information about all Merge Agents at the Distributor.
   • EnumMiscellaneousAgents - returns a DataSet object that contains information about all other replication agents at the Distributor.
   • EnumPublishers - returns a DataSet object that contains information about all Publishers at this Distributor.
   • EnumPublishers2 - returns a DataSet object that returns the Publishers that use this Distributor.
   • EnumQueueReaderAgents - returns a DataSet object that contains information about all Queue Reader Agents at the Distributor.
   • EnumQueueReaderAgentSessionDetails - returns a DataSet object that contains details about the specified Queue Reader Agent and session.
   • EnumQueueReaderAgentSessions - returns a DataSet object that contains session information about the specified Queue Reader Agent.
   • EnumSnapshotAgents - returns a DataSet object that contains information about all Snapshot Agents at the 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.

   • Create an instance of the PublisherMonitor class. Set the Name property for the Publisher, and set the ConnectionContext property to the ServerConnection created in step 1. Call the LoadProperties method to get the properties of the object. If this method returns false, either the Publisher name was defined incorrectly or the publication does not exist.
   • From the PublisherMonitorCollection accessed by means of the PublisherMonitors property of an existing ReplicationMonitor object.

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

   • EnumDistributionAgentSessionDetails - returns a DataSet object that contains details about the specified Distribution Agent and session.
   • EnumDistributionAgentSessions - returns a DataSet object that contains session information about the specified Distribution Agent.
   • EnumErrorRecords - returns a DataSet object that contains error record information about the specified error.
   • EnumLogReaderAgentSessionDetails - returns a DataSet object that contains details for the specified Log Reader Agent and session.
   • EnumLogReaderAgentSessions - returns a DataSet object that contains session information for the specified Log Reader Agent.
   • EnumMergeAgentSessionDetails - returns a DataSet object that contains details about the specified Merge Agent and session.
   • EnumMergeAgentSessionDetails2 - returns a DataSet object that contains additional details about the specified Merge Agent and session.
   • EnumMergeAgentSessions - returns a DataSet object that contains session information for the specified Merge Agent.
   • EnumMergeAgentSessions2 - returns a DataSet object that contains additional session information for the specified Merge Agent.
   • EnumPublications - returns a DataSet object that contains information about all publications at this Distributor.
   • EnumPublications2 - returns a DataSet object that contains additional information about all publications at this Distributor.
   • EnumSnapshotAgentSessionDetails - returns a DataSet object that contains details about the specified Snapshot Agent and session.
   • EnumSnapshotAgentSessions - returns a DataSet object that contains session information for the specified Snapshot Agent.
   • EnumSubscriptions - returns a DataSet object that contains information about all subscriptions to publications at this Distributor.

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.

   • Create an instance of the PublicationMonitor class. Set the DistributionDBName, PublisherName, PublicationDBName, and Name properties for the publication, and set the ConnectionContext property to the ServerConnection created in step 1. Call the LoadProperties method to get the properties of the object. If this method returns false, either the publication properties were defined incorrectly or the publication does not exist.
   • From the PublicationMonitorCollection accessed by means of the PublicationMonitors property of an existing PublisherMonitor object.

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

   • EnumErrorRecords - returns a DataSet object that contains error records about the specified error.
   • EnumLogReaderAgent - returns a DataSet object that contains information about the Log Reader Agent for this publication.
   • EnumMonitorThresholds - returns a DataSet object that contains information about the monitor warning thresholds set for this publication.
   • EnumQueueReaderAgent - returns a DataSet object that contains information about the Queue Reader Agent used by this publication.
   • EnumSnapshotAgent - returns a DataSet object that contains information about the Snapshot Agent for this publication.
   • EnumSubscriptions - returns a DataSet object that contains information about subscriptions to this publication.
   • EnumSubscriptions2 - returns a DataSet object that contains additional information about subscriptions to this publication based on the supplied SubscriptionResultOption.
   • EnumTracerTokenHistory - returns a DataSet object that contains latency information for the specified tracer token.
   • EnumTracerTokens - returns a DataSet object that contains information about all tracer tokens inserted into 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.

   • Create an instance of the PublicationMonitor class. Set the DistributionDBName, PublisherName, PublicationDBName, and Name properties for the publication, and set the ConnectionContext property to the ServerConnection created in step 1. Call the LoadProperties method to get the properties of the object. If this method returns false, either the publication properties were defined incorrectly or the publication does not exist.
   • From the PublicationMonitorCollection accessed by means of the PublicationMonitors property of an existing PublisherMonitor object.

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.

   • Create an instance of the PublicationMonitor class. Set the DistributionDBName, PublisherName, PublicationDBName, and Name properties for the publication, and set the ConnectionContext property to the ServerConnection created in step 1. Call the LoadProperties method to get the properties of the object. If this method returns false, either the publication properties were defined incorrectly or the publication does not exist.
   • From the PublicationMonitorCollection accessed by means of the PublicationMonitors property of an existing PublisherMonitor object.

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.

See Also

Tasks

How to: Programmatically Monitor Replication (Replication Transact-SQL Programming)

Other Resources

Monitoring Replication

Help and Information

Getting SQL Server 2005 Assistance