Strategies for Backing Up Replication

In addition to the regular backup guidelines and procedures for Microsoft® SQL Server™, there are additional strategies for backing up the databases involved in snapshot, transactional, or 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. Always initiate additional backups whenever the replication topology is altered by the addition of new publications, subscriptions, or changes to scheduled jobs.

There are four basic strategies for backing up databases used in snapshot, transactional, or merge replication:

• Back up Publisher.
• Back up Publisher and Distributor.
• Back up Publisher and Subscriber(s).
• Back up Publisher, Distributor and Subscriber(s).

Note As part of any backup 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 use it 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 the backup of the Publisher or Distributor.

Back Up Publisher

The simplest strategy for backing up replication is to back up only the Publisher in your replication topology. This strategy requires you to back up the publishing database regularly on the server where the Publisher is located. Before starting the backup process, be sure to synchronize with any immediate-updating Subscribers or Subscribers to merge publications.

The advantages of backing up only the Publisher are that it does not require coordinating the backup with the backup of any other server and it requires the least amount of computing and storage resources. The disadvantage of this strategy is that restoring replication setup in the event of a Publisher or Distributor failure is time consuming.

Back Up Publisher and Distributor

This strategy involves a coordinated backup of both the Publisher and the Distributor. This strategy requires you to:

1. Synchronize the Publisher with any Subscribers to merge publications. If you are using publications that allow immediate-updating Subscribers, you have the option of generating a new snapshot at the Publisher.
2. Schedule a time when the both the Publisher and Distributor can be backed up simultaneously (or as closely as possible).
3. Back up the publishing database at the Publisher.
4. Back up the distribution database and the snapshot folder at the Distributor.

The advantages of this strategy are that it accurately preserves the snapshot of the publication as well as the history, error, and replication agent information for your application. It also allows you to recover faster in the event of a Publisher or Distributor failure because there is no need to reestablish replication. The disadvantages of this strategy are that it requires coordination between two backups, and it requires more computing and storage resources than backing up the Publisher only.

Note Using a local Distributor further simplifies the strategy by isolating all required backup maintenance to be scheduled on only one server.

Back up Publisher and Subscriber(s)

This strategy is similar to backing up the Publisher and places a higher priority on preserving data at the Subscriber. This strategy requires you to regularly back up the publishing database on the server where the Publisher is located. It also  requires you to regularly back up the subscribing database on the server where the Subscriber is located. Before starting the backup process, be sure to synchronize with any immediate-updating Subscribers or Subscribers to merge publications.

The advantages of this strategy are that it reduces the amount of time required to recover a Subscriber by avoiding the need to reinitialize the Subscriber with a new snapshot, especially where network connections are slow or processing power is limited. The disadvantage of this strategy is that administering the backups when there are many Subscribers can be quite complex.

Note Use the automatic validation features of replication to ensure the Subscriber has the correct data prior to the backup.

Back up Publisher, Distributor, and Subscriber(s)

The most complex backup strategy is to back up all the Publishers, Distributors, and Subscribers in your replication topology. This strategy requires you to:

1. Synchronize the Publisher with any Subscribers to merge publications.
2. Schedule a time when both the Publisher and Distributor can be backed up simultaneously (or as closely as possible).
3. Back up the publishing database at the Publisher.
4. Back up the distribution database and the snapshot folder at the Distributor.
5. Back up the subscribing database at the Subscriber.

The advantages of this strategy are that it preserves all the complexity and configurations of your replication topology and it provides a greater guarantee that the full state of the application can be recovered regardless of where the failure occurs. The disadvantages of this strategy are that it requires the most computing and storage resources, and it requires the most time to implement recovery.

See Also