Move Up to SQL Server 2000

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.

If you have replication set up in your SQL Server 6.5 environment, the upgrade process can move everything—replication included—to SQL Server 2000, provided that you're upgrading in-place on the same computer. If you're moving the databases to a new computer, however, you'll have to break replication on SQL Server 6.5 and set it up again on SQL Server 2000. However, that isn't totally bad news because SQL Server 2000 contains some significant changes in replication. For example, in a SQL Server 2000 pull subscription, the workload is greater on the subscriber because that's where the Distribution Agent now runs. If you have multiple subscriptions in SQL Server 6.5, the Distribution Server has to manage them all. That's still true in SQL Server 2000 for push subscriptions, but for pull subscriptions, the subscriber gets to do the work. Additional options in replication include merge replication, which offers two-way update capability. In SQL Server 2000, subscribing to individual articles is discouraged for data-integrity reasons; you should subscribe to the entire publication. Because of SQL Server 2000's many new replication features, now is a good time to drop your existing replication scenarios and rebuild replication to fully exploit the additional functionality SQL Server 2000 offers. So before you start the upgrade process, cancel the subscribers, publications, and publishers, and remove the replication jobs from the SQL Server 6.5 database.

The SQL Server 2000 Upgrade Wizard, which walks you through the upgrade process, requires that you have a pagefile of at least the amount of RAM in your computer plus 16MB. This limitation seems unnecessary if you bought a system loaded with RAM thinking that with RAM to spare, you wouldn't need a large pagefile. But the Upgrade Wizard won't run until you make the pagefile as large as it wants. You can always reset the pagefile after the upgrade.

Your SQL Server 6.5 installation must have a service pack applied. If you're upgrading in-place on the same computer, you must have Service Pack 5 (SP5) applied to SQL Server 6.5. If you plan to upgrade by moving the databases to a SQL Server 2000 installation on a new computer, the 6.5 installation needs at least SP3. You'll get an error message and the upgrade will terminate if you don't have the correct service pack. The easiest way to determine whether you have the right service pack applied is to open the Query Analyzer (ISQL/w in SQL Server 6.5) and run

SELECT @@version

The original release of SQL Server 6.5 was version 6.50.201. SP3 upgraded this to 6.50.258. SP5 takes the version to 6.50.415, and SP5a to 6.50.416.

Get Set

You need to take care of some more housekeeping details before you get started on the upgrade process. On the SQL Server 6.5 server, make sure that the tempdb database is at least 10MB. (Microsoft recommends 25MB.) In reality, tempdb should already be larger than 25MB; if it isn't, make it 50MB or 100MB if you have the disk space.

The master database needs at least 3MB of free disk space. If you even suspect it doesn't have that much free space, add 10MB. You're going to delete it soon anyway, and this might be the last time you have to add space to a device and then to the database.

If you've set any stored procedures to run automatically at startup, disable them now. You can use the sp_unmakestartup stored procedure to prevent these procedures from running. The upgrade process starts and stops the SQL Server services several times, and you don't want any procedures running while the upgrade is taking place. To find out which procedures run at startup, use sp_helpstartup.

BOL says to "ensure the @@SERVERNAME is defined on SQL Server 2000." We found that you need to make sure that @@SERVERNAME is defined—that is, not NULL—on both the SQL Server 2000 and the 6.5 installations. To check whether @@SERVERNAME is defined, open Query Analyzer (in SQL Server 2000) or ISQL/w (in SQL Server 6.5), and run

SELECT @@servername

If the result is NULL, you need to run

EXEC sp_addserver server_name, local

where server_name is the name of your server. Again, you'll have to stop and restart the SQL Server service for this change to take effect.

Before you start the upgrade, check to make sure that you have enough free disk space. Microsoft recommends that you have free space equal to at least 1.5 times the space occupied by the SQL Server 6.5 databases. You also need to make sure that you have up to 200MB free on your system drive—that's the drive on which your Program Files directory is located. The installation of SQL Server 2000 uses far more space on the system drive than it does on the drive on which you want to install SQL Server. (Note that this behavior is undocumented.) You can check the location of the system drive in the registry under the HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindowsCurrentVersionProgramFilesDir subkey. You can also check the ProgramFiles environment variable in Win2K. For many reasons, including ease of backups, ease of upgrading the OS, and performance, we believe that mixing OSs and applications on the same disk is never a good idea. This lack of choice is one of the few examples of poor programming in SQL Server 2000.

You also need to have Microsoft Internet Explorer (IE) version 5.00.2314.0 or above installed on your server before you can install SQL Server 2000. If you're upgrading an older NT box that's been running SQL Server 6.5 for a couple of years, you might need to install IE 5.0. Remember, the version of IE on the original NT 4.0 CD-ROM was 2.0.

If you're upgrading to a second computer, make sure that the SQL Server service accounts are set up as domain accounts, not as the Local System account. The Local System account can't make the network connections that you need for the database transfer.

Go!

