Chapter 17 - Upgrading to SQL Server 7.0

Microsoft SQL Server version 7.0 offers many new features and product improvements, and it contains significant internal architectural developments. These developments provide an architectural foundation for now and years to come. One key aspect of the new architecture is an on-disk format for all data. The new format supports improvements such as 8-kilobyte (KB) pages, bitmaps to keep track of tables (instead of doubly-linked lists), and index enhancements that result in a faster, more reliable, and more scalable product.

This chapter contains the information you need to prepare for the upgrade process, and includes these topics:

• Preparing for an upgrade. 

• Using the SQL Server Upgrade Wizard. 

• Understanding backward compatibility after you have upgraded. 

• Learning about Microsoft's upgrade experiences with SQL Server 7.0. 

Overview for Upgrading SQL Server

Because SQL Server 7.0 introduces a new on-disk format, the upgrade is different from earlier upgrades (for example, from SQL Server version 6.0 to SQL Server version 6.5). However, the SQL Server Upgrade Wizard makes upgrading from SQL Server 6.0 or SQL Server 6.5 to SQL Server 7.0 a simple operation.

The SQL Server Upgrade Wizard is designed with a typical user in mind. Usually, most users can go through the SQL Server Upgrade Wizard, accept the default values and options, and complete a successful upgrade to SQL Server 7.0. Even so, you should prepare for and understand the upgrade process and what the SQL Server Upgrade Wizard can do for you.

The SQL Server Upgrade is built on some fundamental assumptions. The wizard is designed to upgrade all the databases in a single pass. The purpose of the SQL Server Upgrade Wizard is to upgrade a single instance of SQL Server 6.x to a single instance of SQL Server 7.0 in a single pass. You can run multiple upgrades (for example, one database at a time) as long as they use the same SQL Server 6.x installation; however, this is not recommended because cross-database dependencies cannot be resolved if you upgrade only one database at a time. You cannot run SQL Server 6.x and SQL Server 7.0 on one computer simultaneously.

After you upgrade to SQL Server 7.0, there is no correlation, data mapping, or data sharing with an earlier installation of SQL Server 6.x. If you choose not to delete the SQL Server 6.x data files, you have two full copies of the data on disk: one for the SQL Server 6.x installation and one for the SQL Server 7.0 installation. When you are satisfied with the upgrade, use the custom uninstaller for SQL Server 6.x on the Microsoft SQL Server–Switch menu to delete the SQL Server 6.x data files. You may also want to plan a test-run of an upgrade to determine if there is any reason to prepare a fallback plan.

Prerequisites for Upgrading

You should make some key decisions before you begin upgrading to SQL Server 7.0. A little planning can go a long way to prevent redoing difficult and time-consuming work. You must consider issues such as:

• Is the present version of SQL Server supported for upgrade to SQL Server 7.0? 

• Where will SQL Server 7.0 be installed? 

• Does the SQL Server 6.x installation have the required software? 

• Is there enough disk space available for the upgrade? 

Examining each area in detail can help you understand the upgrade requirements and plan a successful upgrade.

SQL Server 7.0 is the first release of SQL Server to run on the Windows 95 and Windows 98 platforms. However, the Windows 95 and Windows 98 operating-system installations of SQL Server do not have the SQL Server Upgrade Wizard, because upgrades to the Windows 95 and Windows 98 operating systems are not supported. Only Microsoft Windows NT to Windows NT upgrades are supported.

Versions Supported for Upgrade

The SQL Server Upgrade Wizard supports upgrading from SQL Server 6.0 and SQL Server 6.5. Before upgrading from SQL Server 6.0, Service Pack 3 for SQL Server 6.0 must be installed. Before upgrading from SQL Server 6.5, Service Pack 3 for SQL Server 6.5 (or a later service pack) must be installed. If you are currently running SQL Server version 4.21a (or earlier), you must upgrade to SQL Server 6.0 or 6.5 first, and then upgrade to SQL Server 7.0. It is recommended that you upgrade to SQL Server 6.5 directly and then upgrade to SQL Server 7.0.

