Managing Replication

3 4

You now know how to create and configure a replicated database in a SQL Server 2000 environment. To manage this replicated environment or troubleshoot if the replication has not started, you'll use the monitoring capabilities and configuration options in Enterprise Manager.

Monitoring and Managing Replication Agents

The replication agents can be found in the Replication Monitor folder in Enterprise Manager. To access the agents, follow these steps:

  1. Expand a server group, expand a server, and then expand the Replication Monitor folder.
  2. If the server you expanded is a publisher, Publishers and Agents folders will appear under the Replication Monitor folder. The Publishers folder contains the publishers that belong to this server. The Agents folder contains folders for the Snapshot Agents, the Log Reader Agents, the Distribution Agents, the Merge Agents, and miscellaneous agents that are used for cleanup and historical logging.
  3. Although agents do not normally need to be started or stopped, you can use Replication Monitor to do so. If your replicated system does not seem to be working after you have configured it, chances are that the Snapshot Agent has not been started, probably because the agent is using the default schedule. (This is why during the configuration process you have the option of performing the initial snapshot immediately.) Check the status of the agents by clicking an agent folder in Enterprise Manager and viewing information about the agents in the right-hand pane. An example of this is shown in Figure 28-24. Here you can determine whether the agent has been run, and you can start it if necessary. Once you have started the agent, it will run until it has completed its job, and then it will become inactive. SQL Server Agent will then start the replication agent on its regular schedule.

    click to view at full size.

    Figure 28-24. A Merge Agent shown in Enterprise Manager.

  4. Right-click the agents to display a shortcut menu containing a number of options you can use to monitor and manage the agents. The menu that appears when you right-click a Merge Agent is shown in Figure 28-25.

    The options available for a Merge Agent are described here:

    • Error Details Lists the details of any errors that have occurred.
    • Agent History Lists the agent's activities.
    • Agent Properties Lets you modify the replication agent's schedule. You can also modify the database access method, the agent's tasks, and notifications. For example, you can choose to receive e-mail messages notifying you of agent events.
    • Agent Profiles Lets you view and modify agent parameters such as login time-outs, batch size, and query time-outs.
    • Run Agent At Subscriber Lets you specify to run the agent on the subscriber.
    • Run Agent At Distributor Lets you specify to run the agent on the distributor.
    • Start Agent and Stop Agent Let you start the agent if it is stopped or stop the agent if it is started.
    • Refresh Rate And Settings Lets you modify how often Performance Monitor data is refreshed.
    • Select Columns Lets you specify which columns are viewed in the results pane.
    • Show Anonymous Subscriptions Specifies whether anonymous subscriptions are shown in this window.
    • Help Provides help information for this window.

    click to view at full size.

    Figure 28-25. The Merge Agent options.

Configuring the Merge Agent

Once you have created a publication, you might want to modify the behavior of the Merge Agent. For example, you can specify how the Merge Agent is invoked by selecting which mode it runs in. In continuous mode, the Merge Agent is started when SQL Server Agent is started. In scheduled mode, which is the preferred mode, the Merge Agent starts according to a schedule and becomes inactive whenever all of the replicated transactions have been delivered. To configure the Merge Agent, follow these steps:

  1. In Enterprise Manager, expand a server, expand the Replication Monitor folder, expand the Agents folder, and then click the Merge Agents folder.
  2. In the right-hand pane of Enterprise Manager, right-click the publication and choose Agent Properties from the shortcut menu that appears.
  3. The Merge Agent's Properties window appears, as shown in Figure 28-26.

    click to view at full size.

    Figure 28-26. The General tab of a Merge Agent's Properties window.

  4. Click the Steps tab, shown in Figure 28-27. On this tab, you will see the steps that the Merge Agent performs whenever it is invoked. The three steps are listed and described here:

    • Merge Agent Startup Message Logs a message into the Log Reader Agent history table (the MSLogreader_history table in the distribution database).
    • Run Agent Starts the agent according to the specified schedule. When running in continuous mode, the agent will run until the system is shut down.
    • Detect Nonlogged Agent Shutdown Puts a message into the Log Reader Agent history table in the event of an agent failure.

    click to view at full size.

    Figure 28-27. The Steps tab of a Merge Agent's Properties window.

  5. Select the Run Agent step, and click Edit to display the Edit Job Step dialog box, shown in Figure 28-28. This dialog box allows you to configure how the Merge Agent is invoked.

    click to view at full size.

    Figure 28-28. The General tab of the Edit Job Step dialog box.

    Many options can be configured for the Merge Agent. The default Merge Agent parameters can be modified in the Command box of the Edit Job Step dialog box and in the Replication Agent Profile Details dialog box (described later in this section). Two of the parameters you can modify in the Edit Job Step dialog box are described here:

    • Continuous Specifies whether the Merge Agent runs in continuous mode or in scheduled mode. To specify scheduled mode, remove this parameter.
    • DistributorSecurityMode Specifies whether the Merge Agent uses SQL Server or Windows NT authentication mode.

    In addition, you can specify other options in the Edit Job Step dialog box, such as LoginTimeout, PollingInterval, QueryTimeout, distributor and publisher information, and Output.

    MORE INFO


    An explanation of these options can be found in SQL Server Books Online. Look up "Merge Agent, starting" in the Books Online index.

  6. Once you have finished modifying the Merge Agent's properties, click OK to save your changes.

