Dela via

  • Artikel

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

You cannot upgrade a SQL Server 2000 log shipping configuration to SQL Server 2008 R2. The Database Maintenance Plan Wizard, which was integral to log shipping in SQL Server 2000, is not used as part of the log shipping configuration beginning in SQL Server 2005. As a result, log shipping stops functioning when you upgrade a server to SQL Server 2008 R2.

You can migrate a SQL Server 2000 log shipping configuration. This topic describes the methods of doing this:

  • Migrating with failover allows you to maintain the availability of your database while you upgrade each server in the log shipping configuration. The secondary database does not need to be reinitialized.

  • Migrating without failover offers a simpler procedure that does not require you to fail over to your secondary server. If you follow this procedure, your database is unavailable while you are upgrading the primary server. The secondary database does not need to be reinitialized.

  • Redeploy log shipping completely, including reinitializing the secondary database from a full backup.

After you have migrated your log shipping configuration to SQL Server 2008 R2, you can delete the tables and SQL Server Agent jobs that were created by SQL Server 2000 log shipping.

Note

Backup compression was introduced in SQL Server 2008 Enterprise. A migrated 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).

Restrictions

After a SQL Server 2000 log shipping configuration is migrated to SQL Server 2008 R2, secondary databases cannot be put into standby mode.

Migrating with Failover

Migrating a log shipping configuration by using 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.

One secondary server and no monitor server

  1. Upgrade the secondary server instance, Server B, to SQL Server 2008 R2. 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 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

    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 R2 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 R2 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 R2. The log shipping database temporarily remains a SQL Server 2000 database because it is offline.

  4. On Server B, configure SQL Server 2008 R2 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 R2 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.

Migrating Without Failover

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

  1. Upgrade the secondary server instance to SQL Server 2008 R2. 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 R2. 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 R2.

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

Redeploying Log Shipping

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

Removing SQL Server 2000 Log Shipping Tables and Jobs

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