SQL Server 6.0 requires Microsoft Windows NT version 3.5 or later. SQL Server 6.5 requires Windows NT 3.51 or later. SQL Server 7.0 requires Windows NT version 4.0 with Service Pack 4 or later. If you are planning to use the same computer for SQL Server 7.0, you must first upgrade the computer to Windows NT 4.0, apply Service Pack 4 for Windows NT, install SQL Server 7.0, and then begin the upgrade process.

Microsoft supports SQL Server 6.0 on Windows NT 4.0 only during the process of a one-computer upgrade to SQL Server 7.0. If you are running SQL Server 6.0 on Windows NT 3.5, you must first upgrade to Windows NT 3.51 (Service Pack 5 is recommended) for a two-computer upgrade, or to Windows NT 4.0 for a one-computer upgrade before you begin the upgrade process.

Where to Install SQL Server 7.0

SQL Server 7.0 requires at minimum a Pentium 166 with 32 MB of random access memory (RAM). If your SQL Server 6.0 or SQL Server 6.5 installation is on a computer that meets these minimum hardware requirements, you might consider a one-computer upgrade. You must also consider the disk space requirements for an upgrade; otherwise, you should consider a two-computer upgrade.

One-computer Upgrades

If you want to perform a one-computer upgrade, you must install SQL Server 7.0 on the same computer that is currently running SQL Server 6.x. The computer may require some operating-system upgrades. SQL Server 7.0 requires:

• Windows NT 4.0 

• Windows NT 4.0 with Service Pack 4 (or later) 

• Microsoft Internet Explorer version 4.01 with Service Pack 1 (or later) 

You must complete these upgrades before you begin installing SQL Server 7.0. Because you will likely have to reboot your computer after installing each of these products, you should schedule time to prevent production users from accessing the SQL Server 6.x installation.

When the minimum operating-system requirements have been met, you can set up SQL Server 7.0. The SQL Server 6.x installation is not available during the installation of SQL Server 7.0. SQL Server 7.0 requires varying amounts of disk space, depending upon the components you choose to install; however, a typical installation requires about 170 megabytes (MB) of hard disk space. You should have free space equal to at least 1.5 times the amount of space that is used by the SQL Server 6.x data files. By default, the SQL Server 6.x data files are located in the \Sql60\Data or \Mssql\Data directories (for SQL Server 6.0 and 6.5, respectively). This free space is in addition to the space needed for the installation of SQL Server 7.0. Furthermore, you should have 1 MB of free space for files and logs on the hard drive on which SQL Server is installed for each gigabyte (GB) of SQL Server 6.x devices you plan to upgrade.

For example, if you intend to upgrade a 10-GB SQL Server 6.5 installation installed on C:\mssql, you need approximately 10 MB of free space on drive C for temporary files and logs, and 15 GB of free disk space available on the computer for use during the upgrade. Some of the 15 GB of disk space is used by the upgrade process for temporary storage, and the rest is used to store the SQL Server 7.0 data files. Most Microsoft databases shrink in size when they are upgraded; therefore, the data files may take less than 10 GB of space after they have been upgraded to SQL Server 7.0.

If the required space is not available on the hard disks, but you want to perform a one-computer upgrade, you must use the Tape option in the SQL Server Upgrade Wizard. The Tape option requires that the tape drive be installed on the local computer. During the upgrade process, you can specify that the SQL Server 6.x devices be deleted. You must delete the SQL Server 6.x device files to create the disk space for the SQL Server 7.0 data files. The speed of an upgrade using the Tape option is limited by the tape media and may be substantially slower.

Two-computer Upgrades

If you choose a two-computer upgrade to SQL Server 7.0, you must meet the same requirements for a one-computer upgrade (Windows NT 4.0 with Service Pack 4 or later, and Internet Explorer 4.01 with Service Pack 1 or later). In a two-computer upgrade, the SQL Server 7.0 server requires at least 1.5 times the free space used by the SQL Server 6.x data files. The same temporary work space (1 MB for each 1 GB of data upgraded) is required on the hard drive on which SQL Server 7.0 is installed.

The SQL Server 7.0 server, or the import server, must be in the same administrative domain (or at least in the same domain structure) as the SQL Server 6.x server, or the export server. Therefore, both servers must have a common trusted domain. The system administrator performing the upgrade must be a Windows NT local administrator on both computers. The MSSQLServer service account on the import server must be running with a domain user account from a common trusted domain, and the user account must be a local administrator on both computers. The MSSQLServer service account must not be running using the Windows NT LocalSystem account on the import server.