You can modify additional options via the Merge Agent's profile. To modify the profile, follow these steps:

  1. In the right-hand pane of Enterprise Manager, right-click the Merge Agent, as described earlier in this section, and choose Agent Profiles from the shortcut menu that appears. This will invoke the Merge Agent Profiles dialog box, as shown in Figure 28-29.

    click to view at full size.

    Figure 28-29. The Merge Agent Profiles dialog box.

    Notice that this dialog box contains many more options than does the Log Reader Agent Profiles dialog box you saw in Chapter 27. These profiles provide a range of functionality so that you can choose a profile that best suits your system's specific configuration.

  2. Click New Profile to create a new profile. The current profile cannot be modified. This will invoke the Replication Agent Profile Details dialog box as shown in Figure 28-30.

    Figure 28-30. The Replication Agent Profile Details dialog box.

  3. In this dialog box, you can modify the following parameters:

    • BcpBatchSize Specifies the number of rows to be sent in a bulk copy operation. This is used mainly for logging.
    • ChangesPerHistory Specifies the threshold above which uploading and downloading messages are logged.
    • DownloadGenerationsPerBatch Specifies the number of generations to be downloaded in a batch.
    • DownloadReadChangesPerBatch Specifies the number of changes to be read in a batch.
    • DownloadWriteChangesPerBatch Specifies the number of changes to be applied in a batch.
    • FastRowCount Specifies the type of validation to be used. The value 1 specifies a fast method, and the value 0 indicates a rowcount method.
    • HistoryVerboseLevel Specifies how much information is logged.
    • KeepAliveMessageInterval Specifies the number of seconds between heartbeat checking, a technique performed by members of the replication set to determine if other members are functioning.
    • LoginTimeout Specifies the number of seconds that the Merge Agent will wait before timing out.
    • MaxDownloadChanges Specifies the maximum number of downloads in a single session.
    • MaxUploadChanges Specifies the maximum number of uploads in a single session.
    • MaxDeadlockRetries Specifies the number of times that the agent will retry if a deadlock occurs.
    • PollingInterval Specifies how often the transaction log on the publisher is polled for new transactions.
    • QueryTimeout Specifies how many seconds a query waits before timing out.
    • UploadGenerationsPerBatch Specifies the number of generations of uploads to be processed in a single batch.
    • UploadReadChangesPerBatch Specifies the number of reads to be processed in a single batch.
    • UploadWriteChangesPerBatch Specifies the number of writes to be processed in a single batch.
    • Validate Specifies whether validation should be done at the end of the session.
    • ValidateInterval Specifies how often validation should be done, if at all, when the agent is running in continuous mode.

If the Merge Agent is running in scheduled mode rather than continuous mode, it will be invoked by SQL Server Agent, and it will process the number of changes specified by MaxUploadChanges and MaxDownloadChanges before terminating.

Disabling Replication

You can easily disable all or some replication operations on your system by using Enterprise Manager's replication wizards to remove certain replication components. In this section, you'll learn how to accomplish this simple task.

Removing Push Subscriptions

To remove a push subscription, use Enterprise Manager on the distributor system to invoke the Push Subscription Wizard. The Create And Manage Publications dialog box appears after you invoke this wizard. In the Databases And Publications list box, select the subscription you want to delete, and then click Delete Publication. You will be prompted to verify that you want to delete the subscription. Click Yes to drop the subscription.

Removing Pull Subscriptions

To remove a pull subscription, use Enterprise Manager on the subscriber system to invoke the Pull Subscription Wizard. The Create And Manage Publications dialog box appears after you invoke this wizard. In the Databases And Publications list box, select the subscription you want to delete, and then click Delete Publication. You will be prompted to verify that you want to delete the subscription.

Removing Distribution and Publications

To drop distribution and publications, you must invoke the Disable Publishing And Distribution Wizard. In the first screen of this wizard, you specify whether you want to disable all distribution and publications or to remove just publications. If you choose the first option, all publishing, distribution, and publications will be removed. If you accept the second option (the default), only the publications will be removed. You then select the publications you want to disable. After you have made your selection, you will be presented with a verification screen, giving you one last chance to change your mind. Click Yes to remove the replication components you selected in this wizard.



Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

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