Monitoring Replication Activity in Replication Monitor


Although you might spend hours or even days configuring your replication topology, you are likely to spend months or years monitoring the replication activity of your system. Fortunately, SQL Server 2005 provides Replication Monitor, an excellent tool for watching the performance and activity of your replication agents.

Because monitoring personnel are sometimes separate from the database administrators, Replication Monitor is an independent application from Management Studio (see Figure 12-19). You can launch it directly from Management Studio by using the Launch Replication Monitor command on the context menus of all nodes under the Replication folder in Object Explorer.

Figure 12-19. Replication Monitor.


Replication Monitor is organized around the hierarchy of Publishers, publications, and subscriptions. The starting point is a Publisher, and you can add as many Publishers as you want. Replication Monitor enables you to organize your Publishers into groups, with the default group being My Publishers. After a Publisher is added, Replication Monitor retrieves the list of its publications and subscriptions and their status. This information is automatically refreshed at a frequency that you can set.

The left pane, known as the Navigation Pane, displays a tree with the following levels: Replication Monitor, Publisher group, Publisher, and publication. The right pane, known as the Detail Pane, is a tabbed interface that shows details about the object selected in the Navigation Pane. The Detail Pane often displays a grid that contains a list of subscriptions or replication agents.

Tip

You can see detailed information about a subscription or agent in the Detail Pane by double-clicking its grid row or by right-clicking the row and selecting View Details.


By default, Replication Monitor automatically refreshes the data it is displaying on a regular basis. You can set the frequency of the automatic refresh separately for each Publisher in the Publisher Settings dialog box, which is accessible from the context menu of the Publisher. You can see how long ago the data in the Detail Pane was retrieved by looking at the gray ribbon above the tabs where the time of the last refresh for the selected tab is shown. You will also occasionally see the message "Retrieving data from Distributor. Please wait[...]" when Replication Monitor is querying to get fresh data.

The icons in the panes indicate the type and status of the selected object or its children. If a publication or subscription has a status of Error, Warning, or Retry, the status is marked in the icons of the publication, its Publisher, the Publisher group, and the Replication Monitor root node (see Figure 12-20). In this way, Replication Monitor notifies you of status changes regardless of which nodes are expanded or collapsed. When you see a status that requires your attention, you can expand the marked nodes until you find the publication with the problem.

Figure 12-20. Replication Monitor showing an error.


The icons listed in Table 12-3 are used in Replication Monitor.

Table 12-3. Icons in Replication Monitor

Icon

Meaning

Replication Monitor root node

Publisher group

Publisher

Snapshot publication

Transactional publication

Merge publication

Subscription


The status overlays listed in Table 12-4 can appear on the lower-right corner of the icons.

Table 12-4. Status Overlays in Replication Monitor

Icon

Meaning

Error: An agent has encountered an error. Replication has stopped.

Warning: There are various warnings: low performance, expiring subscription, etc. Replication is still operating, but a potential problem has begun.

Retry: An agent has encountered an error, and is retrying the operation. Replication is still operating, but may stop if the retries are unsuccessful.

Running/Synchronizing: A Distribution Agent is running, or a Merge Agent is synchronizing. (Subscription only)

Not Running/Not Synchronizing: A Distribution Agent is not running, or a Merge Agent is not synchronizing. The agent shut down normally, without an error. (Subscription only)


Information about errors, warnings, and retries is always found in the Detail Pane; the status markers in the Navigation Pane are used only to lead you to the object that needs attention. Because almost all replication activity occurs at the publication level, you will usually select a publication in the Navigation Pane and look at the specifics in the Details Pane.

Adding Publishers to Replication Monitor

To begin monitoring replication, you must add one or more Publishers to Replication Monitor. If you launch Replication Monitor from a publication node in Management Studio, the Publisher is automatically added to Replication Monitor with default settings. To add a Publisher manually, right-click on the root node, a Publisher group, or a Publisher, and click Add Publisher. In the Add Publisher dialog box, click the Add button and select one of the following from the menu: Add SQL Server Publisher, Add Oracle Publisher, or Specify a Distributor and Add Its Publishers. With SQL Server 2005, you can configure an Oracle server to publish through a SQL Server Distributor. You can also configure a server instance to act as the Distributor for one or more Publishers. The Add Publisher dialog box lets you add Publishers from all these configurations.

When you specify the connection to the Publisher or to the Distributor for an Oracle Publisher or when using a remote Distributor, you must also specify how Replication Monitor connects to the server instance. Replication Monitor will remember the connection credentials so that you don't have to enter them when you use Replication Monitor in the future. To monitor replication, the login that you specify must be an administrator on the server instance or a member of the replmonitor fixed database role in the distribution database.

