Upgrading from SQL Server 7.0 to SQL Server 2000

The upgrade from SQL Server 7.0 to 2000 is much less of a change than upgrading from SQL Server 6.5. The data-storage structures are the same, so the databases can be quickly and easily converted. Most of the changes in SQL Server 2000 are additional features rather than changes to existing features. Microsoft laid the groundwork for these features in SQL Server 7.0, so compatibility concerns are minimal.

You can take any of three approaches to upgrading. First, you can simply upgrade in-place, replacing your SQL Server 7.0 installation with SQL Server 2000, so that all the databases are upgraded and functioning. Alternatively, you can install a named instance of SQL Server 2000 on the server and run it side by side with SQL Server 7.0. (Note that you can't run SQL Server 7.0 and the default instance of SQL Server 2000 at the same time on one computer.) Then, you can transfer the databases one at a time to the SQL Server 2000 instance. The third option is to install SQL Server 2000 on a new computer and move the databases over to the new computer. While you're upgrading, you can also change to a different edition of SQL Server. For example, you can change from SQL Server 7.0 Standard Edition to SQL Server 2000 Enterprise Edition.

The first upgrade option, the in-place upgrade, removes your SQL Server 7.0 installation, so make sure that you have backups of the databases and the SQL Server 7.0 installation before you begin the upgrade, just in case. The new instance of SQL Server 2000 is the default instance.

The second and third options let you keep SQL Server 7.0 up and running, with no risk of downtime for the production databases. You can then transfer the databases and users while retaining the SQL Server 7.0 versions as a fallback option. You can move the databases manually, using a detach/reattach strategy, but the Database Copy Wizard offers an even easier option. It builds the detach and reattach jobs as a Data Transformation Services (DTS) package. You can copy not only the databases but also the logins, users, and security information.

Note that to avoid losing data, you must disconnect the users from the SQL Server 7.0 database before you move it, then reconnect them to the SQL Server 2000 database when it's ready. From that point on, the SQL Server 7.0 database won't be updated unless you specifically set up replication or distributed transactions to keep it up-to-date. As always, careful planning is the key to upgrade success.

If you elect to use the upgrade method that lets you run a named instance of SQL Server 2000 alongside SQL Server 7.0, tools such as Enterprise Manager and Query Analyzer will be upgraded to the 8.0 (SQL Server 2000) versions. Your Start menu will show two program groups, one for SQL Server 2000 and one for SQL Server 7.0. Many entries in the SQL Server 7.0 group will be modified to point to the 8.0 tools. The Books Online (BOL) entry in the SQL Server 7.0 program group will continue to point to SQL Server 7.0 BOL.

If you have replication set up, you have to use the in-place upgrade. The alternative is to remove and reestablish your replication setup.

After the Upgrade

You still need to attend to a few housekeeping tasks after upgrading. First, refresh the statistics to make sure that the query optimizer has the best possible information for its decision making. BOL warns that using SQL Server 7.0 statistics with the SQL Server 2000 optimizer can result in poor performance. You can use the sp_updatestats stored procedure to refresh the statistics.

If you're using full-text indexing, be aware that the upgrade process marks the databases as full-text disabled because the format of the full-text indexes has changed. The upgrade process could have made the change automatically, but updating full-text indexes can be time-consuming. Rather than make the upgrade process excessively long, the programmers opted to let you rebuild your indexes after the database upgrade is complete.