INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Upgrade to SQL Server 2000

What Should I Expect When Upgrading to SQL 2000? by tlbroadbent
Posted: 12 Apr 02 (Edited 13 Dec 02)

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.
  • 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.

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

  • 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.

  • 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.

  • Expect a few Views, Stored Procedures and application queries to fail with various problems.

    • 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


    • 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.


    • 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.


    • 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
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


Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close