Migrating with failover involves keeping the primary server instance at the original version of SQL Server temporarily, while upgrading the secondary server instance. Upgrading a server instance upgrades only online databases. Offline databases, such as a log shipping secondary database, remain in the original version of SQL Server. As long as a database remains offline, log backups from the original version of SQL Server are restorable.

For purposes of clarity, this procedure refers to the SQL Server 2000 log shipping primary server instance as Server A and the SQL Server 2000 log shipping secondary server instance as Server B. This configuration is represented in the following illustration, which shows a primary server instance, A, and a single secondary server instance, B.

1. Upgrade the secondary server instance, Server B, to SQL Server 2008. When you upgrade Server B, the log shipping database remains a SQL Server 2000 database because it is offline. This database is upgraded in the next step. The SQL Server 2000 log shipping jobs that copy and restore log files will no longer function.
   

   Note:
   At this point, users can continue to access the primary database on Server A.

2. Fail over from the primary server instance, Server A, to the secondary server instance, Server B.
   
   1. To fail over, restore all unapplied transaction log backups from the primary database on server A to server B.
      
   2. Manually back up the tail 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.
      The following example backs up the tail of the log 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

   3. Bring the secondary database on server B online by restoring the transaction log tail WITH RECOVERY.
      
   The failover process brings your secondary database online, after which it is automatically upgraded to a SQL Server 2008 database. The database upgrade process is fully logged.
   

   Note:
   After being upgraded, the log shipping database on Server B is available to users. Until SQL Server 2008 log shipping is configured on Server B, however, log backups from the database on Server B are not being taken.

3. Upgrade Server A to SQL Server 2008. The log shipping database temporarily remains a SQL Server 2000 database because it is offline.
   
4. On Server B, configure SQL Server 2008 log shipping with Server B as the primary server and Server A as the secondary server. After you begin shipping transaction logs to Server A, the log shipping database on Server A is upgraded to a SQL Server 2008 database when the first log backup is applied.
   

   Important:
   When you configure log shipping on Server B, be sure to specify the No, the secondary database is initialized option on the Initialize Secondary Database tab of the Secondary Database Settings dialog box. For more information, see How to: Enable Log Shipping (SQL Server Management Studio).

5. Optionally, if you want to make Server A your primary server again, fail over to Server A. For more information, see Changing Roles Between Primary and Secondary Servers.
   

You can migrate your SQL Server 2000 log shipping configuration to SQL Server 2005 without using failover. This procedure allows you to easily upgrade both server instances in your log shipping configuration; however, your primary database is unavailable while you are upgrading the primary server instance to SQL Server 2008.

1. Upgrade the secondary server instance to SQL Server 2008. When you upgrade the secondary server instance, the log shipping database remains a SQL Server 2000 database because it is in an offline state. The SQL Server 2000 log shipping jobs that copy and restore log files will no longer function.
   
2. Upgrade the primary server to SQL Server 2008. The primary database is unavailable while the upgrade is in progress.
   
3. Configure log shipping from the primary server instance to the secondary server instance. Be sure to specify the No, the secondary database is initialized option on the Initialize Secondary database tab of the Secondary Database Settings dialog box. For more information, see How to: Enable Log Shipping (SQL Server Management Studio).
   

   Important:
   Specify the same backup share that you used with your SQL Server 2000 log shipping configuration. This ensures that all log backups are properly applied to the secondary database when you enable log shipping in SQL Server 2008.

   Because the database upgrade process is a fully logged operation, your secondary database is upgraded to a SQL Server 2008 database when you start shipping logs to the secondary server instance.
   

If you do not want to migrate your log shipping configuration, 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.

To enable log shipping

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

SQL Server 2008 does not use any of the log shipping tables used by SQL Server 2000. You can therefore delete these tables after you upgrade your server to SQL Server 2008. These tables are as follows:

• log_shipping_databases
  
• log_shipping_monitor
  
• log_shipping_plan_databases
  
• log_shipping_plan_history
  
• log_shipping_plans
  
• log_shipping_primaries
  
• log_shipping_secondaries
  

You can also delete any log shipping SQL Server Agent jobs that were created for the log shipping configuration by SQL Server 2000.

Concepts

Other Resources