Strategies for Backing Up and Restoring Transactional Replication
Microsoft® SQL Server™ 2000 allows you to restore transactional replication databases without reinitializing subscriptions or disabling and reconfiguring publishing and distribution. You can set up replication to work with log shipping, enabling you to use a warm standby server without reconfiguring replication.
Recovering transactional replication from a loss of the publication database or distribution database, without having to reinitialize subscriptions or reconfigure replication, requires the publication database and the distribution database be restored to a consistent point in time. In SQL Server version 7.0 and earlier, this had to be ensured manually by backing up the publication database and distribution database simultaneously, and at the same time ensuring no changes were being made to the databases while the backup was in progress. SQL Server 2000 automatically handles the coordination of the backups of the two databases.
To ensure that you can restore the Publisher or Distributor at any time, SQL Server 2000 requires the replication database option sync with backup be set to true on the publication database and on the distribution database. If you use this option, you will need to back up the publication database and distribution databases (usually you would back up the transaction log or make differential backups) frequently because the frequency of backups determines the latency with which replication delivers changes to Subscribers.
Important Only SQL Server 2000 Publishers support this option. If the distribution database is set to sync with backup, Publishers running SQL Server 7.0 and earlier and using that distribution database will be treated as if the option is not set.
Backing Up and Restoring the Publication Database
Usually the Log Reader Agent runs in continuous mode, monitoring the log for data changes, which it immediately propagates to the distribution database (typically within a few seconds). In addition, because backups of the publication database usually occur on a scheduled basis, the Log Reader Agent may be transferring transactions faster than they are being backed up. If the Publisher fails and is restored, the distribution database may already have transactions that will not exist in the restored publication database because those transactions were not backed up.
Setting the sync with backup option on the publication database ensures that the Log Reader Agent will not propagate any transactions to the distribution database that have not been backed up at the Publisher. This ensures that the last backup can be restored without any possibility of the distribution database having transactions that the restored publication database does not have.
Synchronizing the Log Reader Agent with backing up the publication database means that replication latency (the time it takes for changes at the Publisher to be delivered to the Subscriber), which can often be as low as a few seconds, is now constrained to be equal to the frequency of backups at the Publisher. For example, if you are backing up the transaction log of the publication database every five minutes, replication latency could be as much as five minutes plus the time it takes to complete the backup. On the average, it will be less than five minutes, but more than typical transactional replication latency, which can be tens of seconds. If you synchronize the Log Reader Agent with the backup, it is recommended that you back up the publication database (database backup followed by log and/or differential database backups) as frequently as possible to reduce the time it takes for changes to appear at Subscribers.
To synchronize the publication database to a backup
- Execute sp_replicationdboption '<publicationdatabasename>', 'sync with backup', 'true'.
Note If you change the sync with backup option to false, the truncation point of the publication database will be updated after the Log Reader Agent runs, or after an interval if the Log Reader Agent is running continuously. The maximum interval is controlled by the –MessageInterval agent parameter with a default of 30 seconds.
To determine if the sync with backup option has been set on a publication database, use the IsSyncWithBackup property of the DatabasePropertyex() intrinsic function. You can also run the system stored procedure sp_helpdb to check if this option has been set.
If the increase in replication latency is not acceptable, do not to set the sync with backup option on the publication database. If the publication database fails, it will be possible for the distribution database to have transactions that the restored publication database does not have, and it is not guaranteed that the Subscriber will be in synchronization with the Publisher.
Restoring the Publication Database When the sync with backup Option is False
If you do not set the sync with backup option and allow the distribution database to have transactions that the restored publication database does not have, it is possible to restore a publication database from backup and for replication to continue, but the Subscriber and Publisher may no longer be in synchronization. To accomplish this:
- Restore the publication database. At this point, you will get an error from the Log Reader Agent because it will detect that the Distributor is ahead of the Publisher.
- Run sp_replrestart in the publication database with no parameters. This forces replication to continue even if the Distributor and some Subscribers may now have data that the Publisher no longer has.
- Ensure that that the Distribution Agents, which could now deliver duplicate rows to Subscribers, can continue despite these failures. Choose the –SkipError Distribution Agent profile, or you can manually add the –SkipError parameter to the runtime parameters of the Distribution Agents and supply the errors you want the Distribution Agents to ignore. For more information, see Distribution Agent Profile.
Caution This method can lead to inconsistencies between data at the Publisher and data at the Subscribers.
Backing Up and Restoring the Distribution Database
The distribution database can be restored to the last backup without reconfiguring replication or reinitializing subscriptions. Usually, the Log Reader Agent connects to the publication database, scans the log, retrieves the next set of N transactions that need to be replicated, propagates them to the distribution database, and then indicates to the publication database that the transactions have been successfully committed at the distribution database.
At this point, the publication database can truncate the part of the log that contains these transactions (provided they have been backed up). If the distribution database fails at this point and is restored to a previous backup, it will not be possible for the Log Reader Agent to deliver the missing transactions because the part of the log containing them may have been truncated.
Setting the sync with backup option on the distribution database ensures that the log of the publication database will not be truncated beyond the point up to which all transactions have been propagated to the distribution database. It also ensures that the distribution database with the new transactions has been backed up. The distribution database can be restored to the last backup and the Log Reader Agent will be able to deliver transactions that the restored distribution database is now missing. Replication will continue unaffected.
Important To backup the distribution database more frequently by backing up the transaction logs and setting the sync with backup option, you must set the trunc. log on chkpt. option of sp_dboption to false on the distribution database.
Unlike the publication database, setting the sync with backup option on the distribution database has no effect on replication latency, but it will delay the truncation of the log on the publication database until the corresponding transactions in the distribution database have been backed up.The sync with backup option is available only if the Publisher and Distributor are running SQL Server 2000.
To synchronize the distribution database to a backup
- Execute sp_replicationdboption '<distributiondatabasename>', 'sync with backup', 'true'
To determine if the sync with backup option has been set on a distribution database, use the IsSyncWithBackup property of the databaseproperty() intrinsic function. You can also run the system stored procedure sp_helpdb to check if this option has been set.
Backing Up and Restoring a Subscription Database
To restore the Subscriber to the last backup without any need to reinitialize the subscriptions, ensure that the minimum transaction retention period at the Distributor is greater than the frequency of the backup interval at the Subscriber. This guarantees that when you restore a Subscriber, all the transactions necessary for the Subscriber to catch up will still be available in the distribution database. When you restore a Subscriber, the Distribution Agent delivers any transactions the Subscriber is missing. By default, the minimum transaction retention period is set to 0, and under most circumstances a transaction that has been delivered to all Subscribers will be deleted.
To set the minimum transaction retention period of the Distributor
- In SQL Server Enterprise Manager, expand a server group, expand the Distributor, right-click the Replication folder, and then click Configure Publishing, Subscribers, and Distribution.
- To modify the distribution database, click the Properties button for the distribution database to change the transaction retention period.
Note It is not necessary to back up the Subscribers to restore transactional replication; however, it is essential that you back up the Publisher and Distributor. If the cost of generating a snapshot and reinitializing the subscription is less than the time it would take to restore the subscription database from a backup, there is no need to back up the subscription database.
Backing Up and Restoring the msdb Database
The msdb database contains the job definitions for replication agents that are run under the control of SQL Server Agent. To provide additional security against a system failure, the msdb database on the Distributor and Subscribers that use pull subscriptions must be backed up periodically, whenever a subscription is dropped or a new one added, or whenever a change is made to a replication agent.
The msdb database may also contain Data Transformation Services (DTS) package definitions used in replication if transformable subscriptions are used with any transactional or snapshot publications. To provide assurance against system failure, the msdb database on the Distributor and on the Subscribers that use transformable subscriptions must be backed up periodically as well. These operations should be performed any time the DTS package associated with a publication is modified or each time a subscription is dropped or a new one is added. This ensures that the most up-to-date definitions can be recovered.Backing up and restoring the msdb database allows you to restore replication after a complete loss of the Distributor or Subscriber.
Backing Up and Restoring the master Database
When a new Subscriber is added, an entry for the Subscriber is added to the sysservers table in the master database on the Publisher. Back up the master database at the Publisher and after a Subscriber is added or after an entry for the Publisher is added to the sysservers table in the master database on the Distributor.
To restore replication after loss of the Publisher or Distributor, back up the master database on the Publisher and Distributor each time a new Subscriber or Publisher is added (respectively).