Tip

The only way to create a Publisher group is with the New Group button in the Add Publisher or Publisher Settings dialog box. To change the group to which a Publisher belongs after the Publisher is added, right-click on the Publisher and click Publisher Settings.


Publisher Detail Pane

When you select a Publisher in the Navigation Pane, the Detail Pane contains three tabs: Publications, Subscription Watch List, and Common Jobs (see Figure 12-21). Publications contains a list of publications at the Publisher. The list is displayed in a grid with columns that summarize current activity and status for that publication. Common Jobs contains a list of SQL Server Agent jobs that perform background maintenance for all replication components at the server instance. It is rare that you would ever need to work with them, but they are listed here just in case.

Figure 12-21. Subscription Watch List Tab.


The really interesting tab when a Publisher is selected is Subscription Watch List. On Subscription Watch List, you can see lists of subscriptions from all publications of one type at this Publisher. (Replication Monitor does not let you see subscriptions of multiple types at one time because the columns and statuses are different for different publication types.)

By default, Replication Monitor sorts the list by status and then by worst to best performance, so that the most worrisome subscriptions are at the top. For transactional subscriptions, the status order is Error, Warning, Retry, Uninitialized Subscription, Not Running, and Running. Subscriptions whose status is Not Running and Running are then sorted by increasing performance ratings. For merge subscriptions, the status order is Error, Warning, Uninitialized Subscription, Retry, Synchronizing, and Not Synchronizing. Subscriptions whose status is Synchronizing and Not Synchronizing are then sorted by increasing performance.

This sort order lets Replication Monitor put the subscriptions that are of greatest concern at the top of the list. Error status comes first because an error means replication has stopped. Warning and Retry statuses indicates that there is a potential problem that could eventually lead to an error. Uninitialized Subscription status notifies you that you have not finished setting up a subscription. For transactional subscriptions, Not Running status is next because these agents typically run continuously, so an agent that is not running may be a problem. For merge subscriptions the opposite is true: Synchronizing status is next because these agents run intermittently, and those that are currently running are the most interesting to see. Because the status and performance of replication agents change frequently, the order of the subscriptions is likely to change when Replication Monitor refreshes the list.

To see the details of a subscription in the list, double-click the subscription or right-click the row and select View Details.

Tip

You can sort the subscription watch list by any column in the grid by clicking the header cell of that column. Although sorting on status and performance meets most needs, you may want to sort on the Subscription column to focus on subscriptions at one Subscriber, or on the Publication column to focus on subscriptions to one publication.

You can also filter the watch list by selecting a criterion from the Show list. You can filter the list so that it includes only subscriptions with Errors, subscriptions with Warning or Retry status, subscriptions that are Running/Synchronizing or Not Running/Not Synchronizing, or the 25 or 50 worst performing subscriptions.


Transactional Publication Detail Pane

When you select a transactional publication in the Navigation Pane, the Detail Pane looks like Figure 12-22.

Figure 12-22. Transactional Publication detail pane, All Subscriptions tab.


The default content for the Detail Pane is to see all subscriptions to the publication. The list includes the following information:

  • Status The combined status of the Log Reader Agent for the publication and the Distribution Agent that synchronizes this subscription. The status reported is the "worst" status of the two agents. For example, if one agent's status is stopped by an error but the other is running normally, the status for the subscription is Error.

  • Subscription The Subscriber and database that contain the subscription.

  • Performance A rating of the synchronization performance, as measured by the latency. Performance can be Excellent, Good, Fair, Poor, or Critical. If performance drops to Critical, the status of the agent is changed to Warning: Critical Performance. Ratings are given only if you have defined a warning threshold for the publication on the Warnings and Agents tab.

  • Latency The time between when a command is committed at the Publisher and when it is committed at the Subscriber. This value measures the performance of both the Log Reader Agent and the Distribution Agent. This measurement is the time it took to move user data through the replication pipeline.

By default, Replication Monitor sorts the list by status and then worst to best performance, so that the most worrisome subscriptions are at the top, as on the Subscription Watch List tab when a Publisher is selected in the Navigation Pane. Because the status and performance of replication agents changes frequently, the order of the subscriptions is likely to change when Replication Monitor refreshes the list.

To see the details of a subscription in the list, double-click the subscription or right-click the row and select View Details.

Tip

You can sort the subscription list by any column in the grid by clicking the header cell of that column. You can also filter the list by selecting a criterion from the Show list.

If you right-click on a subscription row, you get a menu of commands for that subscription, including commands to see the subscription properties and to start or stop the Distribution Agent.


