Lesson 4: Monitoring and Administering Replication

3 4

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.


After this lesson, you will be able to

  • Monitor replication tasks with Replication Monitor
  • Configure replication alerts
  • Review and modify Distributor properties
  • Review and modify publication properties
  • Review and modify replication agent properties
  • Review and modify subscription properties

Estimated lesson time: 30 minutes


Monitoring with Replication Monitor

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.

 figure 15.73 - configuring replication monitor refresh properties.

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.

Publications

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.

 figure 15.74 - using replication monitor.

Figure 15.74

Using Replication Monitor.

 figure 15.75 - displaying agent history for a publication.

Figure 15.75

Displaying agent history for a publication.

Agents

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.

 figure 15.76 - displaying the status of all snapshot agents.

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.

Alerts

You can also configure Replication Alerts by selecting an alert within the Replication Alerts container. See Figure 15.77.

 figure 15.77 - configuring replication alerts.

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.

Practice: Monitoring Replication

In this practice you monitor publications and subscriptions using SQL Server Enterprise Manager.

To monitor replication

  1. In the SQL Server Enterprise Manager console tree, expand the Replication Monitor container.

    A SQL Server Enterprise Manager dialog box appears.

  2. Click the Yes, Automatically Refresh Replication Monitor By Polling The Distributor option button and then click OK.
  3. Expand the Agents container and then click the Snapshot Agents container.
  4. In the details pane, verify that a snapshot was created for each publication by viewing the Status column.
  5. In the details pane, right-click NorthwindSales and then click Agent History.

    The Snapshot Agent History dialog box appears.

  6. Click the Session Details button.

    The Latest History Of Snapshot Agent dialog box appears. In the list box, notice the steps taken to create the snapshot files.

  7. Click the Close button.
  8. In the Snapshot Agent History dialog box, click the Agent Profile button.

    The Snapshot Agent Profiles dialog box appears.

  9. Click the View Details button.

    The Replication Agent Profile Details dialog box appears. Notice the parameters for the Snapshot Agent.

  10. Click the Close button.
  11. In the Snapshot Agent Profiles dialog box, click OK.
  12. In the console tree, in the Replication Monitor container, expand Publishers, expand SelfPacedCPU, and then click PubSales: pubs.

    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.

  13. On the Tools menu, click SQL Query Analyzer.
  14. On the toolbar, click the Load SQL Script button.

    The Open Query File dialog box appears.

  15. Open PubsUpdate.sql in the C:\SelfPacedSQL\CH_15 folder.

    The script contains an UPDATE statement that updates the last name of the author, Johnson White, to Johnson Black.

  16. On the toolbar, click the Execute Query button.
  17. Switch to SQL Server Enterprise Manager.
  18. In the details pane, observe the Log Reader Agent and the Distribution Agent (named SelfPacedCPU\MyNamedInstance:PubsPush). After about 10 seconds, each of these agents will run and update the PubsPush database on SelfPacedCPU\MyNamedInstance. Each agent will indicate that one transaction with two commands was delivered. After about 10 more seconds, these agents will indicate that no replicated transactions are currently available.
  19. Do not close SQL Server Enterprise Manager or SQL Query Analyzer.

Reviewing and Modifying Distributor Properties

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.

 figure 15.78 - viewing and modifying distributor properties.

Figure 15.78

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.

 figure 15.79 - viewing and modifying retention periods.

Figure 15.79

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.

Reviewing and Modifying Publication Properties

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.

 figure 15.80 - modifying a publication with existing subscriptions notice.

Figure 15.80

Modifying a publication with existing subscriptions notice.

 figure 15.81 - reviewing and modifying publication properties.

Figure 15.81

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.

Reviewing and Modifying Replication Agent Profile

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.

 figure 15.83 - reviewing and modifying agent profile details.

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.

 figure 15.83 - reviewing and modifying agent profile details.

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.

