Lesson 4: Performing a Version Upgrade from SQL Server 6.5 | MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228

3 4

You upgrade a SQL Server 6.5 installation by running the SQL Server Upgrade Wizard on a computer that has SQL Server 2000 installed as the default instance. An upgrade from SQL Server 6.5 to SQL Server 2000 is more involved than an upgrade from SQL Server 7.0 and is therefore more likely to have objects that could not be upgraded properly. This might require some troubleshooting. Finally, you need to be aware that because of changes in features, you might need to use backward compatibility levels to make the transition to SQL Server 2000 smooth.

After this lesson, you will be able to

Perform a version upgrade of SQL Server 6.5 to SQL Server 2000
Troubleshoot a SQL Server 6.5 upgrade
Specify backward compatibility levels for upgraded databases

Estimated lesson time: 30 minutes

Performing a Version Upgrade

To perform a version upgrade of SQL Server 6.5 to SQL Server 2000, you must run the SQL Server Upgrade Wizard from a computer on which you have installed SQL Server 2000 as the default instance. You start the SQL Server Upgrade Wizard from the Microsoft SQL Server - Switch program group. This wizard is available on any computer that has SQL Server 2000 installed as the default instance. This wizard (Upgrade.exe) is also in the Upgrade folder of your SQL Server 2000 installation. The upgrade folder location is \Program Files\Microsoft SQL Server\Mssql\Upgrade. See Figure 4.12.

Note

You must use SQL Server authentication to connect to the SQL Server 2000 server. Because the default authentication mode of SQL Server 2000 allows only Windows authentication, you might need to change the SQL Server 2000 configuration to permit SQL Server authentication. This requires a restart of the SQL Server service. Changing authentication modes is covered in Chapter 10.

When you run the SQL Server Upgrade Wizard, you perform the following steps:

When you start the SQL Server Upgrade Wizard, using one of the methods described above, it begins by recommending that you read the Upgrading topics online carefully. The major issues are discussed in this lesson, but you should definitely read the Upgrading topics in Books Online as well. The changes from SQL Server 6.5 to SQL Server 2000 are substantial. See Figure 4.13.

Figure 4.12

Starting the SQL Server Upgrade Wizard.

Figure 4.13

Welcome to the SQL Server Upgrade Wizard.
Next, on the Data And Object Transfer page, the wizard prompts you to select the upgrade method you will use. You can perform a direct upgrade on the same computer using either named pipes or a tape device for the transfer of data (the tape device option will be available only if a tape device is detected on the local computer). You can also choose to perform additional verification options. If you choose to have the SQL Server Upgrade Wizard validate the successful transfer of data objects, the wizard prepares a list of all objects in the SQL Server 6.5 databases and the number of rows in each table before the upgrade and then compares this list to a similar list after the upgrade. The wizard reports any discrepancies. This verification is in addition to the reporting of any problem in the creation of database objects that is recorded by default in the output logs. Selecting this initial level of verification is highly recommended. The next level of verification is to perform an exhaustive byte-by-byte checksum verification on each column in each table to verify that no data values have changed. This level of verification substantially increases the time required for the upgrade. Errors occur only rarely, but if the time is available, this additional level of verification is also recommended. See Figure 4.14.

Figure 4.14

Selecting the upgrade method and options.
Next, on the Logon page, you specify the name of the SQL Server 6.5 computer you want to upgrade (called the export server). The default is the computer on which you are running the wizard, but you can specify any other computer in the same domain. The server you are upgrading to must be the server on which you are running the wizard (called the import server).

Note
If you are upgrading a database involved in replication, you must perform a single computer upgrade, and the database compatibility level must be set to 70 during the upgrade.

In addition, you must specify the password for the SQL Server administrator account (sa) for both the export and the import server. You can also provide optional startup arguments for the export and the import server. See Figure 4.15.

Figure 4.15

Specifying logon information for the export and import servers.
After you provide the name of the server being upgraded and provide the authentication information and optional startup arguments for both the servers, the wizard displays a SQL Server Upgrade Wizard dialog box warning that both of the SQL Server installations need to be stopped and then restarted, using these authentication and startup parameters. During this verification process, the wizard also obtains the code page used by SQL Server 6.5 from the master database.
Next, the Code Page Selection page appears. The Upgrade Wizard requires the selection of a scripting code page, which is used to create the upgrade scripts. Most users can accept the default code page. The code page used in the upgrade scripts must match the code page of the database being upgraded. See Figure 4.16.

Figure 4.16

Code page selection for upgrade scripts.
You then select databases to upgrade on the Upgrade Databases To SQL Server 2000 page. You can choose to upgrade some or all user databases. Notice that the master, msdb, publication, pubs, and Northwind databases are not available for selection. Only the model database and any user-created user databases can be selected for upgrading. It is recommended that you upgrade all databases at the same time. See Figure 4.17.

