Upgrading from Previous Versions

SQL Server 2000 supports upgrades from SQL Server 6.5 and SQL Server 7.0. To upgrade a SQL Server 6.0 installation, you must upgrade first to version 6.5 or 7.0 and then upgrade to SQL Server 2000.

When installing SQL Server 2000 on a computer with an existing installation of SQL Server 7.0, you can choose to have the installation program overwrite the existing installation. This converts your existing installation and modifies your database files to the SQL Server 2000 format. Alternatively, you can install a named instance of SQL Server 2000, which doesn't touch the SQL Server 7.0 installation. The version 7.0 databases can now be restored to SQL Server 2000 using backups , they can be attached to the new installation using sp_attach_db, or if you choose, you can use the new Copy Database Wizard to copy your databases. This wizard uses a DTS package to copy the database and leaves the existing SQL Server 7.0 database intact. If you want, the operation can be scheduled for execution in off-normal periods. Using this option on the same server as the existing installation assumes that space exists on the server for both databases to coexist.

If you are upgrading from version 6.5, no option is available to upgrade your 6.5 databases during the installation process. After SQL Server 2000 is installed, you can upgrade using the SQL Server Upgrade Wizard. After the wizard runs, your databases will have been transferred into SQL Server 2000 and will remain intact in the SQL Server 6.5 installation as well. At this point, you can delete the previous version, or you can use the switch utility to switch between versions.

Regardless of the version from which you are upgrading, it is imperative that you perform backups immediately prior to the upgrade to prevent any data loss. No matter how well you formulate your upgrade plan, there is always the possibility for disaster when performing major software revisions. Having a good backup on hand gives you peace of mind, knowing that in the worst-case scenario, you can always go back to where you started. If you don't have a backup and problems arise, remember those six magic words for your new career: "Do you want fries with that?"

Hardware and Software Requirements for Upgrading

In addition to the hardware and software requirements for an installation of SQL Server 2000, the computer must meet some additional requirements for an upgrade. The operating system must be at least Windows NT Server or Workstation Service Pack 5 or later, Windows 2000, or Windows XP. Internet Explorer Version 5.0 or later must also be installed.

If you are upgrading SQL Server Version 6.5 to SQL Server 2000 on the same server, the SQL Server 6.5 instance must be at Service Pack 5 or later. If you are upgrading SQL Server 6.5 to an instance of SQL Server 2000 on another server, SQL Server 6.5 must be at Service Pack 3 or later.

Both servers involved in the upgrade must be configured for named pipes and be listening on the default pipe, \\.\pipe\sql\query . Named pipes are required even if you are upgrading the database using tape backups.

If you are upgrading the existing instance of SQL Server 7.0 to 2000, no additional disk space is required. If you plan to keep the original SQL Server 7.0 instance and migrate to SQL Server 2000, you will need additional disk space equivalent to the size of the current 7.0 databases. If you are upgrading a 6.5 SQL Server to SQL Server 2000, you will need approximately 1.5 times the space used by the 6.5 databases.

Post-Upgrade Tasks

After you upgrade from SQL Server 7.0 to SQL Server 2000, you should repopulate any full-text catalogs and update all statistics on your database tables. Both operations can be time-consuming , but will improve the performance of your SQL Server 2000 instance.

Your full-text catalogs need to be updated because the upgrade process marks your databases as full-text disabled due to a difference in formats from 7.0 to 2000. Your full-text catalogs must be repopulated after an upgrade, but this operation is not automatically run by the upgrade process. You should schedule a task to repopulate all full-text catalogs at an appropriate time. For more information, on managing full-text catalogs, see Chapter 44, "SQL Server Full-Text Search Services."

You should also update all statistics for the tables in the 7.0 databases upgraded to 2000. Again, this update might take a significant amount of time on large databases and should be scheduled to run at an appropriate time. Continuing to use SQL Server 7.0 statistics with SQL Server 2000 could result in poor query performance. For more information on index statistics and updating them, see Chapter 34, "Indexes and Performance."

In addition to updating statistics, it is also recommended that you drop and re-create all stored procedures in your upgraded databases. This will ensure that all stored procedure code behaves properly under SQL Server 2000 compatibility and also ensures that the procedures take advantage of the optimization enhancements available in SQL Server 2000.

Upgrade Compatibility Issues

The differences between SQL Server 7.0 and SQL Server 2000 are not quite as drastic as the differences between SQL Server 6.5 and 2000. The database structures in 7.0 are nearly identical to the SQL Server 2000 storage structures and there are no significant changes to the syntax or behavior of the Transact-SQL commands. Most of the changes between 7.0 and 2000 are new features and enhancements. As a matter of fact, the only real compatibility difference between versions 7.0 and 2000 is that several reserved keywords were introduced in SQL Server 2000 that can result in compatibility problems with version 7.0 databases.

NOTE

The number of enhancements and differences between SQL Server 2000 and the 6.x versions are significant and much too numerous to detail here. SQL Server Books Online provides a very detailed listing of the backward compatibility issues. It is strongly encouraged that you review this section of Books Online so that you are aware of any possible changes that might cause problems with existing applications.

One of the key differences in the 6.x versions and SQL Server 2000 is the handling of NULLs and string data in your T-SQL code. These differences often don't lead to errors, but can result in incorrect query results.

