Replication

Transactional Replication and Log Shipping

Microsoft® SQL Server™ 2000 transactional replication can be configured to work with log shipping to provide a warm standby server recovery option if the Publisher fails.

You must be running Microsoft SQL Server 2000 Enterprise Edition to use log shipping. There are two modes for replication and log shipping working together: synchronous and semi-synchronous.

Synchronous Mode

In synchronous mode, the sync with backup option is set on the publication database. This causes the Log Reader Agent to synchronize with the publication database backup. In this mode, the Log Reader Agent does not propagate any transactions from the Publisher to the distribution database if they have not been backed up. This ensures that no Subscriber will get ahead of the Distributor; however, this also means that replication latency (the time it takes changes made at the Publisher to appear at the Subscriber), which can usually be as low as a few seconds, is now constrained to be greater than or equal to the log shipping interval. Typically, this is between two and ten minutes.

The advantage of using synchronous mode is that after failing over to the new Publisher, all replication servers are in synchronization.

To configure replication to work with log shipping in synchronous mode

  1. On the publication database, execute sp_replicationdboption '<publicationdatabasename>', 'sync with backup', 'true'.

    When this option is set, the Log Reader Agent will not process the transaction until it is backed up through either database backup or log backup.

  2. Set up log shipping for the publication database.

  3. When the Publisher fails, restore the last log of the database using the KEEP_REPLICATION option with RESTORE LOG. This will keep all the replication settings.

  4. Rename the warm standby server to the name of the original Publisher. Replication will continue to distribute data changes to Subscribers.
Semi-Synchronous Mode

If the increased latency that occurs in synchronous mode is unacceptable, and the possibility that the warm standby Publisher and the Subscribers are not synchronized is acceptable, use semi-synchronous mode.

The warm standby Publisher and the Subscribers may not be synchronized because the performance of the Log Reader Agent and the backups are not synchronized. This allows transactions that may not have been backed up on the Publisher and shipped to the warm standby to be propagated to the Distributor and then to Subscribers. Although the Publisher and the Subscribers are now out of synchronization, you can restart replication.

To configure replication to work with log shipping in semi-synchronous mode

  1. Set up log shipping for the publication database.

  2. When the Publisher fails, restore the last log of the database using the KEEP_REPLICATION option with RESTORE LOG. This will keep all replication settings.

  3. Rename the warm standby server to the name of the original Publisher. You may receive an error message from the Log Reader Agent that the publication database and the distribution database are not synchronized.

  4. Execute sp_replrestart. This stored procedure can be used to force the Log Reader Agent to ignore all the previous replicated transactions in the publication database log. Transactions applied after the completion of the stored procedure will be processed by the Log Reader Agent. You can restart the Log Reader Agent after the stored procedure executes successfully.

    Important  The sp_replrestart system stored procedure should be used only with log shipping. It can also be used under controlled circumstances if you need to restore the publication database are you are not using the sync with backup option. This option should be used only when the Log Reader fails to process replicated transactions in the publication database log and there are no other ways to resolve the problem.

  5. Set the profile of the Distribution Agent to the Skip Error profile because lost transactions (some of which have already been replicated to the Subscribers) may be reapplied at the Publisher.