The Gotchas in Upgrading

Before you start an upgrade from SQL Server 6.5 to SQL Server 7.0, take some time to prepare. First, check to make sure your system meets the requirements:

• You must have 10MB of free space on your C drive for temporary files and logs.
• Tempdb on your SQL Server 6.5 database must be at least 10MB.
• The Master database must have 3MB of free space. To verify that you meet this requirement, run
  
  USE Master
  EXEC sp_spaceused @updateusage = 'TRUE'
  
  and verify that the unallocated space is at least 3MB (or 3072KB).
• The syscomments text must be intact for all objects to be migrated to SQL Server 7.0 (e.g., views, stored procedures, defaults, rules).
• If you are upgrading from SQL Server 6.5 on one server to a SQL Server 7.0 installation on a different server, both computers must be in the same administrative domain structure.
• The value for @servername must not be null. If you run SELECT @@servername and it returns null, run exec sp_addserver 'myserver','local' (substitute your server name for myserver).

Before you begin your upgrade:

1. Back up your SQL Server 6.5 installation.
2. If you're using replication, upgrade your distribution server first.
3. Ensure that logins exist for every database user. If you restored a database that originated on another server, run sp_change_users_login on the database first to make sure all users map to valid SQL Server logins.
4. Make sure you have no undistributed transactions. To verify that no undistributed transactions exist, run exec sp_repltrans against the Publisher database. If activity has stopped, no rows are returned. Then, run exec sp_MSDistribution_counter @publisher = 'publisher_servername' on the distribution database for each supported Publisher. The No rows returned message should have 0 in the undelivered_jobs column.

Some other considerations:

1. You must run the upgrade as an administrator on both the 6.5 and the 7.0 machines.
2. Stored procedures that modify system tables are not upgraded.
3. The sp_rename system stored procedure does not change an object's name in syscomments. Therefore, any object you upgrade retains its old name if you haven't dropped or recreated the object after you run sp_rename.

For more information, you can download the Upgrading to Microsoft SQL Server 7.0 white paper at http://www.microsoft.com /sql/70/whpprs/Upgrade.htm.