This illustration shows a multiple domain upgrade scenario.

 

If the export server is in Resource Domain A but the import server is in Resource Domain B, the MSSQLServer service on the import server must use a service account that is in a common trusted domain (such as the Master Account Domain in the illustration) and must be a member of the Windows NT Administrators local group on both the export and import servers. (The service account used on the export server does not affect the upgrade.)

This is most easily accomplished if both the export and import computers are installed as member computers in the same domain and use the same service account for the MSSQLServer service.

Preparing to Upgrade

Whether you decide to perform a one-computer upgrade (installing SQL Server 7.0 on existing hardware), or a two-computer upgrade (installing SQL Server 7.0 on a new computer), the information in the rest of this chapter relates to these upgrades similarly. A one-computer upgrade is simply a special case of a two-computer upgrade for which the import and export computers are the same physical computer.

The first step is to obtain the system administrator user password for both the export and import servers. Your Windows NT logon must also be a member of the Windows NT Administrators local group on both the export and import servers. You must leave the SQL Server 7.0 installation in SQL Server and Windows NT Authentication Mode for the duration of the upgrade. If you want to run in Windows NT–only mode (Integrated Security mode in SQL Server 6.x), switch to this mode after the upgrade is complete.

If you use SQL Server 6.x Integrated Security logins that are mapped to any Windows NT local groups, those groups must exist on the import server with the same group and user membership that exists on the export server.

Prepare the SQL Server 6.x Installation

It is essential that you verify the SQL Server 6.x installation before you begin the upgrade. The SQL Server 6*.x* (export) installation must meet the following requirements or the SQL Server Upgrade Wizard will not allow you to continue:

• The tempdb database must be at least 10 MB. 

  The tempdb database must be at least 10 MB to support queries that SQL Server Upgrade Wizard runs against the SQL Server 6*.x* installation. If the tempdb database is not at least 10 MB, the upgrade will not continue. By default, the tempdb database in SQL Server 6.0 and SQL Server 6.5 is 2 MB; therefore, you may need to change the size of the database. Use exec sp_helpdb tempdb or SQL Server Enterprise Manager to verify the size of the tempdb database.

• The @@SERVERNAME must not be NULL. 

  The SQL Server Upgrade Wizard uses the @@SERVERNAME function during the upgrade process. It is recommended that @@SERVERNAME be set to the same name as the computer name during the upgrade. If you run SELECT @@SERVERNAME and receive NULL, run exec sp_addserver 'computername', 'local' to reset the @@SERVERNAME function. For example, if the computer name is SQLPROD1, run exec sp_addserver 'SQLPROD1', 'local'. 

• The master database must have 3 MB of free space. 

  When you run the SQL Server Upgrade Wizard, a Transact-SQL script is run against the export server to upgrade the system tables and stored procedures in the master database for SQL Distributed Management Objects (SQL-DMO) and other connectivity components. These changes are fully backward compatible, but allow later versions of Open Database Connectivity (ODBC) and SQL-DMO to use the SQL Server 6.x installation. To verify that you have 3 MB of free space, run the following script or use SQL Server Enterprise Manager: 

  Use master
  

Go exec sp_spaceused @updateusage = 'TRUE'

Verify that unallocated space is at least 3 MB (or 3072 KB). 

• The text for objects must be intact in the syscomments system table. 

  If you create a nontable object such as a trigger, view, or stored procedure, the text of the CREATE statement is stored in the syscomments system table. If a system administrator has deleted the text for an object, that object is not upgraded.

  The sp_rename system stored procedure does not change an object's name in syscomments. 

  If you use the sp_rename system stored procedure to rename an object, such as a view or stored procedure, the text of the original CREATE statement for that object is not modified. Therefore, when the object is migrated to SQL Server 7.0, it retains the name that it had before the rename. 

• Logins must exist for every database user. 

  If you have moved a database or restored a database to the SQL Server 6.x installation, you must verify that a login exists and is mapped properly for each user. You can do this manually in SQL Server 6.0 or use the sp_change_users_login stored procedure in SQL Server 6.5. If you run the stored procedure with no parameters in either database, it reports the users who do not have a correct login. You must correct login-to-user mappings before you begin an upgrade. 

