After you have implemented a replication solution, you need to monitor and administer replication. In this lesson, you will learn to use Replication Monitor to view the status of replication agents and their tasks. You will also learn to review and modify the properties of the Distributor, publications, subscriptions, and replication agents.
Replication Monitor shows up as a container in the SQL Server Enterprise Manager console tree for a SQL Server instance that is enabled as a Distributor when you are either a member of the sysadmin fixed server role, or a member of the replmonitor role in the distribution database. It displays a list of all Publishers using the Distributor, the status of all publications, and the status of all replication agents. It can be used to set up and monitor replication alerts. It can also be used to stop and start replication agents, and reinitialize subscriptions. Finally, you can also configure Replication Monitor as a top-level node in Enterprise Manager to monitor and administer multiple Distributors (by right-clicking Microsoft SQL Servers in the console tree and then clicking Show Replication Monitor Group).
To open Replication Monitor for a Distributor, connect to the Distributor (by expanding a SQL Server instance enabled as a Distributor) and expand the Replication Monitor container in the Enterprise Manager console tree. When you first expand Replication Monitor, a SQL Server Enterprise Manager dialog box will appear informing you that by default Replication Monitor is not automatically refreshed (to save Distributor resources). See Figure 15.73.
Configuring Replication Monitor refresh properties.
During testing, you might want to select the Yes, Automatically Refresh Replication Monitor By Polling The Distributor option button to have Replication Monitor automatically refresh. If you choose automatic refresh, you can click the Set Refresh Rate button to choose a refresh rate (the default is 10 seconds). However, during production, you should select the No, I Will Manually Refresh Replication Monitor option button to save Distributor resources.
After you open Replication Monitor, you can view the status of various replication components. See Figure 15.74. Notice that each Publisher authorized to use this Distributor is displayed. In the details pane, the status of the Snapshot Agent for the selected publication is displayed along with each replication agent for the publication. Only the last action is displayed in the details pane. To view the history of any replication agent for a publication, right-click the replication agent and then select Agent History as illustrated in Figure 15.75. This displays the Agent History dialog box.
Using Replication Monitor.
Displaying agent history for a publication.
You can also view replication information based on the type of agent rather than based on the publication. Expand the Agents container in Replication Monitor and select a type of replication agent. For example, click the Snapshot Agents container to view the status of all Snapshot Agents. See Figure 15.76.
Displaying the status of all Snapshot Agents.
In this figure, notice that each Snapshot Agent has successfully generated a snapshot. If a publication is not replicating successfully, check the status of the Snapshot Agent to verify that the initial snapshot has been created.
You can also configure Replication Alerts by selecting an alert within the Replication Alerts container. See Figure 15.77.
Configuring replication alerts.
SQL Server 2000 ships with eight preconfigured replication alerts. To use one of these alerts, you must enable the alert and configure operators to be notified. You do this by right-clicking an alert in the Replication Alerts container, clicking Properties, and then enabling the alert and configuring operators in the Replication Alert Properties dialog box.
In this practice you monitor publications and subscriptions using SQL Server Enterprise Manager.
To monitor replication
A SQL Server Enterprise Manager dialog box appears.
The Snapshot Agent History dialog box appears.
The Latest History Of Snapshot Agent dialog box appears. In the list box, notice the steps taken to create the snapshot files.
The Snapshot Agent Profiles dialog box appears.
The Replication Agent Profile Details dialog box appears. Notice the parameters for the Snapshot Agent.
In the details pane, notice the status of each replication agent. Only the Snapshot Agent has run because there are no replicated or queued transactions.
The Open Query File dialog box appears.
The script contains an UPDATE statement that updates the last name of the author, Johnson White, to Johnson Black.
To review and modify Distributor properties, right-click Replication Monitor in the console tree and then click Distributor Properties. Figure 15.78 shows the Publisher And Distributor Properties dialog box.
Viewing and modifying Distributor properties.
In the Distributor tab, you can configure properties of the Distributor, including the replication agent profiles for all new agents (more on agent profiles on the next page). You can also enable or disable Publishers in the Publishers tab, publication databases in the Publication Databases tab, and Subscribers in the Subscribers tab. To configure transaction and history retention periods, click the Properties button in the Distributor tab. Figure 15.79 shows the Distribution Database Properties dialog box.
Viewing and modifying retention periods.
Notice that the default maximum retention period for transactions is 72 hours and the default maximum retention period for replication performance history is 48 hours. These settings help determine the size of the distribution database. For example, if a Subscriber to a transactional publication fails to retrieve replicated transactions on schedule, they will remain in the distribution database for 72 hours by default. The distribution database must have sufficient hard disk space to store this volume of replicated transactions. After the expiration of this maximum retention period, a Subscriber will have to reinitialize the subscription. This requires a current snapshot and will cause a performance hit.
To view and modify the properties of a publication, you can select the publication either in Replication Monitor or from the Publications container for the published database. Right-click the selected publication and then click Properties. If the publication has existing subscriptions, you will receive a warning stating that many properties cannot be modified. The SQL Server Enterprise Manager message box is shownin Figure 15.80. Although you can modify some properties, you will need to drop all subscriptions to modify many properties of a subscription. Click OK to close the SQL Server Enterprise Manager message box.
The Publication Properties dialog box appears, with the General tab selected. Each publication has many properties that can be viewed and modified. See Figure 15.81.
Modifying a publication with existing subscriptions notice.
Reviewing and modifying publication properties.
In the General tab, notice that the default subscription expiration period for a subscription to a merge publication is 14 days. For some environments, you might want to configure subscriptions to never expire.
Each replication agent has a profile that defines the parameters used by the agent to perform its task. This includes values such as timeout values and Bcp batch size parameters. A set of default replication agent profiles is defined for each type of replication agent when a Distributor is configured and can be modified as a property of the Distributor. To modify the profile for an existing replication agent, in the Agents container, right-click the replication agent and then click Agent Profiles. The Distribution Agent Profiles dialog box appears, shown in Figure 15.82.
Reviewing and modifying replication agent profile details.
The agent profile for a Distribution Agent is displayed in Figure 15.82. Notice that the default agent profile is being used, and that three other profiles are available for this agent. One profile specifies that the agent will continue despite data consistency errors. By default, an agent will cease data synchronization if data consistency errors are detected. In some environments you might want to override this default. You can also configure the agent profile to perform verbose history logging for troubleshooting purposes or configure the agent profile for use with Windows Synchronization Manager using a smaller batch size. To view the details of a profile, click the View Details button. The Replication Agent Profile Details dialog box appears, shown in Figure 15.83.
Reviewing and modifying agent profile details.
Notice the types of parameters that can be set or modified. Each replication agent has different default settings and different available alternate profiles. You can also create a new profile for an agent.
To review or modify subscription properties of a publication, right-click the publication in the Publications container, click Properties, and then click the Subscriptions tab in the Publication Properties dialog box. Each current subscription of the publication is displayed. See Figure 15.84.
To view the properties of a subscription, click the subscription in the Subscription container, and then click Properties. The Subscription Properties dialog box appears, as shown in Figure 15.85.
Click the Synchronization tab to view or modify the location at which the agent will run. On Windows 2000 and Windows NT 4.0 Subscribers, you can have the agent run at the Subscriber to reduce the load on the Distributor. See Figure 15.86.
You can review the status of a subscription from the Subscriber side by expanding the Replication folder in the console tree of Enterprise Manager and then clicking Subscriptions. See Figure 15.87.
Reviewing general properties of a subscription.
Specifying the replication agent location.
Viewing subscription status on a Subscriber.
Each subscription is displayed in the details pane along with the current status of the subscription.
To view and resolve a merge replication conflict at the Distributor, right-click the merge publication in the Replication container of Enterprise Manager and then click View Conflicts. The Microsoft Replication Conflict Viewer dialog box appears, shown in Figure 15.88.
Viewing replication conflicts.
If any tables have conflicts, they will be displayed. Click the View button to resolve any conflicts. See Figure 15.89.
Microsoft Replication Conflict Viewer.
Notice that you can resolve the conflict by selecting the winner as selected by SQL Server, you can select the loser, or you can edit the conflicting information. You can also choose to postpone resolution or log the details for later reference.
To use Windows Synchronization Manager on a Subscriber, click Start, point to Programs, point to Accessories, and then click Synchronize. The Items To Synchronize dialog box appears, illustrated in Figure 15.90.
Windows Synchronization Manager.
Notice that various items can be synchronized using Windows Synchronization Manager. Select a subscription to a publication, and then click the Synchronize button to immediately synchronize each item containing a check mark in its check box. If interactive conflict resolution is enabled for a publication, you will be prompted to resolve any conflicts that occur during synchronization. Click the Setup button to display the Synchronization Settings dialog box and to configure synchronization settings for each item. Items can be synchronized on logon and logoff, when the processor is idle, or according to a schedule. See Figure 15.91.
Select a subscription to a publication, and then click the Properties button to configure properties of the subscription. The SQL Server Subscription Properties page appears with the General tab selected (see Figure 15.92). You can synchronize with the current default Publisher or select a different Publisher with which to synchronize. This is useful if your default Publisher is temporarily offline or if a network link is temporarily down. You can also select this other Publisher as the new default Publisher by clicking the Make This Publisher The Default Synchronization Partner check box. You also reinitialize or remove the subscription by clicking either the Reinitialize Subscription button or the Remove Subscription button.
Click the Subscriber tab to modify the login account that a given push subscription uses to connect to this Subscriber (see Figure 15.93). Click the Publisher tab to modify the login account that a given pull subscription uses to connect to the Publisher (see Figure 15.94). Click the Distributor tab to modify the login account that a given pull subscription uses to connect to the Distributor (see Figure 15.95). You can either select the current login account for the user activating Synchronization Manager, or you can specify a SQL Server login account (see Figure 15.95).
Specifying a default Publisher in the General tab of the Subscription properties page.
Subscriber login properties.
Publisher login properties.
Distributor login properties.
Click the Other tab to enable automatic or interactive conflict resolution. You can also configure login and query timeout settings. This can be useful for slow links. The defaults are 15 seconds for logins and 30 seconds for queries (see Figure 15.96).
Configuring automatic or interactive conflict resolution.
In this practice you resolve conflicts interactively using Windows Synchronization Manager.
To resolve conflicts interactively using Windows Synchronization Manager
The Open Query File dialog box appears.
This script updates Employee ID 1, changing the last name and title.
The Connect To SQL Server dialog box appears.
The Select Server dialog box appears.
The Open Query File dialog box appears.
This script updates Employee ID 1, changing the last name and title. Notice that the last name and title for this employee are different from the script in step 3.
The SQL Server Subscription Properties dialog box appears.
A Microsoft SQL Server 2000 dialog box appears asking if you are sure you want to change the conflict resolution mode.
The Synchronizing dialog box appears. After a few moments, the Microsoft Replication Conflict Viewer for 'Northwind' - '[dbo].[Employees]' dialog box appears. Notice that you can use pull-down menus to view the data at the Publisher and at the Subscriber.
The Synchronization process completes.
Notice that the resolved data has been replicated to MyNamedInstance.
Use Replication Monitor to monitor all aspects of replication. You can monitor using a publication focus or an agent focus. From within Replication Monitor, you can view the status of any publication or agent. You can start or stop an agent, or view its history. You can view the details of a publication. Although you can modify some details of a publication that has existing publications, to modify all details you must drop all existing subscriptions. You can also review and resolve replication conflicts for a merge publication using Microsoft Replication Conflict Viewer. Finally, you can use Windows Synchronization Manager to interactively control synchronization on a Subscriber, if that publication option is enabled.