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:
Number of objects
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
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
Direct access to system tables
Problems already present in user's objects that manifest themselves when upgrading
They recommend running DBCC's on 6.5 databases and fixing problems before upgrading.
You can customize the upgrade in several ways:
Convert only selected databases
should only be used for testing
cannot replicate/sync data between 6.x/7
breaks cross database dependencies
Skip transfer of configuration options
Extend the process with supplemental scripts
before or after certain upgrade steps
"Professional stunt drivers on closed track only"
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.
- Segments don't migrate, you will have to write some custom something if you are using segments (other than the default ones).
- Text must be intact in syscomments
- 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.]
- Since master must be at 7.0 compatibility, the syntax used in any objects in master must conform to 7.0 requirements.
- Computer name and local servername (@@servername) must be the same.
- 6.x tempdb must be at least 10MB
- Stored procs that modify system tables will not be transferred.
- 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).
- 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.]
- Tables created by sa for another user (e.g., CREATE TABLE user1.tab1) cannot be scripted as user1 does not have create table privilege.
- Meaningless 6.x permissions (e.g., select permission on a stored proc) will not be scripted.