Lesson 3: After Using the SQL Server Upgrade Wizard

[Previous] [Next]

Due to the significant improvements and changes made in SQL Server 7, it is possible that some of the objects in an upgraded database will not be correctly created in SQL Server 7. You should be aware of the changes in SQL Server 7 and should plan to change your databases to use fully supported SQL Server 7 options.

After this lesson, you will be able to

• Troubleshoot a database upgrade
• Set the database compatibility level

Estimated lesson time: 30 minutes

Removing SQL Server 6.5

After upgrading the SQL Server 6.x databases to SQL Server 7, you may want to leave SQL Server 6.x on your computer until you are sure you no longer need it. When ready, you can remove SQL Server 6.x by using the Remove SQL Server 6.x application on the Start menu.

CAUTION

---

If you need to reinstall SQL Server 6.x, you must first remove SQL Server 7. SQL Server 6.x cannot be installed on a computer alongside an existing SQL Server 7 installation.

Troubleshooting the Upgrade

If you encounter difficulties in upgrading, identify the problem and view the upgrade log files.

Identifying Common Upgrade Problems

You cannot upgrade some objects and settings to SQL Server 7 without modification. If you encounter problems during the upgrade process, check the following:

Objects with Inaccurate or Missing Entries in syscomments

In order to upgrade objects, the text description stored in the syscomments table in SQL Server 6.x must be intact. Objects will not upgrade if

• Text in syscomments has been deleted.
• They were renamed using sp_rename. This system stored procedure does not alter the entry in syscomments for an object.
• They are stored procedures that were created within other stored procedures. There is no entry in syscomments for these stored procedures.

Note

---

Objects created with encrypted text in the syscomments table are upgraded.

A Server Name That Does Not Match the Computer Name

The computer name on which SQL Server runs must match the server name returned by @@SERVERNAME. If the names do not match, the SQL Server Upgrade Wizard may fail. To correct this problem, change the server name returned by @@SERVERNAME to match the computer name, using the sp_dropserver and sp_addserver system stored procedures.

Stored Procedures That Modify and Reference System Tables

Stored procedures that do the following will not upgrade:

• Modify system tables
• Reference system tables or columns that do not exist in SQL Server 7

Restrictions on Creating Tables

During the upgrade process, you may encounter the following problems in upgrading tables and views:

• Tables and views with NULL column names will not upgrade because the wizard cannot script these objects.
• Tables created by the system administrator on behalf of another user who does not have create-table permissions will not upgrade. Because the object owner does not have CREATE permissions, the script to create the object fails.

Viewing the Upgrade Log Files

The SQL Server Upgrade Wizard creates a folder in the C:\Mssql7\Upgrade directory each time it runs. The folder name consists of the server name and the current date and time, in order to distinguish multiple runs of the SQL Server Upgrade Wizard. For example, the name SQLCONV1_092198_151900 indicates that the wizard was run on a SQL Server called SQLCONV1 on 9/21/98 at 15:19.

This folder contains a number of descriptively named log files describing each upgrade step. It contains subfolders for each upgraded database, including the master database. These subfolders contain log files indicating the success or failure of creating objects in the database.

Files with an .OK extension indicate that all instances of that type of object were created successfully. Files with an .ERR extension indicate that at least one instance of that type of object was not created successfully. The error files list each failed object creation statement and the reason the object was not created successfully.

Any log files that indicate a problem are listed at the end of the upgrade, in the SQL Server Upgrade Wizard, for easy access.

Specifying Compatibility Levels

If you are upgrading databases to SQL Server 7, you will probably have objects in the upgraded database that use features that have changed. SQL Server 7 supports different compatibility levels in order to make the transition from previous versions as easy as possible.

What Is a Compatibility Level?

When run with default settings, most SQL Server 6.x applications work unchanged after an upgrade to SQL Server 7 by the SQL Server Upgrade Wizard.

The compatibility level can be set for any SQL Server 7 database, using the sp_dbcmptlevel system stored procedure. The level can be set to 60, 65, or 70, according to the version of SQL Server with which you require compatibility; it defaults to 70 for new databases.