Although the All Subscriptions tab shows the performance of ongoing replication activity, the Tracer Tokens tab enables you to check connections and measure performance in real time by inserting a tracer token. On the Tracer Token tab (see Figure 12-23), click Insert Tracer to insert a small amount of data into the transaction log on the Publisher. SQL Server then monitors this data as the Log Reader Agent moves it from the Publisher to the distribution database on the Distributor and the Distribution Agents move it from the Distributor to the Subscribers. While the token is in transit, Replication Monitor shows "Pending" for any unmeasured times. When the token is delivered to the Subscriber, the Total Latency column reports the end-to-end latency for that subscription.

Figure 12-23. Transactional Publication detail pane, Tracer Tokens tab.


Tip

You can review the times of a past token by selecting the time the token was inserted from Time inserted.


The Warnings and Agents tab (see Figure 12-24) has two purposes: It enables you to define performance warning thresholds so that you can be alerted when a problem is building, and it shows agents for this publication that are common to all subscriptions. For a transactional publication, the common agents are the Log Reader Agent and the Snapshot Agent.

Figure 12-24. Transactional Publication detail pane, Warnings and Agents tab.


If you enable a warning and specify a threshold, SQL Server logs an event to the Windows Event Log if a subscription crosses the threshold. You can define an alert that responds to the event by using SQL Server Agent or any application that watches the event log, such as Microsoft Operations Manager.

Tip

If you make changes to the warnings on this page, you must click Save Changes. If you leave the tab without clicking the button, your changes are not saved.

If you want to use SQL Server Agent to define alerts, click Configure Alerts, specify the warning on which you want to be alerted, and Replication Monitor opens the Alert Properties dialog box. To define alerts in Microsoft Operations Manager or another application, configure alerts in that application.


In Agents and jobs related to this publication, you can see the status of the Snapshot Agent and Log Reader Agent. To see the details of a current or past agent session, double-click the agent or right-click its row and select View Details. You can also start or stop the agent from the context menu.

Tip

If the Snapshot Agent or Log Reader Agent has an error, Replication Monitor shows an error icon in the Warnings and Agents tab so that you know to click on this tab to get information about the error.


Transactional Subscription Detail Window

When you double-click on a transactional subscription in any subscription list, Replication Monitor opens a window to display details about the subscription (see Figure 12-25). The window contains three tabs: Publisher To Distributor History, which shows the activity of the Log Reader Agent; the Distributor To Subscriber History, which shows the activity of the Distribution Agent; and Undistributed Commands, which shows the number of commands that the Log Reader Agent has processed that the Distribution Agent has not yet applied to the Subscriber.

Figure 12-25. Transactional Subscription detail window.


Tip

The detail window for a transactional subscription does not automatically refresh by default. If you want to refresh the information in the window, click Refresh on the Action menu. To toggle automatic refreshes for this window, click Auto Refresh on the Action menu.


When an agent has stopped because of an error, the bottom of the tab displays the error message and other information. Log Reader Agent errors are displayed on Publisher To Distributor History, and Distribution Agent errors are displayed on Distributor To Subscriber History. There is often a series of error messages that come from different components involved in the operation that failed. If the error occurred when the agent was trying to execute a command, such as an INSERT statement, the command also is listed in Last message of the selected session (see Figure 12-26).

Figure 12-26. Error information in Transactional Subscription detail window.


Tip

Many error messages displayed in Replication Monitor have a help link with a URL of http://help/<error ID>. Although this isn't a URL that you can paste into your browser, when you click on it, Replication Monitor translates it into a complex URL that links to an online error database maintained by Microsoft.


Merge Publication Detail Pane

When you select a merge publication in the Navigation Pane, the detail pane looks like Figure 12-27.

Figure 12-27. Merge Publication detail pane, All Subscriptions tab.


The default content for the detail pane is to see all subscriptions to the publication. The list includes the following information:

  • Status The status of the Merge Agent that synchronizes this subscription.

  • Subscription The Subscriber and database that contains the subscription.

  • Friendly Name The name that helps identify the subscription. This name is especially helpful when the server and database names are not meaningful, such as "SVR54101." The friendly name is the Description property of the merge subscription, which you can set in the Subscription Properties dialog box.

  • Performance A rating of the synchronization performance, based on the delivery rate and merge duration. Performance can be Excellent, Good, Fair, Poor, or Critical. If performance drops to Critical, the status of the agent is changed to Warning: Critical Performance. Critical ratings are given only if you have defined a warning threshold for the publication on the Warnings and Agents tab.

  • Delivery Rate The number of rows per second that were processed during the last merge.

  • Last Synchronization The last time that the Merge Agent ran.

  • Duration The length of time taken for the last merge.

  • Connection The connection type used for the last merge, either Dialup or LAN

