Strategies for Restoring Merge Replication

In addition to the regular restore guidelines and procedures for Microsoft® SQL Server™, there are additional strategies for restoring the databases involved in merge replication. These strategies vary according to the role the server performs in replication, that is whether the server is a Publisher, Distributor, or Subscriber, and where in the replication topology the failure occurs.

There are six basic strategies for restoring merge replication:

  1. Reenable replication.
  2. Restore Publisher database, and then synchronize with each Subscriber.
  3. Restore Publisher(s) and Distributor, and then synchronize with each Subscriber.
  4. Restore Distributor only.
  5. Drop and re-create subscription(s).
  6. Restore Subscriber database, and then synchronize with each Publisher.

Note As part of any recovery strategy, always keep a current script of your replication settings in a safe location. In the event of catastrophic failure of a server or the need to set up a test environment, you can modify the script by changing a few server name references, and it can be used to help recover your replication settings.

In addition to scripting your current replication settings, you should also script the enabling and disabling of replication. These scripts are part of two recovery strategies for the Publisher or Distributor.


For information about mapping these six recovery strategies to backup strategies, see Matrix of Backup and Restore Strategies for Merge Replication.The number of each recovery strategy corresponds to the numbers in the matrix.

  1. Reenable Replication

    If your Distributor has failed or if you need to recover your entire replication topology, an appropriate strategy for recovering is to use replication scripting to remove and reenable replication. This strategy requires you to:

    1. Generate scripts for creating or enabling replication on each Publisher, Distributor, and Subscriber.
    2. Generate scripts for disabling or dropping replication from each Publisher, Distributor, and Subscriber.
    3. Run the scripts to disable or drop replication from each server.
    4. Re-enable replication by using the Configure Publishing and Distribution Wizard or programmatically.
    5. Run the script for creating or enabling replication on each server.

    One advantage of this strategy is that it removes the need to reconfigure each Publisher, Distributor, and Subscriber manually. Another advantage is that the same strategy can be used with snapshot or transactional replication, thereby providing a unified strategy in applications that use all three types of replication. The disadvantage of this strategy is that it takes longer for the publication to flow from Publisher to Subscriber.

  2. Restore Publisher Database, and then Synchronize with Each Subscriber

    If your Publisher has failed and the backup of the publishing database is available, an appropriate strategy for recovering is to restore the publishing database and then synchronize the Publisher with each Subscriber. This strategy requires you to:

    1. Restore the publishing database from the backup.
    2. Synchronize each subscribing database.

    The advantage of this strategy is that the merge process then applies all of the changes the Subscribers know about, including any changes synchronized from the Publisher since the backup was taken. The disadvantage of this strategy is that it relies heavily on regular backups of the Publisher.

  3. Restore Publisher(s) and Distributor, and then
    Synchronize with Each Subscriber

    If either your Publisher or Distributor has failed and backups are available, an appropriate strategy is to restore both the Publisher and Distributor, and then synchronize the Publisher with each Subscriber. This strategy requires you to:

    1. Restore the publishing database from the backup.
    2. Restore the distribution database and snapshot folder from the backup.
    3. Synchronize each subscribing database.

    The advantage of this strategy is that it restores the Publisher or Distributor without having to reenable replication and without having to regenerate snapshots from Publisher. The merge process then applies all of the changes the Subscribers know about, including any changes synchronized from the Publisher since the backup was taken. The disadvantage of this strategy is that it relies on a coordinated backup of the publishing and distribution databases.

  4. Restore Distributor Only

    If your Distributor has failed but no Publishers or Subscribers have been added since the last backup of the Distributor backup, an appropriate strategy is to restore only the distribution database. This strategy requires that you restore only the distribution database and snapshot folder from the backup. If any Publishers or Subscribers have been added since the last Distributor backup, you should restore both the Publisher and the Distributor as described in the third strategy.

  5. Drop and Re-create Subscriptions

    If your Subscriber has failed and the backup of the subscribing database is not available, an appropriate strategy for recovering is to drop the Subscriber and re-create the subscription. The strategy requires you to:

    1. Drop the server from the list of enabled Subscribers.
    2. Enable the server as a Subscriber.
    3. Reinitialize the Subscriber.

    The advantage of this strategy is that it can be done easily. Another advantage is that the same strategy can be used with snapshot or transactional replication, thereby providing a unified strategy in applications that use all three types of replication. The disadvantage of this strategy is that it can require processing and network resources in an environment where network connections are slow or processing power limited.

  6. Restore Subscriber Database, and then Synchronize with Each Publisher

    If your Subscriber has failed, has subscriptions to multiple Publishers, and the backup of the subscribing database is available, an appropriate strategy for recovering is to restore the subscribing database and then synchronize the Subscriber with each Publisher. This strategy requires you to:

    1. Restore the subscribing database from the backup.
    2. Synchronize the subscribing database with each publishing database.

    The advantage of this strategy is that the merge process then applies all of the changes the Publishers know about, including any changes synchronized from the Subscriber since the backup was taken. The disadvantage of this strategy is that is relies heavily on regular backups of the Subscriber.

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.