Note Stored procedures that modify system tables are not upgraded. Permissions that had no effect, such as EXECUTE on a table, are not upgraded.

Back Up the SQL Server 6.x Installation

Before upgrading the databases, you should check them for errors that can prevent a successful migration. It is recommended that you run database integrity checks against all of the databases with the DBCC CHECKDB, DBCC CHECKCATALOG, DBCC NEWALLOC, and DBCC TEXTALL commands. In the event that database inconsistencies are found, you must repair the database or restore to a clean one before you begin the upgrade process. You should then back up the SQL Server 6.x installation, including the master, model, and msdb databases, as well as all of the user-defined databases. Also, you may want to back up the actual data and log files. Perform any repair or restoration work after all production activity has been stopped for the upgrade, and the DBCC consistency checks have completed successfully.

Replication Considerations

SQL Server 7.0 can perform transactional replication to SQL Server 6.5 installations. You cannot take advantage of new replication features until all servers involved in the replication (such as Publisher, Distributor, and Subscriber) have been upgraded to SQL Server 7.0. If you have enabled replication, you do not have to disable it to perform an upgrade to SQL Server 7.0.

Replication can be upgraded only if you are upgrading from SQL Server 6.5 and then only with a one-computer upgrade. Otherwise, you must upgrade the databases, and then manually reestablish replication with SQL Server 7.0.

You upgrade the Distributor server first. Before you begin the upgrade from SQL Server 6.5, ensure:

• No new Subscribers have been added that have not completed initial synchronization. 

• No updates occur to the Publisher server(s) that correspond to the Distributor server you are about to upgrade. 

• All transactions have been copied from the transaction log of the Publisher to the distribution database by running the log reader task for the production server. 

• All transactions have been distributed to the Subscribers before you begin the upgrade of the SQL Server containing the distribution database, by running the distribution task. 

To verify there are no undistributed transactions, run exec sp_repltrans against the Publisher database. If activity has stopped, no rows are returned. Then, run exec sp_MSDistribution_counter @publisher = 'publisher_servername' on the distribution database for each supported Publisher. The no rows returned message should have 0 in the undelivered_jobs column.

Install SQL Server 7.0

When you install SQL Server 7.0, use the same sort order and character set that is used in SQL Server 6.x.* *(SQL Server Setup chooses this by default if it can connect to the SQL Server 6.x server at the beginning of setup for a one-computer upgrade). If you do not know the character set and sort order used for the SQL Server 6.x installation, run the sp_helpsort system stored procedure against the SQL Server 6.x server.

If you choose a one-computer upgrade, you can start the SQL Server Upgrade Wizard automatically at the end of the installation. Otherwise, you can start the SQL Server Upgrade Wizard manually anytime after the Setup program is complete. If you plan to perform a two-computer upgrade, you cannot use the Windows NT LocalSystem account for the SQL Server services.

Starting the SQL Server Upgrade Wizard

The SQL Server Upgrade Wizard is a fast, reliable way to upgrade the databases. The wizard is easy to use and understand, but still allows flexibility. Microsoft's extensive testing has shown that simply taking the default path through the wizard results in a successful upgrade for a majority of installations.

You can estimate how long the upgrade will take based on the approximate size of the SQL Server 6.x data devices:

• 1 GB: 1 hour 

• 10 GB: less than 4 hours 

• 50 GB: less than 12 hours 

• 100 GB: less than 24 hours 

These numbers can vary depending upon the hardware and the database schema (the number of tables, for example, to be upgraded). Double these times if you want to perform an upgrade using the tape data transfer option.

After you complete the preparatory work, start the SQL Server Upgrade Wizard. You can start the wizard from SQL Server Setup, or on the Start menu, point to Programs and Microsoft SQL Server – Switch. (Upgrading is supported only on Windows NT–based installations of SQL Server.)

When the wizard begins, the welcome screen warns that both the SQL Server 6.x and SQL Server 7.0 installations (the MSSQLServer service(s)) will be stopped and restarted several times during the upgrade process. Therefore, users cannot use any instance of SQL Server while the upgrade process is running.

