The upgrade process involves upgrading the secondary server instances of a SQL Server 2005 log shipping configuration to SQL Server 2008 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.

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

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 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.

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.

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, and setting up log shipping on a SQL Server 2008 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.

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 AdventureWorks database on the primary server. The backup file is named Failover_AdventureWorks_20080315.trn:
   

   	Copy Code
   BACKUP LOG AdventureWorks TO DISK = N'\\FileServer\Log Shipping\AdventureWorks\Failover_AdventureWorks_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.
      The following example restores the tail log backup of the AdventureWorks database on the secondary database. The example uses the WITH RECOVERY option, which brings the database online:
      

      	Copy Code
      RESTORE LOG AdventureWorks FROM DISK = N'c:\log shipping\Failover_AdventureWorks_20080315.trn' WITH RECOVERY; GO

      Note:
      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.
      

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

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.
  

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

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.
   
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.