Upgrading SQL Server 2005 Log Shipping to SQL Server 2008

It is possible to preserve log shipping configurations when upgrading from SQL Server 2005 to SQL Server 2008 R2. This topic describes alternative scenarios and best practices for upgrading a log shipping configuration.

Notes

Backup compression was introduced in SQL Server 2008 Enterprise. An upgraded log shipping configuration uses the backup compression default server-level configuration option to control whether backup compression is used for the transaction log backup files. The backup compression behavior of log backups can be specified for each log shipping configuration. For more information, see How to: Enable Log Shipping (SQL Server Management Studio).

Protect Your Data Before the Upgrade

As a best practice, we recommend that you protect your data before a log shipping upgrade.

To protect your data

1. Perform a full database backup on every primary database.

   For more information, see:

   • How to: Back Up a Database (SQL Server Management Studio).

   • How to: Create a Full Database Backup (Transact-SQL)

   • BACKUP (Transact-SQL)

2. Run the DBCC CHECKDB command on every primary database.

Upgrading the Monitor Server Instance

The monitor server instance, if any, can be upgraded at any time.

While the monitor server is being upgraded, the log shipping configuration continues to work, but its status is not recorded in the tables on the monitor. Any alerts that have been configured will not be triggered while the monitor server is being upgraded. After the upgrade, you can update the information in the monitor tables by executing the sp_refresh_log_shipping_monitor (Transact-SQL) system stored procedure.

The Upgrade Process for Configurations with a Single Secondary Server

The upgrade process described in this section assumes a configuration consisting of the primary server and only one secondary server. This configuration is represented in the following illustration, which shows a primary server instance, A, and a single secondary server instance, B.

For information about upgrading multiple secondary servers, see "Considerations for Upgrading Multiple Secondary Servers," later in this topic.

Upgrading the Secondary server instance

The upgrade process involves upgrading the secondary server instances of a SQL Server 2005 log shipping configuration to SQL Server 2008 R2 before upgrading the primary server instance. Always upgrade the secondary server instance first. If the primary server were upgraded before a secondary server, log shipping would fail because a backup created on a newer version of SQL Server cannot be restored on an older version of SQL Server.

Log shipping continues throughout the upgrade process because the upgraded secondary servers continue to restore the log backups from the SQL Server 2005 primary server. The process for upgrading the secondary server instances depends partly on whether the log shipping configuration possesses multiple secondary servers. For more information, see "Considerations for Upgrading Multiple Secondary server instances," later in this topic.

While the secondary server instance is being upgraded, the log shipping copy and restore jobs do not run, so unrestored transaction log backups will accumulate. The amount of accumulation depends on the frequency of scheduled backup on the primary server. Also, if a separate monitor server has been configured, alerts might be raised indicating restores have not been performed for longer than the configured interval.

Once the secondary server has been upgraded, the log shipping agents jobs resume and continue to copy and restore log backups from the primary server instance, server A. The amount of time required for the secondary server to bring the secondary database up to date varies, depending on the time taken to upgrade the secondary server and the frequency of the backups on the primary server.

Notes

During the server upgrade, the secondary database is not upgraded to a SQL Server 2008 R2 database. It will get upgraded only if it is brought online.

Important

The RESTORE WITH STANDBY option is not supported for a database that requires upgrading. If an upgraded secondary database has been configured by using RESTORE WITH STANDBY, transaction logs can no longer be restored after upgrade. To resume log shipping on that secondary database, you will need to set up log shipping again on that standby server. For more information about the STANDBY option, see RESTORE Arguments (Transact-SQL).

Upgrading the Primary server instance

When planning an upgrade, a significant consideration is the amount of time that your database will be unavailable. The simplest upgrade scenario involves the database being unavailable while you upgrade the primary server (scenario 1, below).

At the cost of a more complicated upgrade process, you can maximize your database availability by failing over the SQL Server 2005 primary server to a SQL Server 2008 R2 secondary server before upgrading the original primary server (scenario 2, below). There are two variants of the failover scenario. You can switch back to the original primary server and keep the original log shipping configuration. Alternatively, you can remove the original log shipping configuration before upgrading the original primary server and later create a new configuration using the new primary server. This topic describes each of these scenarios.

Important

Be sure to upgrade the secondary server instance before upgrading the primary server instance. For more information, see "Upgrading the Secondary server instance," earlier in this topic.

Scenario 1: Upgrade Primary Server Instance Without Failover

This is the simpler scenario, but it causes more downtime than using failover. The primary server instance is simply upgraded and the database is unavailable during this upgrade.