By default, Replication Monitor sorts the list by status and then by worst to best performance, so that the most worrisome subscriptions are at the top, as on the Subscription Watch List tab when a Publisher is selected in the Navigation Pane. Because the status and performance of replication agents changes frequently, the order of the subscriptions is likely to change when Replication Monitor refreshes the list.

To see the details of a subscription in the list, double-click the subscription or right-click the row and select View Details.

Tip

You can sort the subscription list by any column in the grid by clicking the header cell of that column. You can also filter the list by selecting a criterion from the Show list.

If you right-click on a subscription row, you get a menu of commands for that subscription, including commands to see the subscription properties, and to start or stop synchronizing.


The Warnings and Agents tab (see Figure 12-28) has two purposes: It enables you to define performance warning thresholds so that you can be alerted when a problem is building, and it shows the Snapshot Agent, the only agent for a merge publication that is common to all subscriptions.

Figure 12-28. Merge Publication detail pane, Warnings and Agents tab.


If you enable a warning and specify a threshold, SQL Server logs an event to the Windows Event Log if a subscription crosses the threshold. You can define an alert that responds to the event by using SQL Server Agent or any application that watches the event log, such as Microsoft Operations Manager.

Tip

If you make changes to the warnings on this page, you must click Save Changes. If you leave the tab without clicking the button, your changes are not saved.

If you want to use SQL Server Agent to define alerts, click Configure Alerts, specify the warning on which you want to be alerted, and Replication Monitor opens the Alert Properties dialog box. To define alerts in Microsoft Operations Manager or another application, configure alerts in that application.


In Agents and jobs related to this publication, you can see the status of the Snapshot Agent. To see the details of a current or past session of the agent, double-click the agent or right-click its row and select View Details. You can also start or stop the agent from the context menu.

Tip

If the Snapshot Agent has an error, Replication Monitor shows an error icon in the Warnings and Agents tab so that you know to click on this tab to get information about the error.


Merge Subscription Detail Window

When you double-click on a merge subscription in any subscription list, Replication Monitor opens a window to display details about the subscription.

When a merge subscription is synchronized, SQL Server records detailed information about the data changes that are processed for each article. All this information is displayed in the subscription detail window. A merge is also broken down into phases to further help you understand the activity that occurred during the merge. The phases are

  • Initialization The period in which the Merge Agent starts up and establishes connections to the Publisher and Subscriber.

  • Schema changes and bulk inserts The period in which the Merge Agent applies any changes to the schema of the published objects, such as adding a new column to a published table, and performs bulk insert actions such as applying a snapshot to initialize a new subscription.

  • Upload changes to Publisher The period in which changes made to published data at the Subscriber are applied to the data at the Publisher.

  • Download changes to Subscriber The period in which changes made to published data at the Publisher are applied to the data at the Subscriber.

The amount of time spent in each phase is detailed in Articles processed in the selected session (see Figure 12-29). This list further breaks down the upload and download phases to show how much time was spent processing the changes in each article and the types of changes that were made. This information can help you determine whether there are bottlenecks in your publication, such as inefficient row or join filters, that slow down the merge process.

Figure 12-29. Merge Subscription detail window.


When you open the detail window for a subscription that is currently being merged, you can watch these performance values change as they are being recorded. Note that the Merge Agent works on several threads at the same time, so the sum of the times spent per article may appear to be greater than the duration of the merge. When the merge completes, the time spent per article is apportioned according to the final duration of the merge. Replication Monitor also provides a progress bar at the bottom of the window and an estimate of much time it will take for the merge to complete.

Tip

The detail window for a merge subscription sometimes, but not always, automatically refreshes. If the subscription is being synchronized, the window automatically refreshes because the performance details are actively changing. If the subscription is not synchronizing, the window does not automatically refresh. To manually refresh the window, click Refresh on the Action menu. To toggle automatic refreshes for this window, click Auto Refresh on the Action menu.


When an agent has stopped because of an error, the bottom of the window displays error messages and other information. There is often a series of error messages that come from different components involved in the operation that failed. If the error occurred when the agent was trying to execute a command, such as an INSERT statement, the command also is listed in Last message of the selected session (see Figure 12-30).

Figure 12-30. Error information in Transactional Subscription detail window.


Tip

Many error messages displayed in Replication Monitor have a help link with a URL of http://help/<error ID>. Although this isn't a URL that you can paste into your browser, when you click on it, Replication Monitor translates it into a complex URL that links to an online error database maintained by Microsoft.





Inside SQL Server 2005 Tools
Inside SQL Server 2005 Tools
ISBN: 0321397967
EAN: 2147483647
Year: 2006
Pages: 149

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net