Microsoft SQL Server 7.0 System Administration Training Kit

This lesson describes how to upgrade SQL Server using the SQL Server Upgrade Wizard. When you use the wizard to upgrade from SQL Server 6.x to SQL Server 7, you must

• Decide whether you will use one computer or two to perform the upgrade
• Determine how to transfer the data from SQL Server 6.x to SQL Server 7
• Specify which databases, server configuration, replication settings, SQL Executive settings, and database characteristics to import

Each of these topics is covered in this lesson.

After this lesson, you will be able to

• Upgrade a Microsoft SQL Server 6.x database to SQL Server 7

Estimated lesson time: 30 minutes

Choosing an Upgrade Method

When you upgrade SQL Server 6.x, you must choose whether to upgrade using a single computer or two computers, and then you must select the method of transferring data and objects.

One-Computer Upgrade

You can perform an upgrade on a single computer by using a disk-to-disk named pipe connection or a tape upgrade. When the upgrade is complete, SQL Server 7 immediately takes over as the production server.

For a one-computer upgrade, the same computer is both the import and export server.

Two-Computer Upgrade

You can install SQL Server 7 on one computer and then connect to another computer where SQL Server 6.x is installed. The upgrade takes place using a named pipe connection to transfer data. When the upgrade is complete, SQL Server 7 immediately takes over as the production server.

If you perform a two-computer upgrade, assign a domain user name and password to the MSSQLServer service in your SQL Server 6.x and SQL Server 7 installations instead of using the local system account or a local user account. The domain user account should belong to the Administrators group of both computers. The two computers must both belong to the same domain.

For a two-computer upgrade, the SQL Server 6.x computer is the export server and the SQL Server 7 computer is the import server.

NOTE

Methods for Transferring Data

SQL Server presents several methods for transferring data from SQL Server 6.x to SQL Server 7. These methods depend on the choice of a one-computer versus two-computer upgrade and the amount of disk space available on the import server. Figure 3.1 shows the screen in which you make your choice.

Figure 3.1 Choosing a data and object transfer method

Named Pipes

For a one-computer upgrade, a direct pipeline enables the SQL Server Upgrade Wizard to transfer data in memory from SQL Server 6.x to SQL Server 7. The named pipe method is the most reliable and provides the best performance. When performing a single-computer upgrade using Named Pipes, you cannot reuse the disk space occupied by SQL Server 6.x and the SQL Server 6.x devices until the version upgrade process is complete.

Tape

You can use the tape backup option when you want to perform a one-computer upgrade but the hard disk space is limited. The SQL Server Upgrade Wizard backs up to tape all of the SQL Server 6.x databases you select to upgrade. You also can use the SQL Server Upgrade to delete all of the SQL Server 6.x devices, freeing disk space before the SQL Server 7 data files are created.

CAUTION

Selecting Upgrade Options

SQL Server presents several options for upgrading SQL Server 6.x databases to SQL Server 7. These options determine what information is imported from the SQL Server 6.x installation as well as how the upgrade is performed. Review these options before you run the SQL Server Upgrade Wizard.

Performing Verification

The SQL Server Upgrade Wizard can verify that objects, including schema and stored procedures and data, are transferred correctly.

You can also choose to perform exhaustive data integrity verification. The SQL Server Upgrade Wizard then performs a checksum for each column of each table before and after the upgrade to verify that data values do not change.

Any discrepancies found are reported in the output logs of the SQL Server Upgrade Wizard.

Specifying a Scripting Code Page

The SQL Server Upgrade Wizard requires a scripting code page, used to create the upgrade scripts. Figure 3.2 shows the Code Page Selection screen of the wizard.

Figure 3.2 Specifying a code page

The default scripting code page is the code page recorded in the master database. If you know that the actual code page is different from the recorded code page, select the actual code page from the list. Most users need only accept the default code page.

If you choose a scripting code page other than the default, it is recommended that you do not upgrade replication settings. If the server is involved in replication, reconfigure the replication settings manually after the upgrade is complete.

Selecting the Databases to Upgrade

You can choose to upgrade some or all of your SQL Server 6.x databases. Figure 3.3 shows the screen in which you specify those databases you wish to include or exclude.

Figure 3.3 Choosing the databases to upgrade

The master, msdb, and distribution system databases, and any sample databases, are not explicitly available for selection. However, you can upgrade the logins and server configuration stored in the master database, the replication settings stored in the distribution database, and the tasks stored in the msdb database by selecting options presented by the wizard.

NOTE

If you run the SQL Server Upgrade Wizard again after you have upgraded the databases, previously updated databases will default to the excluded list. If you want to upgrade a database again, move it to the included list in the wizard. You must delete the database in SQL Server 7 before running the upgrade again.

Choosing a New Database Configuration

The screen shown in Figure 3.4 offers options for creating the SQL Server 7 database and log files. You can have the wizard create the new database automatically, or you can specify a custom configuration.

Figure 3.4 Specifying a database configuration

Using the SQL Server Upgrade Wizard (the Default)

The Upgrade Wizard will automatically create new databases, basing the sizes and locations of the data and log files on those of the existing devices. You can view and edit the default database configuration in the Upgrade Wizard. For each database and log file, you can modify the name and file path, the initial size of the file, and the autogrow increment.

