What Should I Expect When Upgrading to SQL 2000?

Upgrading from SQL 7 to SQL 2000 is much simpler and usually smoother than the upgrade from SQL 6.5 to 7. Here is a list of outcomes you should expect.[ul][li]Expect the upgrade to go smoothly. Usually it does. We encountered a situation on multi-processor servers where setup failed. We worked around the problem by telling setup to install one processor license instead of four. We later changed to the four licenses we had purchased.

[li]Expect the upgrade to run faster than expected especially if you have experience the 6.5 to 7 upgrade.

[li]Expect setup to handle all the database conversions quickly and smoothly. I've not seen setup fail yet when converting databases. The database conversion is not as extensive as the upgrade from SQL 6.5 to SQL 7.

I recommend allowing Setup to upgrade the databases. Some people prefer detaching user databases before upgrading and then attaching after the setup completes. Attaching SQL 7 databases in SQL 2000 causes them to be upgraded. This technique works well as does restoring SQL 7 databases to SQL 2000.

Note 1: Databases are not backward compatible. You cannot attach or restore a SQL 2000 database in SQL 7.

Note 2: I recommend setting the compatibility level on all databases to 8 (SQL 2000 mode) as soon as possible.

[li]Expect some performance problems. Statistics must updated after the upgrade. Some queries and procedures that performed well before upgrading may be slow. These queries will require analysis. You may need to modify the T-SQL code or alter, create and/or drop indexes.

[li]Expect a few Views, Stored Procedures and application queries to fail with various problems.[ul]
[li]SQL 2000 has tighter syntax checking. Some things that worked in SQL 7 may not compile in SQL 2000. One interesting example is the following syntax which worked in SQL 7 but fails in SQL 2000. Note the two periods!

Select t..ColumnName
From TableName t[/li]

[li]SQL BOL states: A table alias specified in a FROM clause cannot be used as a qualifier in SET column_name. For example, this is not valid:"

UPDATE titles
SET t.ytd_sales = t.ytd_sales + s.qty
FROM titles t, sales s
WHERE t.title_id = s.title_id
AND s.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

To make the example work, remove the t. alias from the column name.[/li]

[li]Sort orders may be different than under SQL 7. You'll need to explicitly order results sets where it may not have been needed previously.[/li]

[li]Old style, non-ANSI outer Joins may fail to produce correct results. ANSI style joins are recommended. See the article "ANSI JOIN vs. OUTER JOIN" at ...

http://www.microsoft.com/sql/techinfo/tips/development/July23.asp[/li][/ul][/ul]SQL Books Online and the following resources are helpful when preparing for the upgrade.

How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000
http://www.microsoft.com/technet/prodtechnol/sql/deploy/upgrdmigrate/sqlugrd.asp

SQL Server 2000 and SQL Server 7.0
http://msdn.microsoft.com/library/en-us/instsql/in_bckwd_3vlc.asp

Preparing to Install SQL Server 2000
http://msdn.microsoft.com/library/en-us/instsql/in_overview_0lm4.asp

SQL Server 2000 Upgrade Guide - 3rd party white paper
http://www.asapsoftware.com/microsoft/sql2000/SQL2kUpgradeGuide.zip