Strategies for Backing Up and Restoring Merge Replication

SQL Replication

Replication

Strategies for Backing Up and Restoring Merge Replication

Microsoft® SQL Server™ 2000 allows you to restore replicated databases without reinitializing subscriptions or disabling and reconfiguring Publishers or Subscribers. With merge replication, you can use the latest data stored at other sites to resynchronize a server with changes that may not have been preserved in a recent backup. You can also configure replication to work with log shipping, enabling you to use a warm standby server without reconfiguring replication.

Because merge replication stores change tracking meta data directly in your publication and subscription databases, there is no general requirement that you restore the publication database and distribution database to a consistent point in time. When you back up or restore a publication or subscription database, you also back up or restore the system meta data used to track replicated changes to a point in time consistent with your replicated data.

Merge replication ensures data convergence among all replicas in your topology. When it is necessary to restore a backup of a database, there are generally multiple options for recovery depending on the role of the database requiring a restore.

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

Backing Up and Restoring the Publication Database

When restoring a publication database, you may want to reinitialize all subscriptions to any restored publications. You may also want to synchronize immediately with a Subscriber that has the latest data. Reinitializing all subscriptions provides a convenient mechanism to reset all replicas of the publication database to a state consistent with the restored publication database. Alternatively, you may want to synchronize your publication database immediately with a subscription database that has the latest data, and attempt to recover any changes synchronized with that replica but not included in the most recent publication database backup of publication database transaction log backup.

For example, suppose a publication database is backed up, changes are made in the publication database, a subscription database is synchronized with the publication database, and then the publication database is restored from backup. There are two choices for restoring the database:

  • Synchronize the publication database with the subscription database and all changes made previously in the publication database, but not represented in the restored backup, will be uploaded from the subscription database to the publication database.

  • Reinitialize all subscriptions to the publications in the publication database.

You may want to reinitialize all subscriptions if you are restoring a publication database to an earlier point in time as a mechanism to recover from an erroneously performed batch data operation, or if you are recovering your publication database to an earlier state. Reinitializing all subscriptions extends the recovery to an earlier state to all replicas within the enterprise. If you choose this option, it is recommended that you generate a new snapshot for delivery to reinitialized Subscribers immediately after restoring your publication database.

Performing replication configuration or maintenance activities in the publication database, synchronizing those changes with subscription databases, and then restoring the publication database to a state prior to the configuration changes may require a reinitialization of all subscriptions to effected publications in the restored publication database. Subscription databases are expected to have the same publication definition represented in the corresponding publication database whenever synchronization occurs.

It is recommended that you back up the publication database (either incremental or full backup) whenever changes are made to a replicated objects schema (for example, adding or dropping a column) or to a publication property even though you may have regularly scheduled database and log backups to be performed on a regular schedule. A description of some common actions affecting replication configuration or replicated object schema are described later in this topic.

Backing Up and Restoring the Distribution Database

When restoring a publication database that contains only merge publications, it is not always necessary to restore the corresponding distribution database to a consistent point in time. The distribution database has a limited role in merge replication as the common store for synchronization history and error tracking information. It does not store any data used in change tracking and it does not provide temporary storage of merge replication changes to be forwarded to subscription databases. In most cases, it is not necessary to restore a distribution database when restoring a publication database backup for merge publications. The exception is when any database maintenance activity has been performed in the publication database or distribution database that affects replication configuration or replicated object schema.

Backing Up and Restoring a Subscription Database

Similar to backing up a publication database, when a subscription database is restored, you are restoring replication change tracking data to a state consistent with the replicated data. Synchronizing the subscription database with each of its publications following a restore results in the Merge Agent downloading any changes that the subscription database backup has not yet received from the various publication databases for which it has Subscribers. A reinitialization of the subscription database is generally not required, and only the data changes since the backup was taken are synchronized between the publication database and the subscription database.

To restore a subscription database without any need to reinitialize its subscriptions, ensure that the restored database backup represents the subscription database in a state in which it has synchronized all subscriptions within the defined publication retention period.  Restoring a database (and transaction logs) to a point in time prior to the retention period of the publication will require that the subscriptions in the subscription database be reinitialized. For more information about retention periods, see Subscription Deactivation and Expiration.

Backing Up and Restoring a Republishing Database

When a database subscribes to data from a Publisher and in turn publishes that same data to other subscription databases, it is referred to as a republishing database. When restoring a republishing database, follow the guidelines described in the Backing Up and Restoring a Publication Database and Backing Up and Restoring a Subscription Database sections in this topic.

Backing Up and Restoring the msdb System Database

The msdb database at the Publisher contains the job definitions for replication agents that are run under the control of SQL Server Agent. The msdb database at the Distributor contains the job schedule, steps, alerts, and other job components for all Snapshot Agents, agents used with push subscriptions, and miscellaneous replication agents. The msdb database at each Subscriber contains similar job information for all pull subscription agents. The msdb database at the Distributor also contains the agent profile information for all replication agents.

