Upgrading to SQL Server 2005 Log Shipping

SQL Server 2000 log shipping cannot be directly upgraded to SQL Server 2005. The log-shipping procedures have changed; therefore, when upgrading a SQL Server 2000 instance, the log-shipping process stops functioning. There are three common approaches to upgrading log shipping.

Minimum Downtime Approach

The minimum downtime approach requires that the secondary server be upgraded in place using the setup program to SQL Server 2005 first. This will not break log shipping, because SQL Server 2000 transaction logs can be applied to SQL Server 2005. Afterward, a planned failover takes place where the roleswitch procedures are followed and users are redirected to the secondary server that has been promoted. After the role switch and after the users are redirected, stop and disable the log shipping jobs. The newly upgraded SQL Server 2005 primary server cannot log-ship to the old primary server running SQL Server 2000. Complete an in-place upgrade on the old primary server using the SQL Server 2005 setup program and leave it as the secondary server. Configure SQL Server 2005 log shipping from the new primary server to the secondary server using the same log-shipping shared folders used by SQL Server 2000. Additionally, in the Secondary Database Settings, on the Initialize Secondary Database page, choose "No, the Secondary database is initialized." SQL Server 2005 log shipping will then start shipping the transaction log. If you prefer, switch the roles back to the original primary server.

With Downtime Approach

With the downtime approach, the primary and secondary servers will not be available during the upgrade, as both are upgraded in place using the setup program. Use this method if you have allocated downtime to upgrade the SQL Server 2000 servers. Verify that the secondary server is in synch with the primary server by applying all the transaction-log backups and the active transaction log from the primary server. Stop and disable the log-shipping jobs; then do an in-place upgrade on the primary and secondary servers concurrently. Configure SQL Server 2005 log shipping using the same shared folders. Additionally, in the Secondary Database Settings, on the Initialize Secondary Database page, choose "No, the Secondary database is initialized." SQL Server 2005 log shipping will start shipping the transaction log without having to take a database backup and restore on the secondary server.

Deploy Log Shipping Approach

The deploy log shipping approach is more feasible when the log-shipping databases are small and can quickly be backed up, copied, and restored on the secondary server. It is similar to the with-downtime approach. But instead of verifying and waiting for the synchronizing of the secondary database, after the upgrade and during the SQL Server 2005 log-shipping configuration, you can choose "Yes, generate a full backup of the primary database" on the Initialize Secondary Database page. The log-shipping process will perform a database backup on the primary server and restore the backup onto the secondary server to start the log shipping.

Removing SQL Server 2000 Log-Shipping Tables and Jobs

After upgrading to SQL Server 2005 log shipping, the SQL Server 2000 log-shipping tables and SQL jobs can be deleted. The following SQL Server 2000 log-shipping tables can be deleted.

• log_shipping_databases

• log_shipping_monitor

• log_shipping_plan_databases

• log_shipping_plan_history

• log_shipping_plans

• log_shipping_primaries

• log_shipping_secondaries