For example, in the 6.x versions of SQL Server, when you concatenate a NULL value with a character string, the query returns the character string. The ANSI standard states it should return a NULL , so the default behavior in SQL Server 7.0 and 2000 is to return NULL . Needless to say, this can wreak havoc with SQL code that is expecting the old behavior. To get 7.0 or SQL Server 2000 to mimic the old behavior, you need to set the database compatibility level to version 6.5 with the sp_dbcmptlevel dbname , 65 statement, or turn the new behavior off with the SET CONCAT_NULL_YIELDS_NULL OFF command.

In addition, with ANSI NULL compatibility, when equality operators are used on NULL values, the result is unknown . In previous versions of SQL Server, an operator such as @z <> 0 would return true if @z was null. Under ANSI NULL compatibility, the operator will resolve to unknown, causing procedural logic to behave differently. Any code that generates any sort of = NULL or <> NULL logic should be replaced with the ANSI-compliant IS NULL or IS NOT NULL syntax.

Table 8.8 provides a summary of some of the other key Transact-SQL compatibility issues between 6.x and SQL Server 2000.

Table 8.8. Key Transact-SQL Compatibility Issues Between SQL Server 6.x and 2000

SQL Server 6.x Behavior	SQL Server 2000 Behavior
The resultsets of SELECT statements with a GROUP BY clause are sorted by the GROUP BY columns .	A GROUP BY clause does not guarantee a sort order for the results. An ORDER BY clause must be explicitly specified for SQL Server to sort any resultset.
Columns can be prefixed with table aliases in the SET clause of an UPDATE statement.	Table aliases are not accepted in the SET clause of an UPDATE statement.
Warnings for invalid object names are generated when the batch is parsed or compiled, and an error message is returned when the batch is executed.	Error messages are only returned when the batch is executed.
Empty strings ('') are interpreted as a single blank.	Empty strings ('') are interpreted as empty strings.
DATALENGTH('') returns 1.	DATALENGTH('') returns 0.
LEFT('123', m ) returns NULL when m = 0 .	LEFT('123', m ) returns an empty string when m = 0 .
LTRIM(' ') returns NULL .	LTRIM(' ') returns an empty string.
REPLICATE('123', m ) returns NULL when m = 0 .	REPLICATE('123', m ) returns an empty string when m = 0 .
RIGHT('123', m ) returns NULL when m = 0 .	RIGHT('123', m ) returns an empty string when m = 0 .
RIGHT('123', m ) returns NULL when m is negative.	RIGHT('123', m ) returns an error when m is negative.
RTRIM(' ') returns NULL .	RTRIM(' ') returns an empty string.
SPACE(0) returns NULL .	SPACE(0) returns an empty string.
SUBSTRING('123', m , n ) returns NULL when m < length of the string or when n = 0 .	SUBSTRING('123', m , n ) returns an empty string when m < length of the string or when n = 0.
The CHARINDEX and PATINDEX functions return null only if both the pattern and the expression are NULL .	The CHARINDEX and PATINDEX functions return NULL when any input parameters are NULL .

NOTE

It probably goes without saying, but you should not upgrade your production environments until you've applied the upgrade to a test or development environment first and run it through full QA cycle to identify and work out any compatibility issues that might affect your applications.

Setting Database Compatibility Level

To help ease the upgrade process, SQL Server 2000 databases provide the ability to set a database compatibility level. This capability allows you to mimic pre-2000 database behavior in SQL Server 2000 databases. Currently, SQL Server 2000 supports four compatibility levels:

• 80 ”SQL Server 2000 compatibility

• 70 ”SQL Server 7.0 compatibility

• 65 ”SQL Server 6.5 compatibility

• 60 ”SQL Server 6.0 compatibility

When you upgrade a database from SQL Server 7.0 to SQL Server 2000, the default compatibility level is set to 80. When you upgrade a SQL Server 6.5 or SQL Server 6.0 to SQL Server 2000, the original server compatibility level is retained.

In addition to supporting prior version behavior, certain newer behaviors and T-SQL syntax are not enabled at the lower compatibility levels. For example, the LEFT OUTER JOIN clause is not allowed if the compatibility level is set to 60. User -defined functions and indexed views cannot be created in a database if the database compatibility level is less than 80.

You can change the database compatibility level for a database using the sp_dbcmptlevel system procedure. The syntax is as follows :

 EXEC sp_dbmptlevel  dbname  [, {60  65  70  80}] 

If you execute the procedure without specifying a compatibility level, the current compatibility level for the database is returned.

The database compatibility level can also be viewed or set in SQL Enterprise Manager by right-clicking on a database and choosing the Properties option. In the Database Properties dialog box, click on the Options tab to display or set the database compatibility level.

Although the various SET options related to ANSI behavior and the ability to set compatibility levels help ease the migration process from earlier versions of SQL Server, it is strongly encouraged that you become familiar with and begin using the newer ANSI standard features in SQL Server. This is a good idea for two primary reasons:

• It allows your application code to take advantage of new capabilities available only with some of the ANSI SQL commands.

• Support for the older style syntax and behavior might no longer be supported in future releases of SQL Server.

For more information on the various ANSI-compatible SET options and how they affect your T-SQL code, see Chapter 26, "Using Transact-SQL in SQL Server 2000."

Setting Backward-Compatible Collation

One other key item to consider during an upgrade is the collation sequence that you are coming from and what you want it to be in the SQL Server 2000 instance. The Upgrade Wizard will inform you of your options along the way. The thing to keep in mind is how your users and programs are expecting to see the order of the result rows from queries they were using in older SQL Server versions (dictionary order, case insensitive for use with 1252 Character Set versus Latin1_General, and so on). There is a backward-compatibility option that can be specified during this process as seen in Figure 8.8

Figure 8.8. Backward-compatible collation sequence.