SQL Servers upgrade from 6.5 to 7.0

Step-by-step manual and checklist

1. Required Windows NT 4.0 SP4
2. IE 4.01 SP1
3. SQL Server 6.5 SP3
4. Required hard drive space = (size of 6.5’s databases) * 1.5 + 170 Mbytes (except “tape” method of upgrade)
5. Old 6.5 and new 7.0 instances of SQL Server must be running under user (non-system) account, under the same in ideal.
6. Tempdb must be at least 10MB size (25MB strongly recommended)
7. @@SERVERNAME must be not NULL (just check)
8. master database must have at least 3MB of free space
9. Ensure that no encrypted or renamed objects in databases (very attractive perspective!)
10. Ensure that no “orphaned” users in DB (which not associated with any logins)
11. Ensure that no mission-critical stored procs, which operate with systables directly (they won’t be updated)
12. Ensure that “open databases” parameter is large enough for actual number of databases
13. Ensure that no read-only databases
14. List all backup-related tasks in msdb
15. Run DBCC CHECKDB, DBCC CHECKCATALOG, DBCC NEWALLOC, DBCC TEXTALL
16. Backup master, msdb, model and users’ databases
17. Make inactive all startup procedures (sp_helpstartup)
18. Run sp_helpsort to determine sort order and charset in old installation
19. Stop MSSQL, MSDTC and SQLExecutive and backup *.dat files in mssql\data
20. Increase disk space by deleting *.rpt, *.bcp, *.dmp etc files from mssql\log
21. Refresh ERD if needed.

1. Run SQL Server 7.0 setup with the same sort order and charset.
2. Run SQL Server Upgrade Wizard as a last step of setup process
3. Suppose Wizard completed successfully ;-)
4. Recreate backup devices and startup parameters (trace flags’ recovery is impossible, I believe)
5. “Switch” to 7.0
6. Apply SQL 7.0 SPLast
7. Test user db applications

This step may be performed after start of using user databases under 7.0 (In reality they will be in 7.0 format, but in 6.5 compatibility mode). Below the most possible TSQL conflicts between 6.5 and 7.0

1. UPDATE works in 6.5 with “mix” of aliases and real table names, SQL 7.0 doesn’t
2. 6.5 allows compatible datatypes for foreign key columns (e.g. char-varchar), 7.0 doesn’t
3. Deferred name resolution: 6.5 requires tables and views are in place when compile dependent views and sp, 7.0 doesn’t (VB 1.0 in action!).
4. Old-style joins *= and =* still supported, but time is coming … They are not in ANSI standard
5. Optimizer hints! (optimizer 100% changed in 7.0)
6. Subsequent runs of the same query in 7.0 may return the same rows in different orders, 6.5 doesn’t
7. 7.0 has forced NULL behavior to ANSI_NULLS ON, 6.5 (guess what?), of course, OFF
8. NULL values in aggregates now issues warning, which may be treated as error by client apps (by stupid Access clients, for example. 99% Access applications I saw before were stupid. Rest 1% were my own apps)
9. Implicit truncation of strings no longer allowed, even in “65” compatibility mode! (it’s certainly a new definition of term “compatibility” from MS)
10. In 6.5, if INSERT caused arithmetic overflow, error message was issued, but row (with NULL) was inserted. In 7.0 message the same, but no rows inserted.
11. 7.0 can operate with empty (‘’) strings, in 6.5 they treated as ‘ ‘ or NULL, depends of moon phase. E.g. in 6.5: DATALENGTH(‘’)=1, RTRIM(‘ ’)=NULL. In 7.0, DATALENGTH(‘’)=0, RTRIM(‘ ’)=’’
12. @@DBTS now updated only after modification of timestamp column, not after any row
13. Good number for resume: Forget the compatibility! It’s a fiction.

1. Set compatibility level for all databases in 7.0 mode (sp_dbcmptlevel db_name,70)
2. Test db applications