Nineth article Replication: Replication Monitor

This article is the nineth of the SQL Server replication series, please refer to the original text for more information.

Replication Monitor allows you to view the health status of the replication configuration component. This assumes that you are following the first eight articles, and that you already have a merge publication and a transactional publication.
start the Replication Monitor
Replication Monitor is not part of SSMs, it is a standalone executable (SqlMonitor.exe). Replication Monitor is not found under the Start menu in a standard SQL Server installation. The simplest way to start Replication Monitor is to connect to the Publisher under SSMs Object Explorer, and right-click your publication to select Start Replication Monitor, as shown in 9.1:

Figure 9.1 Starting Replication Monitor
The Replication Monitor is shown in the main screen 9.2. The screen is divided into two parts, showing the list of publishers on the left and all the publishers you are connected to. If you choose a Publisher, on the right you can see more information about its release.

Figure 9.2 Replication Monitor Home screen
To connect to a publishing server
If you start Replication Monitor through the release of SSMs, the publisher for that publication is automatically added to the list of publishers. If you want to connect to other publishers, you can right-click any folder in the Publisher list, and in the popup menu select "Add Publisher ...", as shown in 9.3:

Figure 9.3 Adding a Publisher
The open page allows you to connect to one or more publishers. First you need to click the "Add" button to choose to add a separate publisher or specify a Distributor and add its publisher. The drop-down menu 9.4 shows:

Figure 9.4 Adding a SQL Server Publisher
After you choose, a Standard Database Connection dialog box appears. If you choose to add a Publisher directly and the Distributor for the publisher is not the same instance, you will see the dialog box shown in Figure 9.5:

Figure 9.5 Publisher and Distributor are not the same instance
Then you will see a Standard Database Connection dialog box again, this time to provide the credentials of the Distributor. You can repeatedly select a Publisher or Distributor to add any Publisher to the list you need.
The publisher that you add appears in the top half of the Add Publisher dialog box. You can select Automatic connection and automatic refresh for each publisher when Replication Monitor starts. The Publisher selected in the dialog box is divided into the same group (switch to the Publisher group view). In the lower part of the Add Publisher dialog box, you can specify which group the new publisher is added to. 9.6 is shown below:

Figure 9.6 Publishing Server properties
This group creates a single level of folder structure, so you cannot create another group under one group.
You can only connect to each publisher once. means that each publisher can be in only one group. If you try to connect to a publisher a second time, Replication Monitor prompts the publisher to be monitored by Replication Monitor.
Publish Server Details
If you select a Publisher in the list on the left, the right panel displays more information about it. The right panel displays information in Publisher mode, which has three tabs.
The first tab is "Publish", as shown in 9.7:

Figure 9.7 Release
Here you can see all the defined publications, their status, and the number of subscriptions. The rightmost three column contains how many subscriptions are synchronizing and the performance of the current synchronization. This performance measurement is based on the performance of all subscriptions. It can be "very good", "good", "Fair", "bad", "serious". The performance of a transactional subscription, measured only after the Warning tab has a threshold value enabled. The performance of the merge publication requires at least five synchronizations before it is displayed. For more details, refer to books Online.
The second tab of the Publisher mode is the subscription watch list, which is shown in 9.8:

Figure 9.8 Subscription Watch list
The subscription watch list provides a holistic view of the subscriptions that correspond to all publications. At the top left drop-down list you can choose to display a transactional subscription, display a merge subscription, or display a snapshot subscription. The list of columns for each type is different. More details will be mentioned in the following sections.
The right top dropdown list allows you to filter the issue subscriptions. All available option 9.9 is as follows:

Figure 9.9 Subscription Filter Items
If you select any item, only subscriptions that meet the criteria will be displayed in the list. If you are dealing with many subscriptions, this feature will help a lot.
The Third tab of the Publisher mode is "proxy", as shown in 9.10:

Figure 9.10 Replication Agent Information
In the Proxy Type drop-down list you can choose which type of proxy to show. The available types are shown in Listing 9.11:

Figure 9.11 Proxy Type
The Replication Monitor displays the most information here for the Log Reader Agent, so check here first if you think there is a problem with the log reader. The information displayed includes the current status, the last startup time, and the previous action. For non-maintenance job types, it also includes the most recently run performance information, as well as the number of transactions and commands.
Transactional Publishing
If you click on a transactional publication (myfirstpublication) in the left-hand publishing list, the transactional subscription mode is displayed on the right. It has four tabs. The first tab is "all subscriptions." It shows all subscriptions for the selected publication. You can view the status and partial performance information for each subscription. Figure 9.12 shows a list of subscriptions

Figure 9.12 Subscription for the selected publication
The Status column shows that the Distribution Agent for this subscription is running, not running, or retrying. It can also contain warning messages such as "expiring soon". The performance column gives a rough speed to the Subscriber. Latency displays the time that is required to change from the publisher to the Subscriber.
The second tab of a transactional subscription pattern is a tracer token. 9.13 is shown below:

