Log Shipping and Replication

Log shipping and replication can coexist, and in some cases, log shipping can be used to protect replication. However, you need to understand how these two features work together and the caveats of implementing them on the same databases.

Important

You cannot use a log shipped secondary s database as part of an active replication environment (such as a Republisher or a Distributor) because it is in STANDBY or NORECOVERY. You can only use the database once it is fully recovered.

Both transactional and merge replication interoperate with log shipping. The main reason to use log shipping with replication in a highly available environment is to provide protection in the event of a Publisher failure. The secondary server can assume the Publisher s role if you meet all conditions, including the renaming of the server. If you cannot rename the server, you have to reinitialize all subscriptions.

Important

If you use SQL Server 7.0 (with Service Pack 2) as your source, you cannot enable log shipping to work with a database that has replication.

Transactional Replication and Log Shipping

If the Publisher fails, you can use log shipping to protect it. This can only be configured between Microsoft SQL Server 2000 instances only; you cannot combine log shipping and replication if SQL Server 7.0 is the source. There are two modes that enable transactional replication to work with log shipping: synchronous and semisynchronous.

Synchronous Mode

Synchronous mode means that you are going to synchronize the various replication agents with your Publisher and Distributor backups using the Publisher and Distributor sync with backup options. The primary benefit of using synchronous mode is that in the event of a role change, your new Publisher (the old secondary) is completely synchronized with the backups. You do not have to worry about the Publisher, Distributor, and Subscriber being out of sync as long as the backups are completely applied after the restore. Note, however, that the restored Publisher will be missing any of the transactions that were not present in the backup.

Tip

If you want to see what transactions might not have yet been propagated to the distribution database, run DBCC OPENTRAN, and look for Oldest Distributed and Oldest Non-Distributed transactions. If these parameters exist, you more than likely are out of sync.

Semisynchronous Mode

If your business needs dictate that the latency of synchronous mode is unacceptable, you can use semisynchronous mode instead. After a restore when using semisynchronous mode, the Publisher and the Subscribers are possibly out of sync because transactions that were on the old primary or Publisher might not have been backed up, sent, and applied to the warm standby. Despite this problem, you can get replication up and running after a role change. Once you perform the role change and the additional steps provided in the later section Performing a Role Change Involving Replication, you must also run the sp_replrestart stored procedure. Once you run sp_replrestart , change the profile of the Distribution Agent to SkipError or set -SkipError on the command line at run time to ensure that transactions that exist at the Subscriber but not the Publisher can be reapplied to the Publisher.

-SkipError is required because the Publisher and Subscriber might be out of sync after issuing the sp_replrestart because the Publisher backup does not match the Subscriber state. If you do not specify -SkipError , additional failures could occur during replication. For example, if the Publisher has a row that the Subscriber does not yet have, then an update or delete to that row could cause a not found failure when replicated to a Subscriber. If the Publisher is missing a row that the Subscriber has, then an insert of a row with the same key could cause a duplicate failure when replicated to the Subscriber. Once the Publisher has been completely synchronized with the Subscribers again, you can disable the -SkipError option.

Merge Replication and Log Shipping

Like transactional replication, merge replication can use log shipping to protect the Publisher. You have two options for using merge replication with log shipping: semisynchronous mode and alternate synchronization partners .

Semisynchronous Mode

This is similar to the semisynchronous mode of transactional replication: your Publishers and Subscribers might be out of sync. If this is the case, you have only two real options. You can reinitialize the Subscribers to the new Publisher immediately after it is recovered, but this could cause data loss. The other option is to synchronize the Publisher with a Subscriber that has newer data.

Alternate Synchronization Partners

Subscribers of merge replication are able to use other servers besides the Publisher to synchronize their data. If the Publisher is down for some reason (planned or unplanned , including a log shipping role change), replication can continue uninterrupted. There are different methods of setting up alternate synchronization partners for named and unnamed subscriptions, and you can use Windows Synchronization Manager, Enterprise Manager, or the SQL Server merge replication ActiveX control to select your alternate synchronization partner. For a named subscription, you have to enable the Subscriber at the designated alternate synchronization partner and also create a subscription that is the same as the one on the current Publisher. If it is an unnamed subscription, set the alternate synchronization partner on the publication itself.

More Info

For more information on alternate synchronization partners and how to enable them, see the topics Alternate Synchronization Partners, How Alternate Synchronization Partners Work, and Optimizing Synchronization in SQL Server Books Online.

Performing a Role Change Involving Replication

If you plan to use the secondary database after it is restored as a new primary (regardless of the model), you need to ensure that the @keep_replication option of the sp_change_secondary_role is set to 1 if you are using the functionality of Microsoft SQL Server 2000 Enterprise Edition. You also have to set the @do_load option to 1 as well; otherwise setting @keep_replication has no impact. If you do not set these parameters, all replication settings are erased when the database is recovered. If this is a custom implementation of log shipping, you need to set the WITH KEEP_REPLICATION option of the RESTORE LOG (or RESTORE DATABASE) functionality. Using WITH KEEP_REPLICATION is not possible with databases restored with NORECOVERY, so if you are using replication, make sure you restore your database with STANDBY.

More Info

To see the exact steps for performing a log shipping role change, see the section Performing a Role Change in Chapter 7, Log Shipping.

In addition to the normal role change process, there are a few additional steps that you need to add after the database is recovered:

1. If you recover a database from secondary status to be the active database servicing requests , you do not need to reapply a SQL Server 2000 service pack unless there are meta data changes that would affect the database in question (as noted earlier). This would be clearly documented in the documentation that ships as part of the update. An exception to this rule occurs if you are using replication and use the KEEP_REPLICATION option when bringing the database online. Once the database is fully recovered, before opening it up to users and applications, run the sp_vupgrade_replication stored procedure to upgrade the replication meta data. If you do not do this, the replication meta data for that database will be out of sync. If running sp_vupgrade_replication is not necessary, it should be noted in the accompanying documentation.

2. You have to rename the secondary server to be the same as the original primary. This affects you in a few ways. First and foremost, you cannot use a clustered instance of SQL Server 2000 as your primary or secondary, as you cannot rename them. Second, if you want to eventually perform a role change back to the original primary, you cannot have two servers of the same name in the same domain.

   Tip

   You can mitigate this using Network Load Balancing like you would to abstract the role change. That way anything connecting to the underlying SQL Server would only have to know about the name to connect to, which is the Network Load Balancing cluster. Again, this is a very specific use of Network Load Balancing with SQL Server and is not meant for load balancing. See the section Implementing Network Load Balancing for SQL Server “Based Architectures in Chapter 5, Designing Highly Available Microsoft Windows Servers, for information on setting up this solution.