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:

You need to preserve the log sequence number (LSN) chain.
You need to perform the final log backup using the NORECOVERY option. Backing up the log with this option puts the database in a state that allows log backups to be restored and ensures that the database’s LSN chain doesn’t deviate.
The primary log shipping server must still be accessible to use this technique.

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

Disable all backup jobs that might back up the database on both log shipping partners.
Disable the log shipping jobs.
Run each log shipping job in order (i.e., backup, copy, and restore).
Drop log shipping.
Manually back up the log of the primary database using the NORECOVERY option. Use the command
```
BACKUP LOG [DatabaseName]
  TO DISK = 'BackupFilePathname'
  WITH NORECOVERY;
```
where DatabaseName is the name of the database whose log you want to back up and BackupFilePathname is the backup file’s pathname (e.g., Z:\SQLServerBackups\TLog.bck).
Restore the log backup on the secondary database using the RECOVERY option, and bring the secondary database online. The primary and secondary databases have now switched positions.
Back up the log of the new primary database (optional).
Restore the log on the new secondary database using the NORECOVERY option (optional).
Reconfigure log shipping.
Re-enable any backup jobs that were disabled.

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:

Disable all the backup jobs that back up the database on both partners.
Remove the secondary database from log shipping or drop database mirroring.
Disable the log shipping jobs and run the jobs in order (i.e., backup, copy, and restore) if you removed the secondary database from log shipping. This step isn’t needed if you dropped database mirroring.
Configure log shipping or database mirroring.
Re-enable any backup jobs that were disabled.

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:

Configure the replication publication as usual (i.e., configure the distributor, enable the publisher, and create the publication).
Disable all backup jobs for the database.
Disable the log shipping jobs.
Run the log shipping jobs in order (i.e., backup, copy, and restore).
Remove the secondary database from the log shipping configuration.
Change the publication to allow initialization from backup files. This is done by changing the Allow initialization from backup files option from False to True in the Publication Properties dialog box, which Figure 1 shows. Note that it isn’t possible to set this option in step 1 if you’re using the GUI. (You can, however, set this option in step 1 if you’re using T-SQL code to create the publication.)
Back up the publisher’s log. This backup will be used to initialize the subscriber.
Restore the log backup on the secondary database using the RECOVERY option.
Create the subscription on the publisher using the sp_addsubscription stored procedure in Listing 1 (more on this shortly).
Create the subscription on the subscriber.
Re-enable the backup jobs.
Drop log shipping on the publisher if desired.

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:\bak\TwoTBDatabase.trn',
  @article = N'all',
  @update_mode = N'read only',
  @subscriber_type = 0;

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

Set the @publication parameter to your publication’s name.
Set the @destination_db parameter to your database’s name.
Set the @subscriber parameter to your server’s name.
Set the @backupdevicename parameter to the pathname of the log backup created in step 7.
Set the @subscription_type parameter to pull if you’re creating a pull subscription. (The default setting is push, so you don’t need to include this parameter if you’re creating a push subscription.)

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.

Comments

Plain text