Click Next to view the Data and Object Transfer dialog box.

The Data and Object Transfer dialog box contains key choices that affect what you see as you continue with the upgrade wizard. As for most dialog boxes in the wizard, it is recommended that you accept the defaults. However, you should be aware of the options:

• The Export from 6. x Server option allows you to export the objects and data from the SQL Server 6.x installation. If you upgrade using the tape data transfer method, you may choose to upgrade in two steps: an export only to tape, followed by an import only to the SQL Server 7.0 installation after you increase the free disk space. For a named pipe data transfer, this option is always selected. It is recommended that you select this option and, whenever possible, upgrade with a single pass through the wizard that uses the Named Pipe data transfer option. 

• The Import into 7.0 Server option can be cleared only for a tape data transfer method. If you use the upgrade scenario of multiple passes through the SQL Server Upgrade Wizard, check or clear this option as appropriate. It is recommended that you select this option and, whenever possible, upgrade with a single pass through the wizard that uses the Named Pipe data transfer option. 

• The Data transfer method option allows you to select either a Named Pipe transfer or a Tape transfer of the data. If you perform a two-computer upgrade to SQL Server 7.0, you must use a named pipe transfer. If you perform a one-computer upgrade and are short of disk space, you can use a locally installed tape drive and the tape data transfer method. However, in all cases, Named Pipe transfer is the fastest and recommended option. 

  If the tape option is unavailable (as shown in the preceding illustration) but you have a tape drive installed and you want to perform a tape data transfer method upgrade, you probably have some other tape program running, such as the Microsoft Windows NT Backup program. Cancel the wizard, close the open applications that might be using the tape drive, and then restart the SQL Server Upgrade Wizard. 

• The Verification option allows you to verify the successful transfer of the objects and data. The Validate successful object data transfer option verifies that the tables, views, triggers, stored procedures, indexes, rules, defaults, and constraints were created correctly in SQL Server 7.0. If you select this option, you can select Exhaustive data integrity verification. This option performs a byte-by-byte comparison between SQL Server 6.x and SQL Server 7.0. 

  Neither of these options is selected by default. When you choose these options, you receive a warning that the time it takes to perform an upgrade may increase significantly. Select these options if you want 100 percent verification of your upgrade. Selecting the Validate successful object data transfer option can add a significant amount of time to the upgrade, depending on how many objects you have to upgrade. Performing the exhaustive data integrity verification can add approximately one hour for each GB of data you want to upgrade, and is CPU-intensive. 

After you specify the options you want to use, click Next to view the Logon dialog box.

In this dialog box, you can select the export server and the import server. You must supply the system administrator password for both installations of SQL Server.

The optional startup arguments allow you to specify any flags that you want to use during the upgrade process. Unless you are instructed to do so by Microsoft SQL Server Product Support or your Independent Software Vendor (ISV), you should not specify any options here. The -p option is the most common option to specify. The -p option raises the precision of decimal and numeric data types to 38 digits, instead of the SQL Server default of 28 digits. You can specify this option for SQL Server 7.0 if you have used it for SQL Server 6.x and you want numeric values with digits of precision greater than 28 to transfer correctly.

You can verify startup parameters by selecting the Configure option for the server in SQL Server Enterprise Manager, and then clicking Parameters for SQL Server 6.5 as shown in this illustration.

The optional startup parameters you use for the SQL Server 7.0 server are not added to the SQL Server 7.0 configuration. You do not need to specify the –d or –e options on either SQL Server 6.x or SQL Server 7.0. The only options necessary are those that are not part of the existing SQL Server 6.x and SQL Server 7.0 startup parameters.

After you click Next in the Logon dialog box, a message warns that the SQL Server 6.x and SQL Server 7.0 installations will stop and restart.

Click Yes to continue. At that point, several background checks occur. You must correct any problems before you can continue with the SQL Server Upgrade Wizard. For a list of the checks that occur at this stage, see "Prepare the SQL Server 6.x Installation" earlier in this chapter.

The Code Page Selection dialog box appears.

The SQL Server Upgrade Wizard determines the code page in use by the SQL Server 6.x installation and recommends the code page to use during the upgrade. Do not change this selection unless you have been instructed to change it by Microsoft SQL Server Product Support or your ISV, or you have advanced experience with code page issues.

