HOW TO: Set Up Alternate Synchronization Partner in SQL Server 2000 Merge Replication (321176)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 2000 64 bit (all editions)
This article was previously published under Q321176

IN THIS TASK

SUMMARY

With the SQL Server 2000 merge replication feature, alternate synchronization partners, merge replication subscribers on SQL Server 2000 servers can synchronize with servers other than the publisher on which the subscription originated. Synchronizing with alternate partners provides a subscriber the ability to synchronize data even if the primary publisher is not available.

This step-by-step article, describes how to set up and to use the alternate synchronization partner feature.

This article is based on the assumption that you have some knowledge of SQL Server Replications. For clarification about specific features, see the "Replication" topic in SQL Server Books Online. To download an updated version of SQL Server 2000 Books Online, visit the following Microsoft Web site:

SQL Server Books Online (Updated)

The server names that are used as examples in this article are:
  • PublisherA: Primary Publisher and Distributor
  • PublisherB: Alternate Synchronization Partner and Distributor
  • SubscriberA: Merge Subscriber to the PublisherA
To facilitate setting up replication, have each computer that is running SQL Server register the other computers that are running SQL Server in SQL Server Enterprise Manager by using either Microsoft Windows Integrated security or SQL Server security.

back to the top

Set Up the Alternate Synchronization Partner

  1. On PublisherA, use the following script to create a new database named TestA and a new table named t1:
    use master
go
create database TestA
go
use TestA
go
create table t1 (c1 int primary key, c2 int)
  2. On the Tools menu, click Replication, and then click Create and Manage Publications to create a merge publication using TestA and table t1. Name the publication DemoPublication.
  3. Expand Replication Monitor, expand Publishers, and then expand PublisherA. Click DemoPublication: TestA, and then verify that SQL Server successfully created the initial snapshot.
  4. On the Tools menu, click Replication, and then click Configure Publishing, Subscribers, and Distribution. On the Subscribers tab, make sure that you have enabled both PublisherB and SubscriberA as possible subscribers.
  5. On PublisherB, use the following script to create a new database named TestB:
    use master
go
create database TestB
  6. On PublisherA, use SQL Server Enterprise Manager to push a new subscription to PublisherB:
    1. On the Tools menu, click Replication, and then click Push Subscription to Others.
    2. Click TestA, click DemoPublication, and then click Push New Subscription.
    3. Click Show advanced option in this wizard, and then select PublisherB and the database TestB.
    4. In the Push Subscription Wizard (or the Pull Subscription Wizard), on the Set Merge Agent Schedule screen, click Merge Agent to run the Merge Agent continuously and to initialize the data and schema.
    5. On the Set Subscription Priority screen, click Use the following priority to set Priority to the default 75.00. This creates a global subscription to the DemoPublication on PublisherA server to this TestB database.

      NOTE: A subscription with an assigned priority value is a global subscription and a subscription that uses the priority value of the publisher is a local subscription.

      To add a global subscriber, in the Push Subscription Wizard (or the Pull Subscription Wizard), on the Set Subscription Priority screen, click Use the following priority, and then enter a priority number for the subscription. The default priority is 75.00. For more information about global subscribers, see the "Set Subscription Priority" and "Choosing a Resolver" topics in SQL Server 2000 Books Online.
  7. Use Replication Monitor to verify that the Merge Publication ran successfully.
  8. On PublisherB, use the TestB database to create a new merge publication (also named DemoPublication) to publish the same articles subscribed from PublisherA. The new merge publication must have the same name as the publication that you created on PublisherA.
  9. On PublisherA, use Query Analyzer to connect to the published database, TestA, and run the following stored procedure to add PublisherB as an alternate synchronization partner to PublisherA.
    sp_addmergealternatepublisher  @publisher =  'PublisherA' 
    ,  @publisher_db =  'TestA' 
    ,  @publication =  'DemoPublication' 
    ,  @alternate_publisher =  'PublisherB' 
    ,  @alternate_publisher_db =  'TestB' 
    ,  @alternate_publication =  'DemoPublication' 
    ,  @alternate_distributor =  'PublisherB'
  10. In SQL Server Enterprise Manager, click PublisherA, expand Replication, and then under the Publications subfolder, right-click the publication named DemoPublication:TestA.on, and then click Properties. On the Sync Partners tab, click to select the Subscribers to synchronize with other partners than the publisher from which the subscription was created check box. Verify that both PublisherA and PublisherB are enabled as possible synchronization partners.
  11. Repeat step 10 on PublisherB. Be sure to verify that both PublisherA and PublisherB are enabled as possible synchronization partners.
back to the top

Set Up the Subscriber

  1. On SubscriberA, use the following script to create a new database called TestC:
    use master