Once the server is upgraded, the database is automatically brought back online, which causes it to be upgraded. After the database is upgraded, the log shipping jobs resume.

Scenario 2: Upgrade Primary Server Instance with Failover

This scenario maximizes availability and minimizes downtime. It utilizes a controlled failover to the secondary server instance, which keeps the database available while the original primary server instance is upgraded. Downtime is limited to the relatively short time required to fail over, rather than the time required to upgrade the primary server instance.

Upgrading the primary server instance with failover involves three general procedures: performing a controlled failover to the secondary server, upgrading the original primary server instance to SQL Server 2008 R2, and setting up log shipping on a SQL Server 2008 R2 primary server instance. These procedures are described in this section.

Important

If you plan to have the secondary server instance as the new primary server instance, you need to remove the log shipping configuration. Log shipping will need to be reconfigured from the new primary to the new secondary, after the original primary server instance has been upgraded. For more information, see Removing Log Shipping.

Procedure 1. Perform a controlled failover to the secondary server

Controlled failover to the secondary server:

1. Manually perform a tail-log backup of the transaction log on the primary database specifying WITH NORECOVERY. This log backup captures any log records that have not been backed up yet and takes the database offline. Note that while the database is offline, the log shipping backup job will fail.

   The following example creates a tail log backup of the AdventureWorks2008R2 database on the primary server. The backup file is named Failover_AW_20080315.trn:

   BACKUP LOG AdventureWorks2008R2 
     TO DISK = N'\\FileServer\LogShipping\AdventureWorks2008R2\Failover_AW_20080315.trn' 
      WITH NORECOVERY;
   GO
   

   We recommend that you use a distinct file naming convention to differentiate the manually-created backup file from the backup files created by the log shipping backup job.

2. On the secondary server:

   1. Ensure that all backups taken automatically by the log shipping backup jobs have been applied. To check which backup jobs have been applied, use the sp_help_log_shipping_monitor (Transact-SQL) system stored procedure on the monitor server or on the primary and secondary servers. The same file should be listed in the last_backup_file, last_copied_file, and last_restored_file columns. If any of the backup files have not been copied and restored, manually invoke the agent copy and restore jobs for the log shipping configuration. For more information, see How to: Start a Job (SQL Server Management Studio) or sp_start_job (Transact-SQL).

   2. Copy your the final log backup file that you created in step 1 from the file share to the local location that is used by log shipping on the secondary server.

   3. Restore the final log backup specifying WITH RECOVERY to bring the database online. As part of being brought online, the database will upgraded to SQL Server 2008 R2.

      The following example restores the tail log backup of the AdventureWorks2008R2 database on the secondary database. The example uses the WITH RECOVERY option, which brings the database online:

      RESTORE LOG AdventureWorks2008R2 
        FROM DISK = N'c:\logshipping\Failover_AW_20080315.trn' 
         WITH RECOVERY;
      GO
      

      Notes

      For a configuration that contains more than one secondary server, there are additional considerations. For more information, see "Considerations for Upgrading Multiple Secondary server instances," later in this topic.

   4. Fail over the database by redirecting clients from the original primary server (server A) to the online secondary server (server B).

   5. Take care that the transaction log of the secondary database does not fill while the database is online. To prevent the transaction log from filling, you might need to back it up. If so, we recommend that you back it up to a shared location, a backup share, to make the backups available for restoring on the other server instance.

Procedure 2. Upgrade the Original Primary Server Instance to SQL Server 2008

After you upgrade the original primary server instance to SQL Server 2008 R2, the database will still be offline and in the SQL Server 2005 format.

Procedure 3. Set Up Log Shipping on SQL Server 2008

The rest of the upgrade process depends on whether log shipping is still configured, as follows:

• If you have kept the SQL Server 2005 log shipping configuration, switch back to the original primary server instance. For more information, see "To switch back to the original primary server instance," later in this section.

• If you removed the log shipping configuration before failing over, create a new log shipping configuration in which the original secondary server instance is the new primary server instance. For more information, see "To keep the old secondary server instance as the new primary server instance," later in this section.

To switch back to the original primary server instance

1. On the interim primary server (server B), back up the tail of the log using WITH NORECOVERY to create a tail-log backup and take the database offline. The tail log backup is named Switchback_AW_20080315.trn.For example:

   BACKUP LOG AdventureWorks2008R2 
     TO DISK = N'\\FileServer\LogShipping\AdventureWorks2008R2\Switchback_AW_20080315.trn' 
      WITH NORECOVERY;
   GO
   

