Migrating a SQL Server 2000 Log Shipping Configuration to SQL Server 2005
You cannot directly upgrade a SQL Server 2000 log shipping configuration to SQL Server 2005. 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 in SQL Server 2005. As a result, log shipping stops functioning when you upgrade a server to SQL Server 2005.
You can migrate a SQL Server 2000 log shipping configuration while maintaining synchronization between your primary and secondary databases. This topic describes two 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. This procedure requires that your SQL Server 2000 log shipping configuration be configured to allow failover.
- 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 log shipping database is unavailable while you are upgrading the primary server to SQL Server 2005.
Once you have completed the migration of your log shipping configuration to SQL Server 2005, you can delete the tables and SQL Server Agent jobs that were created by SQL Server 2000 log shipping.
Restrictions
After a SQL Server 2000 log shipping configuration is migrated to SQL Server 2005, secondary databases cannot be put into standby mode.
Migrating with Failover
To maintain high availability on your SQL Server 2000 log shipping configuration, you can use the failover capabilities available with log shipping to keep your database online.
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. Therefore, log shipping can continue to restore log backups from the primary database to the secondary database until a failover brings it online as the new primary database.
In order to successfully complete this procedure, you must have log shipping configured on SQL Server 2000, and your configuration must allow failover between the primary and secondary databases. 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.
Upgrade Server B to SQL Server 2005. 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.
Note
At this point, users can continue to access the primary database on Server A.
Fail over from Server A to Server B by applying all required transaction logs from the primary database on Server A and backing up the primary database with NORECOVERY. When you bring your secondary database on Server B online, it is automatically upgraded to a SQL Server 2005 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 2005 log shipping is configured on Server B, however, log backups from the database on Server B cannot be applied to the database on Server A.
Upgrade Server A to SQL Server 2005. The log shipping database remains a SQL Server 2000 database because it is offline.
On Server B, configure SQL Server 2005 log shipping with Server B as the primary server and Server A as the secondary server. When you begin shipping transaction logs to Server A, the log shipping database on Server A is upgraded to a SQL Server 2005 database when the first log backup is applied.
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).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 the need to use 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 2005.
Upgrade the secondary server instance to SQL Server 2005. When you upgrade the secondary server instance, the log shipping database remains a SQL Server 2000 database because it is in an offline state.
Upgrade the primary server to SQL Server 2005. The primary database is unavailable while the upgrade is in progress.
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 assures that all log backups are properly applied to the secondary database when you enable log shipping in SQL Server 2005.
Because the database upgrade process is a fully logged operation, your secondary database is upgraded to a SQL Server 2005 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 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).
Removing SQL Server 2000 Log Shipping Tables and Jobs
After you have deployed a new log shipping configuration, you can remove the SQL Server 2000 log shipping tables and jobs that may still be on your computer.
SQL Server 2005 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 2005:
- 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 by SQL Server 2000.
See Also
Concepts
Log Shipping Tables and Stored Procedures