Finally, you're ready to begin the upgrade process. You start by installing SQL Server 2000; the program starts by searching for existing SQL Server 7.0 or 6.5 installations. When it detects one, it changes the install to an upgrade. During this process, you have the option of changing the default settings to either Windows-based collation or to SQL collation. Change the Windows collation from the default only if you have to match the collation settings for another instance of SQL Server or the Windows locale of another computer. The SQL collation setting exists for compatibility with earlier versions of SQL Server. However, you might want to stay with the default settings on install because in SQL Server 2000 you can change each database's collation for backward compatibility if you need to.

As part of an upgrade-in-place, the SQL Server 2000 installation automatically stops the SQL Server 6.5 services, installs SQL Server 2000, builds a new program group—Microsoft SQL Server — Switch—and starts the instance of SQL Server 2000. The new Switch program group gives you the option of switching back to running SQL Server 6.5 on this computer, uninstalling SQL Server 6.5, or running the SQL Server Upgrade Wizard. Note that if you encounter a problem and the Upgrade Wizard fails to complete successfully, the Switch utility won't function properly. Both the SQL Server 2000 and 6.5 program groups might appear in the Program Manager, although entries in the SQL Server 2000 program group might not be fully functional. But if you cycle between the two versions a few times, the SQL Server 2000 program group will disappear.

If you don't want to use the Upgrade Wizard, you can use Data Transformation Services (DTS) in SQL Server 2000 to set up tasks to transfer the databases from SQL Server 6.5 to 2000. You'll have to install the two versions on different computers or use a named instance of SQL Server 2000, because you must have both versions running at the same time for DTS to work.

Note that the upgrade-in-place doesn't automatically upgrade your user databases. You have to use the Upgrade Wizard to upgrade the user databases. Using the wizard after performing the upgrade-in-place means that the upgrade process runs faster and you can upgrade the databases one at a time. Because you have to choose between running either SQL Server 2000 or 6.5 (you can't run them both on the same computer at the same time), you'll want to convert all the databases promptly. The Upgrade Wizard recommends upgrading all your databases in one operation. If you have any cross-database references, doing a simultaneous upgrade is essential. If you have no cross-database references, you have more flexibility in how to prioritize the upgrading of your databases.

The Upgrade Wizard requires SQL Server authentication, but the default for SQL Server 2000 is to use Windows NT Only authentication. You can change this setting during the SQL Server 2000 installation or in Enterprise Manager before starting the Upgrade Wizard. In Enterprise Manager, select the server, right-click, and select Properties to open the tabbed dialog box. Select the Security tab, then select the SQL Server and Windows security option. When you click OK to close this dialog box, SQL Server offers to stop and restart the SQL Server service. The change in security mode doesn't take effect until the service is restarted, so accept the restart. Be aware that if you install SQL Server 2000 with NT security and later change the security mode, your SQL Server will have a blank password for the sa account. To change the password after changing the security mode, open the Security settings from Enterprise Manager and select Logins. Double-click sa in the logins list and type in a password.

The Upgrade Wizard starts and stops the SQL Server services during the upgrade process, so make sure that no users are connected to the database. Also make sure to shut down other applications such as Enterprise Manager and Query Analyzer. Don't attempt to bring up any SQL Server interfaces such as Enterprise Manager during the upgrade. If you do, the upgrade process is likely to hang. If you need to verify some information, cancel out of the Upgrade Wizard, cycle the version switch utility to reset all the services, get the information, then restart the Upgrade Wizard.

As you start the Upgrade Wizard and advance beyond the welcome screen, you'll have to specify the data-transfer method, as Figure 2, page 28, shows. Typically, you'd use Named Pipes with a local connection if you're performing an in-place upgrade or Named Pipes with a network connection if you elect to do a server-to-server upgrade. If you upgrade in-place, you have to make sure that you have enough disk space to add the new databases on the server. That's one reason for upgrading one database at a time—you can reclaim the space after you upgrade each database. If you're upgrading in-place and you don't have enough room on the computer for two installations of SQL Server plus two sets of databases, you need to transfer data by tape. After you back up the data to tape, the Upgrade Wizard deletes all the SQL Server 6.5 devices, so you must include all the databases you plan to upgrade in this backup operation.

After the upgrade, the Upgrade Wizard verifies that the database objects have been transferred successfully. It can also perform an exhaustive data-integrity verification by using a checksum on each column of each table, thus ensuring that no values changed during the upgrade.

Regardless of whether you're upgrading on one computer or two, you need to supply the logon information for both SQL Server 2000 and 6.5, as Figure 3, page 28, shows. Note that this screen doesn't let you choose the Import Server because the Import Server is the computer on which you're running the Upgrade Wizard.

The default for upgrading databases is to upgrade them all at once, as Figure 4, page 28, shows. You can exclude databases if you want to upgrade them one at a time or if you're upgrading in groups. The Upgrade Wizard can help you decide where to put the new databases. SQL Server 2000 lets you specify default locations for both data and log files. The usual practice is to place data and logs on different physical hard disks. As you convert your databases, you can change file locations, change filenames, or even add files to spread a database across more than one disk. To change file locations, select the Edit option on the Database Creation screen, which Figure 5, page 28, shows. Selecting the Advanced option from the next screen that opens displays a list of files, disk drives, available space, and other information, as Figure 6 shows. If the SQL Server 6.5 database you're upgrading was located on multiple devices, you'll see multiple files in this list for your new database. The Upgrade Wizard will place most of the data into the first file. You might want to take this opportunity to get rid of any extra file space that you don't need. You'll have an easier time doing file maintenance now rather than when the upgrade is complete and the files contain data.