Figure 9.13 Tracer Tokens
A tracer token is a special token that is written to the log file in the publication database. Tracking tokens do not affect real data changes in replicated tables in the publication or subscription database. However, the replication agent treats this token as a normal transaction. This means that it will move in accordance with the standard copy steps. This allows SQL Server to measure the latency between the publisher and the Distributor, as well as between the distributor and the Subscriber. You can insert a tracer token by clicking the "Insert Tracker" button. The list displays a row for each subscription, listing the latency of the Publisher to Distributor and Distributor to Subscriber. The sum of the two is also displayed in the Total latency column. The previous trace information can be viewed through the Insert time drop-down list.
The Proxy tab of the transactional subscription pattern shows the Snapshot Agent and Log Reader Agent. 9.14 is shown below:

Figure 9.14 Transactional replication agent
This information includes the SQL Agent job status, the last Startup time, the duration, and the previous action. The Distribution Agent is not included in this tab.
The last tab of the transactional subscription pattern is "warning". 9.15 is shown below:

Figure 9.15 Transactional Replication warning
Warning the Status column of the All Subscriptions tab and the left Publisher list all levels appear as a yellow triangle. Figure 9.16 is an example of this:

Figure 9.16 Warning Subscription
You can set a two warning threshold for transactional publications. The first warning threshold is an out-of-date warning. If the subscription exceeds the percentage specified in the maximum distribution retention period (fourth), a warning is displayed for this subscription. Second warning you will receive a warning when the latency exceeds the specified time limit. You can select seconds, minutes, or hours, and you can type any number. Two warnings can be disabled by removing the enabled check box.
Merge Publication
If you click on a merge publication (myfirstmergepublication) in the Publish list, the merge subscription mode will appear on the right. 9.17 is shown below:

Figure 9.17 Merge Subscriptions
The merge subscription pattern has three tabs. They are all subscriptions, proxies, warnings, respectively. There is no tracer token, because merge replication is not using transaction logs. The All Subscriptions tab contains all subscriptions for the selected merge publication (one subscription per row). You can view the status, connection type, last synchronization, duration, performance, and transfer rate for each subscription. The Status column can contain these values: Error,performance critical,long-running merge,expiring soon/expired,uninitialized subscription,Retrying Failed command,synchronizing and not Synchronizing. The performance column contains four values: Good, good, Fair, poor.
The second tab of the merge subscription pattern is "proxy." For merge replication It contains only the Snapshot Agent. Display information and transactional replication (9.14) are the same.
The last page sign is "warning", 9.18 shows:

Figure 9.18 Merge Replication warning
There are three different types of warnings for you to choose to receive. One subscription expires, two performance-related. For performance warnings You can specify different thresholds depending on whether the subscriber is over a dial-up connection or a LAN connection (which is why figure 9.18 has five elements). The warning display for merge subscriptions is the same as for transactional subscriptions. The transaction subscription warning example is shown in Figure 9.16.
Snapshot Publishing
Snapshot replication is not discussed in this series. However, you can also use Replication Monitor to get information about snapshot publications and subscriptions. If you select a snapshot publication (Mysnapshot) on the left, the snapshot copy mode is displayed on the right. 9.19 is shown below:

Figure 9.19 Snapshot publication
Three tabs display the same information as the merge publication. However, due to the simplification of the snapshot replication structure, the amount of information available is significantly reduced. There is no performance information on the All Subscriptions tab, and the warning page has only warnings for subscription expiration.
subscription information
There are several places in this article that show a list of subscriptions. In the list of subscriptions, you can get more information for each subscription by using the context menu. Figure 9.20 shows the context menu for a transactional subscription:

Figure 9.20 Subscription context Menu
From this can mark a subscription to being reinitialized by using a existing or a new snapshot. You can view and modify subscription properties and proxy configuration files. Open a new dialog box by tapping "View Details":

Figure 9.21 Transaction Subscription details
The Details dialog box for a transactional subscription has three tabs, as shown in 9.21. The previous two tabs contain the last synchronization information. The first shows the publisher-to-distributor history, and the second shows the distributor-to-subscriber history. The Third tab (Figure 9.22) contains the number of non-distributed commands and the estimated time required to distribute the commands.

Figure 9.22 Non-distributed commands
For merge subscriptions, the Subscription Details dialog box contains only a tab for the synchronization history, as shown in 9.23:

Figure 9.23 Merge Subscription details
By selecting a session for the Merge Agent, you can see the items processed by the selected session in the lower section.
For a snapshot subscription, the subscription details also contain only one page sign. It is the same as the "Distributor to Subscriber History" tab in a transactional subscription.
Alerts
Use the Warnings tab to enable a warning for any publication that causes a yellow triangle to appear if any of the published subscriptions exceed the set threshold. No additional action is performed by default. If you want SQL Server to notify you of such a problem, you can set up a SQL Server alert. You can use several predefined alerts. You can enable these by using SSMS alone, which is usually easier with the Configure Alerts button on the Warnings tab. The warning page of the transactional publication is shown in Figure 9.15, and clicking Configure Alerts Opens the dialog box in Figure 9.24:

Figure 9.24 Configuring Replication Alerts
Select any alert click Configure to open the Standard Alert dialog box, and the required information for the selected alert is automatically populated. You can use this page to specify how SQL Server responds and who receives notifications. For more information about how to set up alerts, refer to SQL Server Agent alerts and operators.
Summary
In this article we introduce Replication Monitor. The Replication Monitor provides a vast amount of information on your entire replication environment. The different dialogs and forms used to convey this information were explained, followed by a brief introduction to SQL Se RVer Alerts.

Nineth article Replication: Replication Monitor