If you have selected a tape data transfer method, after you click Next, the Data Transfer dialog box appears.

In the Data Transfer dialog box, you can select which tape drive to use if you have multiple tape drives installed, whether to back up the SQL Server 6.x devices, and whether to delete the SQL Server 6.x devices:

• Select the Backup 6. x devices before exporting data option if you want the SQL Server Upgrade Wizard to back up the data files (rather than backing them up before you begin the upgrade process, as was recommended). If you choose to be prompted, the SQL Server Upgrade Wizard prompts you when it is time to back up the SQL Server 6.x device files. If you select Automatically copy device files to the following location, click Browse, and then select a network location on which to save your data files. At the appropriate point in the wizard, all of the SQL Server 6.x devices are copied to this network share. 

• If you select Delete 6. x devices before importing data, you must select how those devices will be deleted. Use this option if you do not have enough disk space to keep the SQL Server 6.x devices on the computer and to upgrade successfully to SQL Server 7.0. If you select Prompt before delete, you can choose to delete the SQL Server 6.x files. If you select Silent delete without prompt, the files are deleted. 

  Both of these options delete all of the SQL Server 6.x device files, including Master.dat. Use this option only if you are performing a complete upgrade of all databases to SQL Server 7.0. Make sure that you have backed up all of the SQL Server 6.x databases and the SQL Server 6.x device files before selecting this option. 

If you click the Browse button next to Device for data transfer, the Detected Tape Drives dialog box lists the names of tape devices. Select the one you want to use to hold the data that will be exported from the SQL Server 6.x databases.

 

Click Next to view the Upgrade Databases to SQL Server 7.0 dialog box, in which you can select the databases you want to upgrade to SQL Server 7.0.

You should upgrade all of the databases in a single pass, which is the default configuration. If you run the SQL Server Upgrade Wizard multiple times, the databases that have been upgraded previously appear in the Exclude these databases list by default.

The model database is selected for upgrade so that custom users or objects in the SQL Server 6.x model database will transfer to in the SQL Server 7.0 model database. Upgrade the SQL Server 6.x model database even if you have not modified it.

A database marked offline cannot be upgraded until the database is placed back online. Additionally, raw partitions are not supported during upgrade; therefore, databases that use raw partitions do not appear in this dialog box.

Click Next to view the Database Creation dialog box.

By default, the SQL Server Upgrade Wizard creates databases and files for you in SQL Server 7.0 for each database you have selected to upgrade. The number and layout of the files in SQL Server 7.0 is similar to the number and location of files that are used in SQL Server 6.x. In most cases, this option is recommended for the upgrade.

However, you may want to review or edit the default. Click Edit to configure the names, sizes, and locations of the files.

If you want to see the entire dialog box, click Advanced. You can modify the name, location, and size of the file, as well as the autogrowth increment. Right-click each file you want to modify, and change the attributes as needed. Also, you can remove or add files or filegroups as appropriate for your server configuration. When you complete your changes, click Accept.

If you have created the databases in SQL Server 7.0, in the Database Creation dialog box, select Use databases already created in SQL Server 7.0. Using this option is not recommended. The databases are matched by name. If you select this option, make sure that the databases are large enough to hold the data when it is imported into SQL Server 7.0, and configure the database compatibility mode. In addition, you must have a sufficiently large tempdb, assign database ownership, and set the compatibility level of the databases to the version of the export server by using sp_dbcmptlevel dbname, 65 (use 60 if the export server is SQL Server 6.0).

Also, you can specify a Transact-SQL create database script. This option is useful, however, only if you are familiar with SQL Server 7.0 CREATE DATABASE syntax. This is an advanced option and is recommended only for advanced users or ISVs who have tested their scripts carefully (unless you are performing an import-only upgrade from tape).

After you select any changes or accept the default configuration, click Next.

In the System Configuration dialog box, you can specify the system options you want to transfer. These include relevant server configuration options, replication settings, and SQL Server Agent settings (scheduled tasks, alerts, and operators). It is recommended that you transfer the server configuration options and SQL Server Agent settings, which includes tasks such as scheduled backup. If you do not use replication, the Replication settings check box is unavailable. If replication was enabled in the SQL Server 6.5 configuration, this option is selected the first time you run the SQL Server Upgrade Wizard.