go
create database TestC
  2. To create a push subscription on PublisherA, use SQL Server Enterprise Manager to create a Push subscription to the SubscriberA server. To do so, on the Tools menu, click Replication, and then click Push Subscription to Others. Expand the TestA database, click the DemoPublication, and then select Push New Subscription. Click Next to start the Push subscription Wizard to create a subscription to the TestC database on the SubscriberA server. Click Next to select the default wizard settings.
  3. For the push subscription on PublisherA, expand the Management folder, click SQL Server Agent, expand Jobs, and then double-click the Merge Agent job for this push subscriber. For example:

    PublisherA-TestA-DemoPublication-SubscriberA-TestC-0

    On the Steps tab, double-click the Run Agent step. In the command box, modify the values of the following parameters to point to the alternate synchronization partner and its Distributor:

    -Publisher [PublisherB] -PublisherDB [TestB] -Distributor [PublisherB]

    Add one more parameter to this Merge Agent job:

    -SyncToAlternate 1

    By default, the Merge Agent uses Microsoft Windows NT authentication to connect to the Distributor of the alternate synchronization partner (-DistributorSecurityMode 1). To use SQL Server standard authentication to connect to the Distributor of the alternate synchronization partner, modify the Merge Agent parameters with the Distributor logon and password. For example:

    -DistributorSecurityMode 0 -DistributorLogin [sa] -DistributorPassword [password]

    Save these changes to the job, and then re-start the Merge Agent. This will create another Merge Agent job, on the alternate distributor server (in this case, it's PublisherB, because it is both a publisher and a distributor). The new Merge Agent job will be from the alternate publisher PublisherB to SubscriberA. By default, it is not running. This is required because if the original distributor is unavailable, you cannot modify the Merge Agent job parameters on that server to point to the alternate publisher or distributor. When you finish on PublisherA, repeat these steps to modify the original Merge Agent job parameters, and point back to the original publisher and distributor, so that you will use the original servers as the default. For example:

    -Publisher [PublisherA] -PublisherDB [TestA] -Distributor [PublisherA]

    Remove the parameter -SyncToAlternate 1.

    Save the changes, and then restart the Merge Agent job.
  4. For a pull subscription, in SQL Server Enterprise Manager, locate the SubscriberA server, where the Merge Agent is running. Expand the Management folder, click SQL Server Agent, expand Jobs, and then double-click the Merge Agent job for this subscriber. For example, on the Tools menu, click Replication, and then click Pull Subscription to 'SubscriberA'. Select TestC, click Pull New Subscription, click PublisherA, and then click DemoPublication. Click to select Yes, Initialize the schema and data, and then click Start the Merge Agent to initialize the subscription immediately. Click Next to select the default wizard settings and to create a pull subscription to the DemoPublication on SubscriberA.

    For a named subscription (a subscription that is not anonymous to the Publisher), make sure that the SubscriberA is enabled at the alternate synchronization partner, PublisherB, so that SubscriberA can synchronize data with PublisherB. If you do not do this, the Merge Agent automatically adds the subscription entry at PublisherB.
  5. Run the Merge Agent between PublisherA and SubscriberA until it successfully completes. On PublisherA, expand Replication Monitor, and then verify that the Merge Agent between PublisherA and SubscriberA ran successfully. Or, on SubscriberA, expand Replication, expand Subscription, and then verify that the Pull subscription status is "succeeded."
back to the top

Use SQL Server Enterprise Manager to Synchronize with Alternate Synchronization Partner

NOTE: This option is available for both push and pull subscriptions.
  1. Determine where the Merge Agent for SubscriberA is running. For a push merge subscription, the Merge Agent is running at the Distributor server, and for a pull merge subscription, the Merge Agent is running at the subscriber server.
  2. In SQL Server Enterprise Manager, locate the server on which SQL Server and the Merge Agent are running, expand the Management folder, click SQL Server Agent, expand Jobs, and then double-click the Merge Agent job for this subscriber. For example:

    PublisherA-TestA-DemoPublication-SubscriberA-TestC-0

    On the Steps tab, double-click the Run Agent step, and then under the command box, modify the values of the following parameters to point to the alternate synchronization partner and its Distributor:

    -Publisher [PublisherB]
    -PublisherDB [TestB]
    -Distributor [PublisherB]

    Add one more parameter to this Merge Agent job:

    -SyncToAlternate 1

  3. By default, the Merge Agent uses Microsoft Windows NT authentication to connect to the Distributor of the alternate synchronization partner (-DistributorSecurityMode 1). To use SQL Server standard authentication to connect to the Distributor of the alternate synchronization partner, modify the Merge Agent parameters with the Distributor logon and password. For example:

    -DistributorSecurityMode 0
    -DistributorLogin [sa]
    -DistributorPassword [password]

  4. Save these changes to the job, and then re-start the Merge Agent.
back to the top

Use Windows Synchronization Manager to Synchronize with Alternate Synchronization Partner

NOTE: This option is only available for pull subscriptions.
  1. On the Start menu, click Programs, click Accessories, and then click Synchronize. Windows Synchronization Manager is available with Microsoft Windows 2000 and with the Microsoft Internet Explorer version 5.0 installation.
  2. Select the publication that you want to synchronize (DemoPublication), and then click Properties. On the Identity tab, click the Alternate Synchronization Partner server, PublisherB, and then choose its publication database, TestB, and the publication, DemoPublication, with which you want to synchronize.
  3. Click OK, and then click Synchronize.
back to the top
Modification Type:MinorLast Reviewed:12/26/2003
Keywords:kbHOWTOmaster KB321176 kbAudDeveloper
©2003 Microsoft Corporation. All rights reserved.