Reviewing and Modifying Subscription Properties

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.

 figure 15.84 - reviewing subscriptions.

Figure 15.84

Reviewing subscriptions.

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.

 figure 15.85 - reviewing general properties of a subscription.

Figure 15.85

Reviewing general properties of a subscription.

 figure 15.86 - specifying the replication agent location.

Figure 15.86

Specifying the replication agent location.

 figure 15.87 - viewing subscription status on a subscriber.

Figure 15.87

Viewing subscription status on a Subscriber.

Each subscription is displayed in the details pane along with the current status of the subscription.

Viewing and Resolving Merge Replication Conflicts

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.

 figure 15.88 - viewing replication conflicts.

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.

 figure 15.89 - microsoft replication conflict viewer.

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.

Using Windows Synchronization Manager

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.

 figure 15.90 - windows synchronization manager.

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.

 figure 15.91 - synchronization settings.

Figure 15.91

Synchronization settings.

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

 figure 15.92 - specifying a default publisher in the general tab of the subscription properties page.

Figure 15.92

Specifying a default Publisher in the General tab of the Subscription properties page.

 figure 15.93 - subscriber login properties.

Figure 15.93

Subscriber login properties.

 figure 15.94 - publisher login properties.

Figure 15.94

Publisher login properties.

 figure 15.95 - distributor login properties.

Figure 15.95

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

 figure 15.96 - configuring automatic or interactive conflict resolution.

Figure 15.96

Configuring automatic or interactive conflict resolution.

Practice: Resolving Conflicts Interactively Using Windows Synchronization Manager

In this practice you resolve conflicts interactively using Windows Synchronization Manager.

To resolve conflicts interactively using Windows Synchronization Manager

  1. Switch to SQL Query Analyzer.
  2. On the toolbar, click the Load SQL Script button.

    The Open Query File dialog box appears.

  3. Open NorthwindUpdate.sql in the C:\SelfPacedSQL\CH_15 folder.

    This script updates Employee ID 1, changing the last name and title.

  4. On the toolbar, click the Execute Query button.
  5. On the File menu, click Connect.

    The Connect To SQL Server dialog box appears.

  6. Click the SQL Server ellipsis button to select an instance of SQL Server.

    The Select Server dialog box appears.

  7. Click SelfPacedCPU\MyNamedInstance and then click OK.
  8. Verify that the Windows Authentication option button is selected, and then click OK.
  9. On the toolbar, click the Load SQL Script button.

    The Open Query File dialog box appears.

  10. Open NwindPullUpdate.sql in the C:\SelfPacedSQL\CH_15 folder.

    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.

  11. On the toolbar, click the Execute Query button.
  12. To synchronize using Windows Synchronization Manager, click Start, point to Programs, point to Accessories, and then click Synchronize.
  13. Click NwindPull: NorthwindSales and then click the Properties button.

    The SQL Server Subscription Properties dialog box appears.

  14. Click the Other tab.
  15. Click the Resolve Conflicts Interactively (Only Applies To Articles That Support Interactive Resolution) option button and then click OK.

    A Microsoft SQL Server 2000 dialog box appears asking if you are sure you want to change the conflict resolution mode.

  16. Click the Yes button.
  17. Clear the My Current Home Page check box, verify that only the NwindPull: NorthwindSales check box is selected, and then click the Synchronize button.

    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.

  18. Click the Resolve With This Data option button.

    The Synchronization process completes.

  19. In SQL Query Analyzer, in the connection to MyNamedInstance, click the Clear Window button on the toolbar.
  20. In the query pane, type SELECT * FROM Employees WHERE EmployeeID = 1. and then click the Execute Query button on the toolbar.

    Notice that the resolved data has been replicated to MyNamedInstance.

  21. Close SQL Server Enterprise Manager and SQL Query Analyzer.

Lesson Summary

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.



MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228
ISBN: N/A
EAN: N/A
Year: 2001
Pages: 126

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