2. If any transaction log backups were taken on the interim primary database, other than the tail backup that you created in step 1, restore those log backups using WITH NORECOVERY to the offline database on the original primary server (server A). The database is upgraded to SQL Server 2008 R2 when the first log backup is restored.

3. Restore the tail-log backup, Switchback_AW_20080315.trn, on the original primary database (on server A) using WITH RECOVERY to bring the database online.

4. Fail over back to the original primary database (on server A) by redirecting clients to the online secondary server from the original primary server.

After the database comes online, the original log shipping configuration will resume.

To keep the old secondary server instance as the new primary server instance

Establish a new log shipping configuration using the old secondary server instance, B, as the primary server and the old primary server instance, A, as the new secondary server, as follows:

Important

The old log shipping configuration should have been removed from the original primary server at the start of the process before taking the manual transaction log backup that took the database offline.

1. To avoid performing a complete backup and restore of the database on the new secondary server (server A), apply the log backups from the new primary database to the new secondary database. In the example configuration, this involves restoring the log backups taken on server B to the database on server A.

2. Back up the log from the new primary database (on server B).

3. Restore the log backups to the new secondary server instance (server A) using WITH NORECOVERY. The first restore operation updates the database to SQL Server 2008 R2.

4. Configure log shipping with the former secondary server (server B) as the primary server instance.

   Important

   If you use SQL Server Management Studio, specify that the secondary database is already initialized.

   To configure log shipping

   • How to: Enable Log Shipping (SQL Server Management Studio)

   • How to: Enable Log Shipping (Transact-SQL)

5. Fail over the database by redirecting clients from the original primary server (server A) to the online secondary server (server B).

   Important

   When you failover to a new primary database, you should ensure that its metadata is consistent with the metadata of the original primary database. For more information, see Managing Metadata When Making a Database Available on Another Server Instance.

Considerations for Upgrading Multiple Secondary server instances

This configuration is represented in the following illustration, which shows a primary server instance, A, and two secondary server instances, B and C.

Always upgrade all the secondary server instances before you upgrade the primary server.

Upgrading With Failover and Switching Back to the Original Primary Server

When upgrading the primary instance with failover the process is more complex when there are multiple secondary server instances. In the following procedure, after all the secondary servers are upgraded, the primary server is failed over to one of the upgraded secondary databases. The original primary server is upgraded, and log shipping is failed over back to it.

1. Upgrade all the secondary server instances (server B and server C).

2. Obtain the tail of the transaction log of the primary database (on server A), and take the database offline, by backing up the transaction log using WITH NORECOVERY.

3. On the secondary server to which you plan to fail over (server B), bring the secondary database online, by restoring the log backup using WITH RECOVERY.

4. On every other secondary server (server C), leave the secondary database offline by restoring the log backup using WITH NORECOVERY.

   Notes

   The log shipping copy and restore jobs will run on the secondary servers, but the jobs will do nothing because new log-backup files will not be placed on the backup share.

5. Fail over the database by redirecting clients from the original primary server (server A) to the online secondary server (server B). The online database becomes an interim primary server, keeping the database available while the original primary server is offline (server A).

6. Upgrade the original primary server (server A).

7. On the database to which you failed over—the interim primary database (on server B), manually back up the transaction log using WITH NORECOVERY. This takes the database offline.

8. Restore all transaction log backups that you created on the interim primary database (on server B) to every other secondary database (on server C) using WITH NORECOVERY. This allows log shipping to continue from the original primary database after its upgrade, without requiring a full database restore on each secondary database.

9. Restore the transaction log from the interim primary server (server B) to the original primary database (on server A) using WITH RECOVERY.

Redeploying Log Shipping

If you do not want to migrate your log shipping configuration using one of the procedures shown above, you can redeploy log shipping from scratch by reinitializing your secondary database with a full backup and restore of the primary database. This may be a desirable option if you have a small database or if high availability is not crucial during the upgrade procedure.

For information about enabling log shipping using SQL Server Management Studio, see How to: Enable Log Shipping (SQL Server Management Studio).

For information about enabling log shipping using Transact-SQL, see How to: Enable Log Shipping (Transact-SQL).

See Also

Other Resources

Working with Transaction Log Backups

Log Shipping

Log Shipping Stored Procedures and Tables

Migrating a SQL Server 2000 Log Shipping Configuration to SQL Server 2008

Change History

Updated content
Updated the "Upgrading the Secondary server instance" section to state that the RESTORE WITH STANDBY option is not supported for a database that requires upgrading.