Everything you need to know about upgrading SQL Server
If you've been putting off upgrading your old SQL Server 6.5 databases, now is the time to bring them into the twenty-first century. You might as well skip SQL Server 7.0 because SQL Server 2000 is the officially supported, current release and has many additional features. The SQL Server 2000 Upgrade Wizard will upgrade from SQL Server 6.5 to 2000, so you can skip the upgrade to SQL Server 7.0. If you're already running SQL Server 7.0, the upgrade is much easier; see the sidebar "Upgrading from SQL Server 7.0 to 2000," page 26, for details.
Because of the new data-storage model and many new features in SQL Server 2000, upgrading from SQL Server 6.5 to 2000 is a little more complex than the usual version upgrade, but you'll find that life is simpler afterward. The more recent releases of SQL Server—2000 and 7.0—are much easier to administer. However, you won't have much free time on your hands because you can do more with the new releases now that the administrative chores are automated.
Because the differences between SQL Server 2000 and 6.5 are so significant, we strongly recommend that you install SQL Server 2000 Books Online (BOL) on a computer and read up on the new features. You can install BOL without installing the other software from the CD-ROM. You can even install BOL on a Windows 2000 Professional, Windows NT Workstation 4.0, or Windows 98 system. The standard and enterprise editions will give you an error message saying that they won't install on these OSs, but you can still install the client tools. Accept that option, then clear the check boxes for all the other client tools and install only BOL.
The upgrade process is straightforward. A simple upgrade will give you a database that works, but not necessarily a SQL Server 2000 database that's running at its full potential. To take full advantage of the new features of SQL Server 2000, you'll need to rethink some of your approaches to database management, especially regarding indexing, replication, and backups. For information about the differences in these areas and what you might need to change after the upgrade, see the Web-exclusive sidebar "New in 2000" (see "More on the Web," page 28, for instructions).
Get Ready
You can make a few changes to your SQL Server 6.5 installation and databases to help the upgrade process go more smoothly. You might even want to take this opportunity to upgrade your hardware and OS. To justify this expenditure, keep in mind that installing SQL Server 2000 on a new computer will let you run both versions of the database in parallel for a few days to ensure a smooth transition. You can run SQL Server 2000 and 6.5 on the same computer, but not at the same time, unless you install a named instance of SQL Server 2000. However, that complicates the upgrade process because the Upgrade Wizard requires a default instance, not a named instance, of SQL Server 2000. Realistically, if you're running SQL Server 6.5, you might need to upgrade the older server hardware just for SQL Server 2000. If you have two servers, you can also move databases one at a time, rather than upgrading them all at the same time. And if that isn't enough to convince your boss to let you add a server, later in this article, we suggest a great use for the older computer.
Before starting any upgrade, back up your databases. Also, run some database consistency checking utilities (DBCC commands) such as DBCC CHECKDB to make sure that you're not trying to upgrade a damaged or corrupted database. If you haven't already done so, we also recommend using the Generate SQL Script Wizard to generate SQL scripts containing all the objects for each database. In Enterprise Manager, select the database, then click Options, Generate SQL Scripts from the menu bar. Unlike SQL Server 2000, the script generator in SQL Server 6.5 only generates objects within the database. It can't generate the SQL code required to create the database, but you can always add that manually. Don't forget to generate the user and security settings.
SQL Server 2000 and 7.0 use a different approach to indexing data than SQL Server 6.5 does. As a result, some unsuspecting DBAs have seen their databases grow alarmingly following an upgrade. In SQL Server 6.5 and earlier, the traditional nonclustered indexes use physical row pointers to point to the actual data record. In SQL Server 2000 and 7.0, nonclustered indexes use the clustered index key to point to the data record if the table is clustered, as Figure 1, page 24, shows. Microsoft included this major architectural change to improve performance during data modifications. However, it can slow data retrieval, so you need to review which columns you're using for clustered indexes. If the key values are short, such as invoice number or customer number, you shouldn't see extraordinary database growth. However, if your clustered index is on a column or combination of columns that takes up 20 bytes or more, consider rethinking your indexing strategy now. Even if the nonclustered index key values are short and don't take up much space, each SQL Server 2000 index value is associated with a clustered index key that points to the data. Consequently, the total size of the index will now be much larger than in previous releases.
Because indexes are built so differently in SQL Server 2000 and 6.5, even if you don't redesign your indexing, we recommend that you drop all the indexes before the upgrade, then rebuild them after transferring the data to SQL Server 2000. Doing so will make the upgrade run faster. This task is easier if you have the SQL scripts from the Generate SQL Script Wizard to guide you about what to rebuild.