When you upgrade existing systems with existing applications, you can use the database compatibility level settings to retain earlier behaviors if your existing applications depend on those behaviors. Setting a compatibility level gives you time to upgrade applications in a planned, orderly manner, although it is recommended that you update all of your scripts to full SQL Server 7 compatibility when possible. Future versions of SQL Server will not necessarily offer compatibility with versions earlier than SQL Server 7. Many applications are not affected by the changes in behavior.

The effects of the compatibility level settings are generally limited to the behaviors of a small number of Transact-SQL statements that also exist in earlier versions of SQL Server. When the database compatibility level is set to 60 or 65, applications still gain almost all of the benefits of the new performance enhancements of SQL Server 7.

Initial Settings

You can select initial compatibility level settings for user, model, and master databases.

User Databases

The SQL Server Upgrade Wizard sets the compatibility level of upgraded databases to the version number of the export server.

For example, if your server is SQL Server 6.5 and you upgrade to SQL Server 7, the compatibility level for all existing user-defined databases is set to 65. This setting enables existing applications to run with a minimum number of changes after an upgrade.

The model Database

The compatibility level of an upgraded model database is set to 70. If you change this setting, it will propagate to new databases.

The master Database

The compatibility level of an upgraded master database is set to 70. You should not change this setting. If an upgraded stored procedure in the master database requires a SQL Server 6.x compatibility level, you must move it out of the master database.

Backward Compatibility Details

If you have been using a previous version of SQL Server, you should be aware of the major feature changes that affect the operation of SQL Server 7. These changes are grouped into four levels:

• Level 1—Statements, stored procedures, or other items that have been removed in SQL Server 7. Code or scripts that use these items must be changed before they are used with SQL Server 7.

Examples: The DISK REINIT and DISK REFIT commands.

• Level 2—Changes that cause significantly different behavior in SQL Server 7. Code or scripts that use these items probably need to be changed; new behavior must at least be well understood so that you are not taken by surprise.

Example: When restoring multiple transaction logs, the last RESTORE statement must specify the WITH RECOVERY option; all other RESTORE statements must specify the WITH NORECOVERY option.

• Level 3—Items that are fully supported in SQL Server 7, but for backward compatibility only. Future versions of SQL Server may not support these items, and you should begin using the SQL Server 7 replacement when possible.

Example: DBCC ROWLOCK enabled Insert Row Locking. Row locking is now automatic and DBCC ROWLOCK is not required.

• Level 4—Changes that produce slightly different behavior in SQL Server 7.

Example: The SQL-92 syntax for outer joins (LEFT OUTER JOIN and RIGHT OUTER JOIN instead of *= and =*) should be used.

Your upgrade planning should include checking your existing databases and scripts for items that fall into any of these levels and replacing them before or after the upgrade as necessary.

Registering a SQL Server 6.5 Server Using Enterprise Manager

You can register a 6.5 server in SQL Server Enterprise Manager. The 6.5 server can be started and stopped, and selecting the 6.5 server launches the version 6.5 SQL Server Enterprise Manager. The 6.5 SQL Server Enterprise Manager must be installed on the computer. Both versions of Enterprise Manager and the other SQL Server client utilities can be installed on the computer where SQL Server 7 is installed or on any Microsoft Windows 95, Windows 98, or Windows NT computer on a network.

Lesson Summary

One of the most important issues in performing an upgrade is testing the upgrade and reviewing the upgrade log files to ensure that the upgrade correctly transferred your databases. It is possible that you will have to create some objects manually after changing the old scripts for these objects to use new SQL Server 7 syntax. After upgrading, you should begin updating all of your scripts to use SQL Server 7 syntax, even if the objects transfer correctly with the Upgrade Wizard.

You can use the compatibility level to force SQL Server 7 to use SQL Server 6.x functionality for certain Transact-SQL statements. Four levels of changes have been defined to help you prioritize the modifications you need to make your code and scripts.