Note that when you're upgrading your SQL Server 6.5 database, you can also upgrade into a SQL Server 2000 database that you created previously. If you select this option, which Figure 5 shows, a pop-up box tells you that this option isn't recommended. The databases need to have the same name, and the previously created database needs to be sized correctly to hold the incoming data. The same conditions are true if you run a script to build the databases—the previous and new database names must match and the sizes must be appropriate for the requirements of the data set.

As part of the Upgrade Wizard process, you can move your server configuration, any replication settings (if you didn't drop them during preparation), and the SQL Executive settings, as Figure 7 shows. The server configuration includes logons, remote logon registrations, and any user-configurable options that carry over to SQL Server 2000. Many server settings don't carry over to or are automated in SQL Server 2000; the wizard ignores these settings and doesn't transfer them. If you elect to upgrade replication, rather than reestablish it, the Upgrade Wizard converts your replication setup to the SQL Server 2000 installation. The SQL Executive upgrade operation transfers and upgrades all scheduled tasks, but it doesn't reconfigure scheduled tasks to take advantage of any new features such as job flow or multiserver tasks.

If you're upgrading one database at a time, when you upgrade a second (and a third, and a fourth) database, you still have the option to transfer the server configuration, but this time the option isn't selected by default. You don't have the option of transferring the replication settings or the SQL Executive settings on the second and subsequent upgrade operations. If you want to transfer the replication settings from SQL Server 6.5 to 2000, make sure that you upgrade all the databases involved in replication on the first upgrade pass. Also, the Upgrade Wizard will discourage you from putting off upgrading the model database until a subsequent pass. Upgrade the model database in the first pass or don't upgrade it at all. Unless you've changed the SQL Server 6.5 model database, such as adding some user-defined data types that you want to retain in SQL Server 2000, you don't really need to upgrade the model database.

The Upgrade Wizard also lets you change the ANSI Nulls and Quoted Identifiers settings. The important point to remember is that the new database's ANSI Nulls option setting doesn't change the NULL/NOT NULL setting of the columns in the upgraded database. The NULL/NOT NULL property of the upgraded columns is determined by the ANSI Nulls settings in the SQL Server 6.5 database so as not to disrupt any existing functions, procedures, or tasks.

The upgrade is a little more complex for Quoted Identifiers. If you're certain that all the objects in the SQL Server 6.5 database were created in the same way, either with Quoted Identifiers on or off, select that same setting when upgrading the database. If you're not sure of the Quoted Identifiers setting or if the SQL Server 6.5 objects were created with varying settings, select the Mixed (or don't know) option, as Figure 7 shows. If you choose this option, the Upgrade Wizard looks for objects with double quotes, then converts them with Quoted Identifiers set to ON. Then, it converts the remaining objects and sets their Quoted Identifiers to OFF.

As a final step, the Upgrade Wizard generates a summary of the choices you made, with warnings if appropriate. You can scroll through the summary as presented, or you can view it in Notepad. The Notepad option is nice because you can view the summary report, then save it to a file for future reference. This screen contains the odd instruction Click Finish to begin your upgrade. As the upgrade progresses, it adds a line for each step to a status screen (as Figure 8 shows), so that you can follow the progress of the upgrade. A pop-up window lets you know when the upgrade is finished. You can then turn your attention to making the databases fully SQL Server 2000—compliant. You can even start taking advantage of the many new features in SQL Server 2000. For more information about what to do next, see the Web-exclusive sidebar "Before and After."

Double Parking

Now, here's a suggestion for that old server if you're upgrading to a new server. First, remove all the databases from the old server after moving them to the new server. Be sure you won't need the old SQL Server 6.5 files anymore. Then, upgrade the old server to SQL Server 2000 also. Identify the databases and tables that are most heavily used for reporting and those that analysts and managers use for browsing the data in decision-support mode. Now replicate those databases, whole or in part, as appropriate, to the old server. Redirect the users who query but don't update the databases to the replicated databases on the old server. Because these users are just querying the data, their queries can use the WITH NOLOCK option, so their queries will run faster and won't be slowed down by any update operations (except for replication, which happens quickly and only at intervals). You can experiment with setting the transaction isolation level to read uncommitted for even higher levels of concurrency. And you can build different indexes on the two servers. Data entry people want few but efficient indexes to assist in update operations. Data analysts want to index everything to speed up their complex queries. You can resolve this classic conflict and look like a hero by giving each group a separate copy of the database. Keep in mind that you need the appropriate licenses for SQL Server 2000 and the client access licenses (CALs), so the cost might outweigh the benefits. If so, you can always use the old computer as an MP3 file server instead.