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