You also can determine advanced settings, such as whether to use the ANSI Nulls option during transfer, and whether to use the Quoted Identifiers options. Unless a prior upgrade has failed, accept the defaults.

When you click Next, the syscomments system table in each SQL Server 6*.x* database you want to upgrade is examined. If a problem is encountered, those objects may not be upgraded.

After syscomments has been selected for integrity in each database you are upgrading, the Completing the SQL Server Upgrade Wizard dialog box appears.

If the message indicating no errors were detected does not appear, click View warnings and choices in notepad to view any problem reports. If problems are reported, repair them before continuing.

After all errors are repaired (the initial state for most configurations), click Finish to start the SQL Server Upgrade Script Interpreter.

The SQL Server Upgrade Script Interpreter is the program that performs most of the work during the upgrade process. The tasks that run depend on the choices you made in the SQL Server Upgrade Wizard. If you selected a default upgrade path, the first step is to run a script against the SQL Server 6.x database to upgrade the SQL-DMO capabilities. Then, the SQL Server Upgrade Script Interpreter exports the schema for all databases that are to be upgraded and includes the row counts and byte comparison values if you requested those options. System settings, including replication settings, are exported. Next, the process begins on the import server, including creating the database(s), importing the schema for those databases, and then loading the data from the SQL Server 6.x data files. Finally, the schema is verified, and row counts and byte comparison values are verified if you requested this step.

Files Created by the Upgrade Process

After the upgrade process is complete, you can view the files that are created. Each time you run the SQL Server Upgrade Wizard, a new directory is created in the \Mssql7\Upgrade directory. This directory name is in the format EXPORTSERVER_DATE_TIME. For example, on the server used for this chapter, the directory SERVER1_102898_134353 indicates the server name is SERVER1, the date is 28 October 1998, and the time is 1:43:53 PM. Within that directory, each database has its own directory.

In each database directory, you can find the results of running the create scripts for each type of object (such as .tab for tables). The root directory contains reports for various parts of the upgrade that are not related necessarily to the scripts run in each database. You do not have to examine any of these files unless there is a problem with the upgrade. A separate copy of the data is not kept; only scripts of objects, logins, users, scheduled tasks, and any other options you requested are kept.

Do not delete this directory until you are comfortable that you have upgraded fully to SQL Server 7.0.

Database Compatibility in SQL Server 7.0

SQL Server 7.0 has built-in backward compatibility. When you upgrade the databases to SQL Server 7.0, they are left in a backward compatible state. If you upgrade from SQL Server 6.0, the databases are in 6.0 mode. If you upgrade from SQL Server 6.5, the databases are in 6.5 mode. Databases in native SQL Server 7.0 mode are said to be in 7.0 mode.

Backward compatibility modes allow your application to function as it did in SQL Server 6.x. For example, the TOP keyword in SQL Server 7.0 is not valid in a SQL Server 6.x backward compatibility mode, because this word may have been used as a column name, a column alias, or in another context that is not valid in SQL Server 7.0.

In addition to syntax changes, some functional behaviors are affected also. In SQL Server 6.x, a SELECT statement with a GROUP BY clause always implies an ORDER BY on the same columns. In SQL Server 7.0, this is not the case; however, in SQL Server 6.x backward compatibility modes, an ORDER BY is implicitly added to GROUP BY queries so that the data is returned in the same sequence that it would have been in SQL Server 6.x. For more information about backward compatibility, see SQL Server Books Online.

You can run the sp_dbcmptlevel system stored procedure to switch between backward compatibility modes. For example, if you upgrade a database named upgrade_test, you run exec sp_dbcmptlevel 'upgrade_test', 70 to change the database compatibility level to SQL Server 7.0 mode. No other database on the server is affected by this change. To switch back to SQL Server 6.5 mode instantly, simply run exec sp_dbcmptlevel 'upgrade_test', 65. This switching mechanism allows you to switch compatibility modes easily and test your application. If no problems are encountered, you can leave the database in 7.0 mode. If you do encounter issues that require coding changes, you can switch back to the compatibility mode of the earlier version until you have time to complete the code changes. You still get most of the performance enhancements and the other benefits of SQL Server 7.0 while running your applications in backward compatibility mode.

