Microsoft SQL Server 2000 High Availability

It is possible to manually configure log shipping from SQL Server 7.0 to SQL Server 2000. The best use of this is to facilitate an upgrade from SQL Server 7.0 to SQL Server 2000 where you need to minimize the amount of downtime in the server switch. There are some constraints for doing this, though, namely the following:

• SQL Server 7.0 must be configured with at least SQL Server 7.0 Service Pack 2.

• The pending upgrade option must be set on SQL Server 7.0. This is a database-level option that will only affect the database that it is configured on. Setting pending upgrade means that indexes cannot be created and statistics cannot be generated. Therefore, you should not allow log shipping to occur for a lengthy period of time, as it will affect performance of the SQL Server 7.0 database.

• Because recovery models are new to SQL Server 2000, truncate log on checkpoint and select into/bulk copy cannot be set as options on the SQL Server 7.0 databases.

• When restoring the database under SQL Server 2000, you must use NORECOVERY only. STANDBY is not supported when an upgrade is required.

• Replication cannot be configured between the server running SQL Server 7.0 and the SQL Server 2000 instance if log shipping is going to be used. If you are currently replicating using these servers, you have to script your configuration and unconfigure replication.

• As with custom log shipping, there is no monitoring functionality for log shipping between SQL Server 7.0 and SQL Server 2000. You have to code your own.

  Note	Transaction logs for SQL Server 2000 are not backward- compatible. Therefore, it is not possible to apply transaction logs (or full backups for that matter) generated on SQL Server 2000 to a SQL Server 7.0 installation.

Configuring Log Shipping from SQL Server 7.0 to SQL Server 2000

The following steps show how to configure log shipping between SQL Server 7.0 Service Pack 2 or later and SQL Server 2000.

1. Install and configure an instance of SQL Server 2000.

2. Perform full backups for all databases on SQL Server 7.0.

3. Restore the database that will be log shipped on your SQL Server 2000 instance using NORECOVERY. Here is example syntax:

   RESTORE DATABASE mydb FROM DISK = ' C:\mydbbackup.bak ' WITH NORECOVERY

   If requiring a new location:

   RESTORE DATABASE mydb

   FROM DISK = ' C:\mydbbackup.bak ' WITH NORECOVERY MOVE ' data file ' TO ' x:\newlocation\dbdatafile.mdf ', MOVE ' log file ' TO ' x:\newlocation\dblogfile.ldf '

4. Make sure that truncate log on checkpoint and select into/bulk copy are not selected as options for the database being log shipped from SQL Server 7.0.

5. Create a location for the transaction logs to be stored.

6. Execute the following command using Transact -SQL: sp_dboption ' database name ', ' pending upgrade ', ' TRUE ', where the database name is the name of the database that will be log shipped to SQL Server 2000.

7. Using the Database Maintenance Plan Wizard, back up your transaction logs on a regularly scheduled basis. Use the directory created.

   Warning

   If this is an actual SQL Server 7.0 to SQL Server 2000 upgrade and not just a way of creating a test database or something similar, stop all traffic and users from accessing the database at this point to ensure that if you have a problem, the database is in the state it was prior to the switch to SQL Server 2000. You will then have no data loss should you need to go back to SQL Server 7.0.

8. Manually apply each transaction log generated. The following is example syntax:

   RESTORE LOG mydb FROM mydb_log1 WITH NORECOVERY

9. When it is time to restore the final transaction log, the syntax is slightly different. You now bring the database online and make it available for use. Here is sample syntax:

   RESTORE LOG mydb FROM mydb_finallog WITH RECOVERY

10. Ensure that all users, objects, and other items not brought over as part of the transaction logs exist under SQL Server 2000.

11. If necessary, redirect any applications to the new SQL Server 2000 database. Test all applications against the new database on SQL Server 2000 to ensure that everything functions as it did under SQL Server 7.0. If this is an upgrade, do not allow end users to access the new server if it has not been verified .

12. If you need to use the SQL Server 7.0 installation again, disable the pending upgrade option with the following Transact-SQL statement: sp_dboption 'database name', 'pending upgrade', 'FALSE'