SQL Server 7.0 Version Upgrade - Page 5

Microsoft's goal for upgrading databases is to make it easy, fast, reliable and compatible. If you upgrade a SQL Server 6.5 database to 7.0, it will run in 6.5 compatibility mode. You will be able to turn this off once you are certain (more below) that everything will work OK.

You can specify the level of verification you want when you upgrade. You can not upgrade individual tables.

There is a wizard to guide you through. It does a fully automatic upgrade of your server configuration, security, objects, data, replication settings and tasks. You can also manage this with INI files. (don't know how yet.)

For a single machine upgrade, you must use named pipes. The upgrade will check for disk space as it is going to make a copy of the database, though there is an option to use a tape. You can not upgrade a 6.5 database in place, there must be a copy. You can upgrade 6.0 and 6.5 databases to 7.0. You must upgrade 4.2 databases to 6.x before upgrading to 7.0.

You do not have to upgrade everything at once, and you can stop and restart as you wish. If servers are involved in replication, they can upgrade in any order, with the exception of a separate distribution server which must be upgraded first.

The upgrade is designed to be fast. There is a special export engine and a custom OLE DB driver optimized for high speed import. Timings they have gotten so far include (using a pipeline, not a tape)

Neither server can be in use during the upgrade. The number of databases doesn't influence the time unless you select the detailed verification (CRC check) option.

The upgrade is a CPU intensive task, and they recommend that you get rid of any unneeded objects before upgrading. Factors that influence the speed are:

I/O subsystem

Memory

CPU

Number of objects

Indexes/table, row/table

text/image data

It is reliable. It does not lose or duplicate data and you don't have to start all over in case of a failure. It does not leave the database in an inconsistent state. You can request comparisons of

All objects

Row counts for all tables

CRC checks of all data

Microsoft will have upgraded 1000 different customer databases by ship date.

They are striving to preserve compatibility between 6.0/6.5 databases and provide compatibility modes which

Avoid keyword conflicts

Use implicit order by on group by

6.5 handling of empty strings

Do not require aliases on update statements

The system databases are always in 7.0 mode. The compatibility setting is on a per database basis so you can turn it off one at a time when you are ready.

With Beta 2, they have found that the most common problems are

They recommend running DBCC's on 6.5 databases and fixing problems before upgrading.

You can customize the upgrade in several ways:

Upgrade files will be placed in the MSSQL7\Upgrade directory. There will be a subdirectory for each upgrade performed: MSSQL7\UPGRADE\machinename and in that, a subdirectory for each database: MSSQL7\UPGRADE\machinename\nnnDBNAME.

There will be some stored procs created in the 6.x database to suport the upgrade.

The files in the database directory include the scripts for table definitions, stored procedures, views, triggers, defaults, rules and their column bindings, user-defined data types, DRI, permissions, logins, users and groups. Note that the login password for standard security will NOT be exported. [Users at TechED requested that an option be provided for preserving passwords.] Groups will be migrated to roles.

Upgrade "Gotcha's"

1. Segments don't migrate, you will have to write some custom something if you are using segments (other than the default ones).
2. Text must be intact in syscomments
3. If you have used sp_rename to rename an object, this change is not carried back to syscomments and can cause problems. [I have always urged people not to use this anyway.]
4. Since master must be at 7.0 compatibility, the syntax used in any objects in master must conform to 7.0 requirements.
5. Computer name and local servername (@@servername) must be the same.
6. 6.x tempdb must be at least 10MB
7. Stored procs that modify system tables will not be transferred.
8. System table schema changes between 6.x and 7. (Customers are urged not to interrogate system tables directly in 7.0, many more covering functions will be provided).
9. Stored procs created from within stored procs will not be scripted as they are not in syscomments. [Since one of the rules is that you can't issue a CREATE PROC inside a stored procedure, I am at a loss as to how one would do this anyway.]
10. Tables created by sa for another user (e.g., CREATE TABLE user1.tab1) cannot be scripted as user1 does not have create table privilege.
11. Meaningless 6.x permissions (e.g., select permission on a stored proc) will not be scripted.