Note The master database must always be in 7.0 compatibility mode; therefore, applications that use the master database must change immediately when upgrading.

Testing the Upgrade Process

The SQL Server Upgrade Wizard has been tested extensively. Microsoft upgraded literally thousands of production databases to determine problems that might be encountered and ways to handle them appropriately. Microsoft worked with ISVs and businesses to upgrade SQL Server 6.x databases to SQL Server 7.0 to ascertain requirements for backward compatibility. Microsoft assisted its internal information technology department with the upgrade to SQL Server 7.0.

Because of this testing, the SQL Server Upgrade Wizard and the backward compatibility modes changed during development to meet the needs of customers.

In addition to backward compatibility support in SQL Server 7.0, Microsoft implemented several other projects to help ensure the integrity and simplicity of the upgrade process.

The 1K Challenge Project

The 1K Challenge was a Microsoft project to upgrade 1,000 real customer databases before shipping SQL Server 7.0. Customers (under nondisclosure) sent Microsoft their SQL Server 6.0 and SQL Server 6.5 production databases to be upgraded to SQL Server 7.0.

More than 92 percent of all customer databases converted during the 1K Challenge project were 100 percent successful. These databases were received from 27 countries and varying in complexity and size ranging from 25 MB to 200 GB. Because these databases represent a random sampling of databases from Microsoft's worldwide customer base, these numbers should accurately predict the customer's upgrade experience. An additional 6.4 percent of the converted databases required only minor changes to migrate to SQL Server 7.0. Based on these results, the majority of customers can upgrade their databases successfully on their first attempt.

The ISV Migration Lab Project

The ISV Migration Lab invited certain companies that sell SQL Server as part of their solutions to upgrade to SQL Server 7.0 at the SQL Server development site. ISVs participating in the lab upgraded their databases by using the SQL Server Upgrade Wizard, and then tested their applications by using the configurations that their customers use in production. The ISVs tested SQL Server versions 6.5 and 7.0 client libraries (for example, DB-Library, ODBC, OLE DB) with their converted databases in both 6.5 and 7.0 compatibility modes.

More than 96 percent of all applications tested during the ISV Migration Lab project run unchanged with SQL Server 7.0 in 6.5 backward compatibility mode. That is, the client computers using the ISV application run unchanged against a database that has been upgraded to SQL Server 7.0 with the SQL Server Upgrade Wizard.

Internal Database Migrations at Microsoft

Microsoft has migrated most of its internal SQL Server databases to SQL Server 7.0, including all mission-critical systems. Each of these upgrades occurred with beta versions of SQL Server 7.0.

The SAP R/3 enterprise resource planning (ERP) environment at Microsoft manages the company's worldwide financials, human resources, OEM contracts and all procurement transactions. Moving SAP R/3 to SQL Server 7.0 has drastically reduced the update performance time from an average of more than 5 seconds to 0.7 seconds. In addition, the total database size has been reduced from 140 GB to 80 GB, system availability has increased substantially, and backup throughput has increased 450 percent.

Microsoft Sales, Microsoft's revenue-reporting data warehouse, contains 150 GB of sales data that supports business intelligence throughout the corporation, and is used concurrently by more than 1,600 employees worldwide, primarily accountants and sales and marketing people. Since the move to SQL Server 7.0, query performance has increased 57 percent, and backup speed has increased 25 percent. The Microsoft Sales database upgraded quickly and easily in about 24 hours.

With SQL Server 7.0, Microsoft's product support systems used for call tracking, credit card billing, and customer contract information can be used concurrently by up to 3,600 support specialists worldwide. SQL Server 7.0 provides Microsoft with the enhanced reliability and scalability imperative to keep the system up and running continuously, an advantage that translates into better customer service and lower support costs.

Microsoft has also converted its corporate Web site, https://www.microsoft.com/ , to SQL Server 7.0. This site, one of the 10 busiest Web sites on the Internet, receiving between 140 million and 200 million hits a day, is run by multiple SQL Server databases, each of which has been upgraded to SQL Server 7.0. Additionally, MSNBC and MSN have upgraded to SQL Server 7.0.