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
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
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
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
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
They recommend running DBCC's on 6.5 databases and fixing problems before
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 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
- 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
- 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.