Figure 4.17

Selecting databases to upgrade.

After you select the databases to upgrade, the wizard examines the layout of SQL Server 6.5 devices. It uses this information to create database files in SQL Server 2000.
Next, the Database Creation page appears. The wizard creates data and log files for the databases being upgraded. The data files are sized to hold all transferred objects and data, with no allowance for additional free space. The log files are sized based on the size of the SQL Server 6.5 log files. The locations of the data and log files are the same as for the first device for data and logs in SQL Server 6.5. If multiple devices were used in SQL Server 6.5, multiple files will be created in SQL Server 2000, but the initial file is sized to contain the objects and data with additional files sized minimally. On the Database Creation page, you can specify a custom configuration of database files and logs by editing this default configuration, using databases previously created for this purpose in SQL Server 2000, or by using Transact-SQL scripts. See Figure 4.18. When you click the Edit button on the Database Creation page, the SQL Server Upgrade Wizard dialog box appears. In this dialog box, you can modify the name, file path, and initial size of the file, as well as the autogrow increment. See Figure 4.19.

Figure 4.18

Specifying the database configuration for the upgrade.

Figure 4.19

Modifying the database configuration.
Next, on the System Configuration page, you select system objects to transfer. These include server configuration information (such as local information and remote logon registrations), replication settings (including all articles, publications, and subscriptions) and SQL Executive settings (including all tasks and schedules). In addition, you must set the ANSI_NULLS and the QUOTED_IDENTIFIER settings. The wizard uses these settings for all database objects it creates. Refer to the topic "System Configuration" in Books Online for more information regarding these advanced settings. The choices you make for these settings will vary based on your existing SQL Server 6.5 databases and how you created objects within them. See Figure 4.20.

Figure 4.20

Selecting system objects to transfer.
Finally, the Completing The SQL Server Upgrade Wizard page appears. A summary of your choices and any warning messages are listed here. Click the Finish button to begin the upgrade. See Figure 4.21.

Figure 4.21

Completing the SQL Server Upgrade Wizard.

While the upgrade is in process, the wizard displays each step in the process in the SQL Server Upgrade Script Interpreter dialog box and then notifies you when the upgrade is complete. If an error occurs, details of the error are displayed. See Figure 4.22.

Figure 4.22

Notification of your completed upgrade.

Troubleshooting a SQL Server 6.5 Upgrade

If the SQL Server Upgrade Wizard encounters problems during the upgrade, either you are notified during the upgrade of the problem or you must look in the upgrade logs created by the wizard specifying any problems it encounters. Typical problems are an inability to create objects and tables (you will have to create these manually after the upgrade). The possible reasons for this include:

Text is missing from the syscomments table.
Objects were renamed using sp_rename (the syscomments entry is not updated when the object is renamed).
Stored procedures were embedded within other stored procedures (no entry exists in syscomments for these stored procedures).
Table and views have NULL column names (the wizard cannot script these objects).
Tables were created on behalf of a user that does not have CREATE permissions.
A stored procedure modifies a system table or references a system table that does not exist in SQL Server 2000.

Another problem that can occur is having a computer name that does not match the server name returned by @@SERVERNAME. Use the sp_dropserver and sp_addserver system stored procedures to change the server name returned by @@SERVERNAME to match the computer name.

Specifying a Backward Compatibility Level for Upgraded Databases

When you upgrade databases from SQL Server 6.5 to SQL Server 2000, it is likely that you will have objects in the upgraded databases that use features that have changed. Most applications are not affected by the changes in behavior. However, in some cases, applications will need to be upgraded. SQL Server 2000 allows you to set a backward compatibility level to retain the earlier behavior while retaining almost all of the performance enhancements of SQL Server 2000. This allows time for applications to be upgraded. The backward compatibility setting affects only a small number of Transact-SQL statements. As always, test your applications thoroughly after the upgrade. If you need to set a backward compatibility level for an upgraded database, use the sp_dbcmptlevel system stored procedure. Valid levels are 60, 65, 70, and 80. You can also set the compatibility level using SQL Server Enterprise Manager by right-clicking the desired database, clicking Properties, clicking the Options tab, and then selecting the desired compatibility level.

Note

Microsoft might drop the 60 and 65 backward compatibility levels in future versions of SQL Server.

Lesson Summary

The SQL Server Upgrade Wizard allows you to upgrade a SQL Server 6.5 installation to the default instance of SQL Server 2000. The default instance can be on the same computer as the SQL Server 6.5 installation, or it can be on a second computer. After the upgrade is complete, SQL Server 6.5 will be upgraded to SQL Server 2000, and your SQL Server 6.5 installation will remain intact. This allows you to verify the upgrade. Check the upgrade logs to determine whether certain objects were not upgraded. Finally, test your applications and use backward compatibility levels where necessary until you can upgrade the applications.