To provide improved recovery options if you need to restore one or more replicated databases, the msdb database should be backed up periodically. In additionensure that an accurate backup of the msdb database is taken whenever any database maintenance activity has been performed in the publication database, distribution database, or subscription database that affects replication configuration (especially agent profiles or agent properties) .Backing up the msdb database is necessary in the event you want to restore replication after the loss a Distributor or Subscriber.

Backing Up and Restoring the master Database

It is not generally necessary to back up the master database on a regular basis; however, similar to backing up the msdb database, the master database is involved in storing limited configuration information regarding the replicated databases on any instance of SQL Server. For example, when a server is enabled as a Distributor, Publisher, or Subscriber, the sysservers table in the master database on the Distributor is updated. To restore replication after the loss of a master database on a Publisher or Distributor, back up the master database on the Publisher and Distributor. It is recommended that you back up the master database periodically and when any database maintenance activity has been performed in the publication database, distribution database, or subscription database that effects replication configuration (especially changes to enabled Publishers or Subscribers).

Merge Replication, Log Shipping, and Alternate Synchronization Partners

Microsoft® SQL Server™ 2000 merge replication can be configured to work with log shipping to provide a warm standby server recovery option if the Publisher fails. Merge replication also allows Subscribers to synchronize with an alternate Publisher in the event the Publisher at which their subscriptions originated is unavailable.

Alternate synchronization partners can be used with any edition of SQL Server 2000 that supports merge replication; however, you must be running Microsoft SQL Server 2000 Enterprise Edition to use log shipping. Because merge replication tracks changes directly in the publication database, merge replication works with log shipping in a semi-synchronous mode only.

Semi-Synchronous Mode

In semi-synchronous mode, there is a possibility that the warm standby Publisher and its Subscribers may not be synchronized at the point of failover if any changes synchronized between the primary Publisher and its Subscribers have not yet been transferred using log shipping to the warm standby Publisher at the point of failover.When restoring a publication database from backup, you may want to reinitialize all subscriptions to publications following a failover, or you may elect to synchronize immediately with a Subscriber that has the latest data. Typically, you can synchronize immediately, and use log shipping to help provide continuous synchronization of updatable replicas if the primary Publication server fails.

Alternate Synchronization Partners

Similar to log shipping, using alternate synchronization partners during merge replication is an option that supports continuous synchronization in the event of a failure of the primary Publisher. Log shipping can be used to send all changes, including schema changes, user modifications, and database maintenance activities, to a warm standby Publisher. Specifying an alternate synchronization partner for publications defined at a Publisher provides a method to synchronize data changes to replicated tables with servers other than the Publisher at which a subscription originated. Synchronizing with alternate synchronization partners provides the ability for a Subscriber to synchronize data even if the primary Publisher is unavailable. For more information, see Alternate Synchronization Partners.

Common Actions Requiring an Updated Backup

In addition to regularly scheduled backups, it is recommended that you update backups of the publication, distribution, subscription, msdb, and master databases after making modifications to your replication schema or topology.

Backup the publication database after:

  • Creating new publications.

  • Altering any publication property including filtering.

  • Adding articles to an existing publication.

  • Performing a Publication-wide reinitialization of subscriptions.

  • Altering any published table using a replication schema change.

  • Performing on-demand script replication.

  • Cleaning up merge meta data (running sp_mergecleanupmetadata).

  • Changing any article property including changing the selected article resolver.

  • Dropping any publications.

  • Dropping any articles.

  • Disabling replication.

Backup the distribution database after:

  • Creating or modifying replication agent profiles.

  • Modifying replication agent profile parameters.

  • Changing the replication agent properties (including schedules) for any push subscriptions.

Backup the subscription database after:

  • Changing any subscription property.

  • Changing the priority for a subscription at the Publisher.

  • Dropping any subscriptions.

  • Disabling replication.

Backup the msdb system database after:

  • Enabling or disabling replication.

  • Adding or dropping a distribution database (at the Distributor).

  • Enabling or disabling a database for publishing (at the Publisher).

  • Creating or modifying replication agent profiles (at the Distributor).

  • Modifying any replication agent profile parameters (at the Distributor).

  • Changing the replication agent properties (including schedules) for any push subscriptions (at the Distributor).

  • Changing the replication agent properties (including schedules) for any pull subscriptions (at the Subscriber).

Backup the master system database after:

  • Enabling or disabling replication.

  • Adding or dropping a distribution database (at the Distributor).

  • Enabling or disabling a database for publishing (at the Publisher).

  • Adding the first or dropping the last publication in any database (at the Publisher).

  • Adding the first or dropping the last subscription in any database (at the Subscriber).

  • Enabling or disabling a Publisher at a Distribution Publisher (at the Publisher and Distributor).

  • Enabling or disabling a Subscriber at a Distribution Publisher (at the Subscriber and Distributor).