3 Log Shipping Techniques

Recent SQL Server releases have had a major focus on high availability. Early previews of the next release of SQL Server, code-named Denali (SQL Server 2012), show that this trend will continue. Somewhere in the midst of the new availability technologies and feature sets, log shipping seems to have fallen by the wayside. Although log shipping received a much-needed face lift with SQL Server 2005, there were only minor enhancements in SQL Server 2008.

Although log shipping isn’t as widely used as it once was, it’s still a vital tool that has many applications, such as using warm standbys for disaster recovery, migrating large databases, and providing read-only access to data. Log shipping isn’t difficult to understand or use. There are three techniques that can save you a lot of time and make working with log shipping painless: reversing log shipping, switching between log shipping and database mirroring, and converting log shipping to transactional replication.

Reversing Log Shipping

Reversing log shipping is an often overlooked practice. When DBAs need to fail over to a secondary log shipping server, they tend to worry about getting log shipping back up later. This is especially true in the case of very large databases. If you're using log shipping as your primary disaster recovery solution and you need to fail over to the secondary log shipping server, you should get log shipping running as quickly as possible. With no disaster recovery failover in place, you might be running exposed.

Reversing log shipping is simple. It doesn’t require reinitializing the database with a full backup if performed carefully. However, it’s crucial that you remember the following:

To fail over to a secondary log shipping server, follow this 10-step process:

Note that step 7 and step 8 are listed as optional because they’re not required for establishing log shipping. However, I recommend performing these steps to ensure that the log shipping configuration will proceed without any problems.

With a few minor adjustments, this 10-step process works with multiple secondary log shipping databases. You perform the same basic steps, keeping in mind that the other secondary databases will still be secondary databases after the failover. After you back up the log on the new primary database, you should use the NORECOVERY option to restore that backup on all the planned secondary databases. You can then add them as secondary databases to the new primary database.

Switching Between Log Shipping and Database Mirroring

Log shipping and database mirroring are both initialized with a full backup and both rely on the LSN chain for synchronization. These similarities make it possible to switch between the two without reinitializing with a full backup. This technique is useful when you have either log shipping or database mirroring configured and you want to switch to the other technology for a short time. The switch can go either way. For example, I use this technique when I need to perform maintenance on log shipping servers.

Switching between log shipping and database mirroring doesn’t involve manually performing any database backups or restores. It’s as simple as dropping whichever technology is configured, then configuring the other. However, a key concept to remember is that you need to prevent any backups from occurring when neither technology is configured.

To switch between log shipping and database mirroring, follow these steps:

When a failover will be short-term, I recommend switching between log shipping and database mirroring because this technique requires much less work to set up than reversing log shipping. When a failover is intended to be long-term, reversing log shipping is the best technique to use.

Converting Log Shipping to Transactional Replication

Initializing transactional replication from a backup was introduced in SQL Server 2005. What some people might not realize is that the backup used for initialization doesn’t have to be a full backup. You can initialize a subscriber with a log backup or differential backup. For example, I used to lead a team that was responsible for a 1.8TB OLTP database that was replicated to two replica servers. Our general process for configuring replication for this database was to start by setting up log shipping. We could generally get to this point in the process within a reasonable time frame, thanks to a lot of work we did to optimize the backup and restore processes. We then completed the replication setup the following day, knowing that the subscriber database had been kept synchronized.

The process for converting log shipping to transactional replication is fairly simple. The following steps detail the process, starting from the point at which log shipping is configured but replication isn’t:

Most of these steps can be performed using the GUI in SQL Server Management Studio (SSMS). However, to create the subscription on the publisher in step 9, you must use the sp_addsubscription stored procedure because the GUI doesn’t support the options required for initializing the subscriber from a backup. The sp_addsubscription stored procedure in Listing 1 creates a pull subscription for the TwoTBDatabase database on the SQLSrv02 server.

USE TwoTBDatabase;EXEC sp_addsubscription  @publication = N'TwoTBDatabase_pub',  @subscriber = N'SQLSrv02',  @destination_db = N'TwoTBDatabase',  @subscription_type = N'Pull',  @sync_type = N'initialize with backup',  @backupdevicetype = N'Disk',  @backupdevicename = N'e:bakTwoTBDatabase.trn',  @article = N'all',  @update_mode = N'read only',  @subscriber_type = 0;

Here’s how to customize the stored procedure for your environment:

The rest of the parameters don’t need to be changed.

Combining the Techniques

You can use the three log shipping techniques separately or combine them. For example, I used all three techniques when my team ran into some problems after a production platform migration last year. Figure 2 shows the new production platform, which consists of a two-node failover cluster, one log shipping server, two replica servers, and a dedicated remote replication distributor.

Figure 2: The production platform in which the three techniques were applied

After completing the migration, we encountered some problems with the new failover cluster. To remediate the immediate performance problem, we failed over the database from the cluster to the log shipping server and reconfigured log shipping to one of the replica servers. The failover also gave us the ability to take the cluster offline as needed for troubleshooting.

After the cluster’s problems were resolved, we scheduled some downtime to fail back the cluster. During that time, we failed over the database from the log shipping server to the cluster, reversed the log shipping back to the log shipping server, and reconfigured the replica server as a subscriber to the publication on the cluster—all of which took less than 15 minutes. It took longer to reconfigure the front-end web servers to point to the cluster. Overall, the system was down for only a half hour.

As this real-world example illustrates, the three log shipping techniques that I discussed can minimize downtime when problems arise. They can also save you a lot of time when performing tasks such as initializing transactional replication and performing server maintenance. If you’d like to see a demonstration of the techniques, check out the video at the SQL Soldier web page. In this demo, I perform a log shipping failover, configure log shipping back to the original server, and configure replication to a third server.