Chapter 3 - Upgrading from an Earlier Version of SQL Server
You can upgrade Microsoft SQL Server version 6.x to SQL Server 7.0 using the SQL Server Upgrade Wizard. The wizard upgrades any or all of your databases, transferring all catalog data, objects, and user data. It also transfers replication settings, SQL Executive settings, and most of the SQL Server 6.x configuration options.
The SQL Server Upgrade Wizard does not support consolidation of databases from multiple SQL Server 6.x installations. If you need to upgrade SQL Server 6.x databases from multiple servers, consolidate all of the SQL Server 6.x databases on one server and then run the wizard to upgrade the consolidated server.
When the upgrade process is complete, two separate installations of SQL Server, including two separate sets of the same data, exist. The SQL Server 6.x and SQL Server 7.0 installations are independent of each other from that point forward.
The SQL Server Upgrade Wizard does not remove SQL Server 6.x from the computer, although it can optionally remove the 6.x devices to save disk space if you are using a tape backup to perform the upgrade. You should leave SQL Server 6.x on the computer until you are sure the upgrade was successful.
Before Upgrading
Before running the SQL Server Upgrade Wizard, consider these upgrading issues:
Hardware and software requirements
Which versions can be upgraded
Estimating the disk space required
Replication
Failover support
In addition, be sure to complete the steps in "Before Upgrading: Checklist" in this volume.
Hardware and Software Requirements for Upgrading
In addition to the hardware and software requirements for an installation, a computer must meet these requirements for an upgrade.
Hardware/software |
Requirements |
|---|---|
Hard disk space1 |
In addition to the hard disk space used by Microsoft SQL Server version 7.0, you need approximately 1.5 times the size of your 6.x databases. |
Operating system1,2 |
Microsoft Windows NT Server Enterprise Edition version 4.0 with Service Pack 4 (SP4) or later. |
SQL Server 6.x |
SQL Server 6.0 with SP3. |
Network protocols |
Named Pipes |
1 When performing a computer-to-computer upgrade, only the computer with SQL Server 7.0 installed must meet the hard disk space and operating system requirements. |
|
|
It is highly recommended that you have the most recent SQL Server 6.x and Windows NT service packs installed before you perform an upgrade.
See Also
In This Volume
Estimating the Disk Space Required for Upgrading
Which Versions Can Be Upgraded
Only Microsoft SQL Server version 6*.x* databases can be upgraded to SQL Server 7.0. You cannot upgrade SQL Server 4.2 software or databases directly to SQL Server 7.0. Instead, you must upgrade the SQL Server 4.2 software and databases to SQL Server 6.5, and then upgrade to SQL Server 7.0.
Estimating the Disk Space Required for Upgrading
Before you perform an upgrade of Microsoft SQL Server version 6*.x* to SQL Server 7.0, you must have enough available disk space. This is important if you intend to perform either a one-computer or a two-computer upgrade.
The SQL Server Upgrade Wizard estimates how much disk space is necessary to upgrade the SQL Server 6*.x* server to SQL Server 7.0. The wizard examines the current SQL Server 6*.x* installation and estimates how much disk space the SQL Server 6*.x* data will occupy in the new SQL Server 7.0 server.
You can estimate:
The size of SQL Server 7.0 databases.
The size of SQL Server 7.0 logs.
The amount of disk space required for tempdb.
Note The SQL Server Upgrade Wizard estimates the disk space required; it cannot give an exact requirement.
Replication and Upgrading
When upgrading enterprise servers involved in replication, you must upgrade the Distributor before upgrading any other servers. Microsoft SQL Server version 7.0 replication is designed to support existing SQL Server 6.5 Publishers and Subscribers automatically, in addition to SQL Server 7.0 replication servers. You can phase in the conversion of the servers in your replication topology by upgrading the Distributor first and then upgrading other servers as time and resources permit.
Note You cannot use all of the new replication features until you have upgraded all of the servers involved in your replication topology.
See Also
In Other Volumes
"Replicating Between Different Versions of SQL Server" in Microsoft SQL Server Distributed Data Operations and Replication
Upgrading a Server Involved in Failover Support
If you are using failover support for Microsoft SQL Server that works in conjunction with Microsoft Windows NT Clustering Services, refer to the SQL Server failover support documentation for specific information on upgrading SQL Server version 6.5 to SQL Server 7.0.
See Also
In Other Volumes
"SQL Server Failover Support" in Microsoft SQL Server Administrator's Companion
Upgrading a Custom Sort Order
Microsoft SQL Server version 7.0 does not support custom sort orders. Defining a self-consistent set of sorting rules for a character set can be difficult, and the introduction of Unicode data, with its exponentially larger set of characters, makes this even more challenging.
It is recommended that you use Unicode data types when possible in SQL Server 7.0. In addition to offering an expanded range of characters of Unicode data, a Unicode collation may be compatible with your SQL Server 6.x custom sort order. Unicode collations are equivalent to Microsoft Windows NT collations for the same locale, and these vary from the SQL Server 6.x sort orders in some instances. Also, the case, accent, width, and kana-sensitivity can be changed for each collation.
If you use a custom sort order with your SQL Server 6.x installation, evaluate what benefit you get from the custom sort order that you do not get from a SQL Server 7.0 sort order and Unicode collation. You may be able to switch without any complications. If the custom sort order presents important benefits, you may be able to compensate programmatically in your applications.
The SQL Server Upgrade Wizard can usually upgrade a custom sort order to the nearest equivalent. However, upgrading from a custom sort order can cause problems if either of these two conditions are true:
The nonuniqueness of your data depends on the custom sort order.
If two strings compare as not equal in the custom sort order but as equal in the SQL Server 7.0 sort order, you may violate a uniqueness constraint. If the two strings exist in a SQL Server 6.x database in a unique index, the upgrade will fail.
An application depends on the custom sort order.
The sort order may affect how well data is presented, but most applications do not depend on a particular sort order in order to function correctly. An application that fails without the custom sort order in place must be changed to programmatically account for the new sort order.
See Also
In This Volume
Character Set
Sort Order
Unicode Collation
Before Upgrading: Checklist
The following is a checklist for upgrading:
Install Microsoft SQL Server version 7.0.
Back up your SQL Server 6*.x* database files (all .dat files, including master) so that you can completely restore them if necessary.
Run the appropriate database consistency checks (DBCC) on the SQL Server 6.x databases to make sure they are in a consistent state.
Set tempdb to at least 10 MB in your SQL Server 6.x installation (25 MB recommended.)
Ensure that all database users have logins in the master database.
This is important for restoring a database because system logins reside in the master database.
Disable any startup stored procedures.
The SQL Server Upgrade Wizard starts and stops the SQL Server 6.x server during the upgrade process. Stored procedures processed at startup may cause the upgrade process to hang.
Ensure that you upgrade all databases with cross-database dependencies at the same time.
During the version upgrade process, the SQL Server Upgrade Wizard cannot create a login in SQL Server 7.0 for any 6.x logins using default databases that do not exist in SQL Server 7.0.
For example, you are upgrading three databases, database1, database2, and database4, and there is a login in SQL Server 6.x master..sysdatabases for USER1 that defaults to database3 (not one of the databases you are upgrading). The SQL Server Upgrade Wizard does not create the login because the database is not being upgraded and therefore does not exist in SQL Server 7.0. If USER1 is listed as the owner for objects in any of the databases being upgraded, those objects cannot be created because the login for USER1 does not exist.
If you are performing a two-computer upgrade, assign a domain username and password to the MSSQLServer service in your SQL Server 6.x and SQL Server 7.0 installations instead of using the local system account or a local user account. The domain user account should belong to the Administrators group of both the computers involved in the upgrade. (The local system account is sufficient for a one-computer upgrade.)
Stop replication and make sure that the log is empty.
Shut down all applications, including all services dependent on SQL Server.
If you copied your SQL Server 6.x databases to a new computer to perform the upgrade, you may need to update the new 6.x master database.
Change references from the earlier server name to the current server name in the 6.x master database.
Update the device file locations in the SQL Server 6.x master database.
Make sure all users have corresponding logins.
Upgrade Options
The SQL Server Upgrade Wizard presents you with several options for upgrading Microsoft SQL Server version 6.x databases to SQL Server 7.0. These options depend on how you install SQL Server 7.0 and determine what other options are presented. Familiarize yourself with all of the options before you run the SQL Server Upgrade Wizard.
Data Transfer Method
You can perform an upgrade using either of the following data transfer methods:
Named pipe (if you have ample disk space)
A direct pipeline enables the SQL Server Upgrade Wizard to transfer data in memory from Microsoft SQL Server version 6*.x* to SQL Server 7.0. This data transfer method is the most reliable and provides the best performance. However, when performing a one-computer upgrade, you cannot reuse the disk space occupied by the SQL Server 6*.x* devices until the version upgrade process is complete.
Tape (if you are low on disk space)
The SQL Server Upgrade Wizard backs up to tape all of the SQL Server 6.x databases you have selected to upgrade. The SQL Server Upgrade Wizard then optionally deletes all of the SQL Server 6.x devices, freeing disk space before the SQL Server 7.0 data files are created.
Important The SQL Server Upgrade Wizard deletes all of the SQL Server 6.x devices, not just the ones that are being upgraded. You should upgrade all databases if you choose to delete the SQL Server 6.x devices.
The tape backup option should be used only when you want to upgrade on a single computer but there is not enough space on the hard disk to install SQL Server 7.0 alongside SQL Server 6*.x* and perform the version upgrade.
Note The SQL Server Upgrade Wizard always uses a named pipe, even when performing a tape backup upgrade. SQL Server 6.x and SQL Server 7.0 must be set to listen to the default named pipe, \\.\pipe\sql\query.
Upgrade Verification Options
The transfer of objects and data by the SQL Server Upgrade Wizard is a very reliable process. If any objects could not be imported due to errors in those objects or compatibility problems with Microsoft SQL Server version 7.0, they are noted in the output logs of the SQL Server Upgrade Wizard.
The SQL Server Upgrade Wizard also offers the following optional verification measures:
Validation of successful object data transfer
The SQL Server Upgrade Wizard examines the SQL Server 6.x databases before the upgrade process and the SQL Server 7.0 databases after the upgrade. For each, the wizard prepares a list of all objects, including schema and stored procedures, and the number of rows in each table. It then compares the two lists and reports any discrepancies.
Exhaustive data integrity verification
The SQL Server Upgrade Wizard performs a checksum for each column of each table before and after the upgrade to verify that data values have not changed.
Note The SQL Server Upgrade Wizard does not report as errors any differences in objects that are there by design. If some objects, typically stored procedures, could not import due to errors in the objects or compatibility problems with SQL Server 7.0, they are reported twice: once in the SQL scripts that show the source code of the objects and the error messages received from SQL Server 7.0 when trying to create them, and then again in the output of the verification processes.
Tape Upgrade Options
When you perform a tape backup, you must select a tape drive and choose how the SQL Server Upgrade Wizard handles backing up and deleting objects in the Microsoft SQL Server version 6.x databases.
Device for Data Transfer
The SQL Server Upgrade Wizard transfers all of the data you are upgrading to this tape drive before the SQL Server version 7.0 databases are created.
Backing Up the 6. x Devices
You may also choose to back up the SQL Server 6.x devices. This is separate from the transfer to tape that the SQL Server Upgrade Wizard uses to complete the upgrade. There are two options for backing up the devices.
Prompt me to backup my devices manually
Before data is exported, the SQL Server Upgrade Wizard pauses and prompts you to perform a backup. The SQL Server Upgrade Wizard does not perform a backup for you. You must use a backup utility like Microsoft Windows NT Backup.
Automatically copy device files to the following location
Before data is exported, the SQL Server Upgrade Wizard copies the device files to a shared network directory.
Warning If you back up the devices to tape, remove the tape backup and insert a blank tape before continuing. Before the SQL Server Upgrade Wizard begins transferring data to the tape drive, it formats the tape in the drive. If you do not remove your tape backup, the SQL Server Upgrade Wizard overwrites it.
Deleting the 6. x Devices
If you decide to delete your SQL Server 6.x devices before creating the SQL Server 7.0 databases, you can choose whether to be prompted before the devices are deleted. All of the SQL Server 6.x device files will be deleted if you choose to delete devices, even if you are upgrading only one database. This will render the SQL Server 6.x server unusable until the files are restored.
Note If you choose not to delete the devices, then you must have enough disk space for both the SQL Server 6.x and SQL Server 7.0 databases. If sufficient space is available, you should use a Named Pipe upgrade instead of a Tape upgrade.
One-computer vs. Two-computer Upgrade
The upgrade process can take place on a single computer or from one computer to another, depending on where Microsoft SQL Server version 6.x and SQL Server 7.0 are installed. The SQL Server Upgrade Wizard identifies the two servers as follows:
Export server
The name of your SQL Server 6.x server. This defaults to the name of the computer on which the SQL Server Upgrade Wizard is being run, but may be changed if your 6.x server is on another computer.
Import server
The name of your SQL Server 7.0 server. This is always the name of the computer on which the SQL Server Upgrade Wizard is being run.
For a one-computer upgrade, leave the import and export servers at their default values.
For a two-computer upgrade, select the name of the computer with your 6.x server as the export server. To upgrade SQL Server from one computer to another, the two computers must be in the same network domain.
Important The one-computer upgrade is the only method supported when upgrading a server used in replication. A two-computer upgrade is not supported for replication servers.
Upgrade Scripting Code Page
In some instances, the actual code page in effect for a SQL Server 6.x installation will differ from the code page recorded in the master database. The SQL Server Upgrade Wizard requires a scripting code page, which is used to create the upgrade scripts. The default scripting code page is the code page recorded in the master database. If you know that the actual code page is different from the recorded code page, select the actual code page in the list. Most users only need to accept the default code page.
If you choose a scripting code page other than the default, it is recommended that you do not upgrade replication settings. If the server is involved in replication, reconfigure the replication settings after the upgrade is complete.
Selecting Databases to Upgrade
When running the SQL Server Upgrade Wizard, you can choose to upgrade some or all Microsoft SQL Server version 6.x databases. The master, msdb, and publication system databases, as well as the pubs and Northwind sample databases, are not explicitly available for selection. However, the master, msdb, and publication databases can be selected for upgrading (the default) in the Server Configuration dialog box of the SQL Server Upgrade Wizard.
Note If you run the SQL Server Upgrade Wizard again after databases have been upgraded, previously updated databases will default to the excluded list. If you want to upgrade a database again, drop the database in SQL Server 7.0 and move it to the included list in the wizard.
Database Configuration
Before any data is transferred, the SQL Server Upgrade Wizard creates, if necessary, database and log files large enough to contain the upgraded database data. You have several options for creating the Microsoft SQL Server version 7.0 database and log files.
Using the Default Database Configuration
The SQL Server Upgrade Wizard estimates how much space is necessary to hold transferred objects and data for each selected database and creates database files of the estimated sizes. The wizard makes no allowance for free space beyond the loaded data. By default, the data file for a database is placed in the same location as the first device used by that database in SQL Server 6.x.
The SQL Server Upgrade Wizard also creates a log file for each database using the SQL Server 6.x log size. By default, the log file is placed in the same location as the first device used for log space in SQL Server 6.x.
You can view and edit the default database configuration in the SQL Server Upgrade Wizard. For each database and log file you can modify:
The name and file path.
The initial size of the file.
The autogrow increment.
If the 6.x database(s) used multiple devices, multiple database files will be created in the same locations. However, the first database file will be sized to accommodate the bulk of the data and the other files will be minimally sized. If you want to remove these files you must do so before they are created. All files are set to grow automatically if extra space is required.
Using a Custom Database Configuration
You can specify your own configuration in one of two ways:
Using databases and logs that you created in SQL Server 7.0
The SQL Server Upgrade Wizard does not create any user databases. You must create the necessary databases and logs in SQL Server 7.0 before you start the SQL Server Upgrade Wizard. Use this option only if necessary.
Using an SQL script file that you provide
The SQL Server Upgrade Wizard uses an SQL script file that you provide to create the necessary user databases and logs. Use this option only if you are familiar with the new CREATE DATABASE statement in SQL Server 7.0.
If you create the user databases or an SQL script file, the SQL Server 7.0 databases must have the same names as in SQL Server 6.x. Also, remember that data may take up more disk space in SQL Server 7.0 than in SQL Server 6.x. The SQL Server Upgrade Wizard estimates this growth. Click Edit to view the default configuration to see the estimated initial size of the SQL Server 7.0 databases. It is recommended that you leave the autogrow feature on for each database. You may also want to set a backward compatibility level for each database.
See Also
In This Volume
Estimating the Disk Space Required for Upgrading
Backward Compatibility
In Other Volumes
"CREATE DATABASE" in Microsoft SQL Server Transact-SQL and Utilities Reference
Objects to Transfer
When upgrading the master database, the SQL Server Upgrade Wizard can upgrade several configuration options:
Server configuration
Login and remote login registrations and server configuration options relevant to Microsoft SQL Server version 7.0 are transferred as part of the version upgrade process. The SQL Server 6.x configuration options not used in SQL Server 7.0, or not recommended to be set to nondefault values, are not transferred.
Replication settings
All articles, subscriptions, and publications of each selected database, including the distribution database, if any, are transferred and upgraded.
SQL Executive settings
All tasks scheduled by SQL Executive are transferred and upgraded so that SQL Server 7.0 can schedule and run the tasks in SQL Server Agent.
Note Upgrading replication or SQL Executive settings causes existing modifications that have already been made to the SQL Server 7.0 replication or SQL Server Agent settings to be overwritten.
ANSI Nulls
The ANSI_NULLS option controls both database default nullability and comparisons against null values. When upgrading Microsoft SQL Server version 6.x to SQL Server version 7.0, you must set the ANSI_NULLS option to ON or OFF.
When the SQL Server Upgrade Wizard creates the SQL Server 7.0 database tables, the database default nullability determined by the ANSI_NULLS option is not an issue. All columns are explicitly qualified as NULL or NOT NULL based on their status in SQL Server 6.x.
The ANSI_NULLS option is important with regard to comparisons against null values, when the SQL Server Upgrade Wizard creates the SQL Server 7.0 database objects. With ANSI_NULLS set to ON, the comparison operators EQUAL (=) and NOT EQUAL (<>) always return NULL when one of its arguments is NULL. With ANSI_NULLS set to OFF, these operators will return TRUE or FALSE depending on whether both arguments are NULL.
In SQL Server 6.x, the ANSI_NULLS option in objects, such as stored procedures and triggers, is resolved during query execution time. In SQL Server 7.0, the ANSI_NULLS option is resolved when the object is created. You must choose the ANSI_NULLS option setting you want for all objects in the databases you are upgrading. The SQL Server Upgrade Wizard then creates all database objects using this ANSI_NULLS setting.
See Also
In Other Volumes
"SET ANSI_NULL_DFLT_ON" in Microsoft SQL Server Building SQL Server Applications
"SET ANSI_NULLS" in Microsoft SQL Server Building SQL Server Applications
Quoted Identifiers
The QUOTED_IDENTIFIER setting determines what meaning Microsoft SQL Server gives to double quotation marks ("). When QUOTED_IDENTIFIER is set to OFF, double quotation marks delimit a character string, just as single quotation marks do. When QUOTED_IDENTIFIER is set to ON, double quotation marks delimit an identifier, such as a column name. An identifier must be enclosed in double quotation marks, for example, if its name contains characters that are otherwise illegal in an identifier, including spaces and punctuation, or if the name conflicts with a reserved word in Transact-SQL. Regardless of the QUOTED_IDENTIFIER setting, an identifier can also be delimitted by square brackets.
The meaning of the following statement, for example, depends on whether QUOTED_IDENTIFIER is set to ON or OFF:
SELECT "x" FROM T
If QUOTED_IDENTIFIER is set to ON, "x" is interpreted to mean the column named x. If it is set to OFF, "x" is the constant string x and is equivalent to the letter x.
If the previous SELECT statement example were part of a stored procedure created when QUOTED_IDENTIFIER was set to ON, then "x" would always mean the column named x. Even if the QUOTED_IDENTIFIER setting was later switched, and set to OFF, the stored procedure would act as if it were set to ON and treat "x" as the column named x.
When the SQL Server Upgrade Wizard re-creates database objects in SQL Server version 7.0, the QUOTED_IDENTIFIER setting determines how all of these objects behave. If all database objects were created in SQL Server 6.x with the same QUOTED_IDENTIFIER setting, click that setting, either On or Off. If objects were created in SQL Server 6.x with a mix of the two settings, or if you are unsure of the settings used, click Mixed.
With the Mixed option, the SQL Server Upgrade Wizard first converts all objects containing double quotation marks with QUOTED_IDENTIFIER set to ON. The SQL Server Upgrade Wizard then converts any objects that failed to be created with QUOTED_IDENTIFIER set to OFF.
Switching Between SQL Server 6.x and SQL Server 7.0
While Microsoft SQL Server version 7.0 can be installed alongside SQL Server 6.x on the same computer, only one version can be active at one time. When the SQL Server Upgrade Wizard is complete, SQL Server 7.0 is the active version of SQL Server. If you have enough disk space, you may want to leave SQL Server 6.x on your computer until you are sure you no longer need it.
To switch from one version to the other, use the Microsoft SQL Server-Switch application on the Start menu, or run Vswitch.exe in the \Mssql7\Binn directory.
Important Do not switch between the active and nonactive versions of SQL Server while the SQL Server Upgrade Wizard is running.
Removing SQL Server 6.x
After upgrading the Microsoft SQL Server version 6.x databases to SQL Server 7.0, you may want to leave SQL Server 6.x on your computer until you are sure you no longer need it.
When ready, you can remove SQL Server 6.x by using the Remove SQL Server 6.x application on the Start menu.
If you need to reinstall SQL Server 6.x, you must first remove SQL Server 7.0. SQL Server 6.x cannot be installed on a computer alongside an existing SQL Server 7.0 installation.
Configuring SQL Server After Upgrading
After the server is upgraded, you may need to perform several configuration tasks, such as setting server configuration parameters, setting security parameters, and registering the server and adding it to a server group. Use SQL Server Enterprise Manager or Transact-SQL to perform these tasks.
See Also
In Other Volumes
"Overview of Administering SQL Server" in Microsoft SQL Server Administrator's Companion
"Managing Servers" in Microsoft SQL Server Administrator's Companion