Specifying a Custom Configuration

You can specify your own configuration in one of two ways: by using databases and logs that you create in SQL Server 7 before running the Upgrade Wizard, or by using a Transact-SQL script file that you provide. If you provide a script file, it must use the new SQL Server 7 CREATE DATABASE statement syntax. Do not use the script you used to create your databases in SQL Server 6.x. If you are not familiar with the new CREATE DATABASE statement, do not use a Transact-SQL script to create the new databases.

Choosing Objects to Transfer

When upgrading system databases, the SQL Server Upgrade Wizard can transfer the server configuration and various service settings, as shown in Figure 3.5.

Figure 3.5 Choosing the system objects to transfer and the data characteristics

Server Configuration

When the Server Configuration option is checked, logins and server configuration options relevant to SQL Server 7 are transferred as part of the version upgrade process. The SQL Server 6.x configuration options that are not used in SQL Server 7 are not transferred.

Replication Settings

When the Replication Settings option is checked, all articles, subscriptions, and publications of each selected database, including the distribution database, if any, are transferred and upgraded.

SQL Executive Settings

When the SQL Executive Settings option is checked, all tasks scheduled by SQL Executive are transferred and upgraded so that SQL Server 7 can schedule and run the tasks in SQL Server Agent.

ANSI Nulls

The ANSI Nulls option controls both database default nullability and comparisons against null values. When upgrading SQL Server 6.x to SQL Server 7, you must set the ANSI Nulls option to On or Off.

When the SQL Server Upgrade Wizard creates the SQL Server 7 database tables, the database default nullability determined by the ANSI Nulls option is not an issue. All columns are explicitly qualified as NULL or NOT NULL based on their status in SQL Server 6.x.

The ANSI Nulls option is important with regard to comparisons against null values when the SQL Server Upgrade Wizard creates the SQL Server 7 database objects. With ANSI Nulls On, the comparison operators EQUAL (=) and NOT EQUAL (<>) always return NULL (UNKNOWN) when one of their arguments is NULL. (This is the ANSI SQL-92 standard for handling NULL values.) With ANSI Nulls Off, these operators will return TRUE or FALSE, depending on whether both arguments are NULL.

In SQL Server 6.x, the ANSI Nulls option in objects, such as stored procedures and triggers, is resolved during query execution. In SQL Server 7, the ANSI Nulls option is resolved when the object is created. When upgrading you must choose the ANSI Nulls option that you want for all objects in the databases. The SQL Server Upgrade Wizard then creates all database objects using this setting.

If you have stored procedures in your old database that use SQL Server nullability, set ANSI Nulls to Off. If you have stored procedures in your old database that use ANSI nullability, set ANSI Nulls to On.

Quoted Identifiers

The Quoted Identifiers setting determines what meaning SQL Server gives to double quotation marks (""). When the Quoted Identifiers setting is Off, double quotation marks delimit a character string, just as single quotation marks do. When Quoted Identifiers is On, double quotation marks delimit an identifier, such as a column name. An identifier must be enclosed in double quotation marks, for example, if its name contains characters that are otherwise illegal in an identifier, including spaces and punctuation, or if the name conflicts with a reserved word in Transact-SQL. Regardless of the Quoted Identifiers setting, an identifier can also be delimited by square brackets.

The meaning of the following statement, for example, depends on whether Quoted Identifiers is On or Off:

If the Quoted Identifiers setting is On, "x" is interpreted to mean the column named x. If it is Off, "x" is the constant string x and is equivalent to the letter x.

The Quoted Identifiers setting in the SQL Server Upgrade Wizard corresponds to the SQL Server QUOTED_IDENTIFIER setting. If the previous SELECT statement example were part of a stored procedure created when QUOTED_IDENTIFIER was ON, then "x" would always mean the column named x. Even if the QUOTED_IDENTIFIER setting was later set to OFF, the stored procedure would act as though it were set to ON and treat "x" as the column named x.

When the SQL Server Upgrade Wizard re-creates database objects in SQL Server 7, the Quoted Identifiers setting determines how all of these objects behave. If all database objects were created in SQL Server 6.x with the same QUOTED_IDENTIFIER setting, click that setting for Quoted Identifiers, either On or Off. If objects were created in SQL Server 6.x with a mix of the two settings, or if you are unsure of the settings used, click Mixed.

With the Mixed option, the SQL Server Upgrade Wizard first converts all objects containing double quotation marks with QUOTED_IDENTIFIER set to ON. The wizard then converts any objects that failed to be created with QUOTED_IDENTIFIER set to OFF.

Running the Upgrade

Once you have selected all of the upgrade options, the upgrade process runs. The SQL Server Upgrade Script Interpreter dialog box will keep you informed as each step in the process completes (see Figure 3.6).

Figure 3.6 Running the upgrade

Lesson Summary

You can perform an upgrade on one or two computers, and you can transfer the data during the upgrade using a named pipes direct transfer or a transfer using tape. Verify the database objects to be transferred before performing the upgrade. In the Upgrade Wizard, you must specify a number of options that determine how objects will be transferred and created.