Microsoft SQL Server 7.0 System Administration Training Kit

As part of your upgrade plan, you should analyze the software requirements and address any upgrade considerations. You must also perform certain tasks to prepare the SQL Server 6.x database for the upgrade process. This lesson describes how to prepare for the upgrade.

After this lesson, you will be able to

• Describe the upgrade process
• Identify and address potential issues before upgrading a database

Estimated lesson time: 15 minutes

The Upgrade Process

The SQL Server Upgrade Wizard automates the process of upgrading SQL Server 6.x databases to SQL Server 7. You can use other tools such as Data Transformation Services or the bcp command-prompt utility to move a production database to SQL Server 7 manually. However, using the SQL Server Upgrade Wizard makes it relatively easy to configure and transfer data, and it accomplishes the upgrade more rapidly than other methods.

Using the SQL Server Upgrade Wizard

The SQL Server Upgrade Wizard can transfer schema, objects, and data as well as logins and database users. It also transfers replication settings, SQL Executive settings (called SQL Server Agent in SQL Server 7), and many SQL Server 6.x configuration options.

The SQL Server Upgrade Wizard does not remove SQL Server 6.x from the computer. After upgrading, you will have two installations of SQL Server and two sets of data. SQL Server 6.x and SQL Server 7 installations are completely separate and independent. You should leave SQL Server 6.x on the computer until you verify the success of the upgrade. Optionally, you can remove SQL Server 6.x devices to save disk space if you use the tape upgrade option.

Switching Between SQL Server 6.x and SQL Server 7

To switch from one version of SQL Server to the other, use the Microsoft SQL Server-Switch application on the Start menu, or run Vswitch.exe in the C:\Mssql7\Binn directory.

CAUTION

Upgrade Considerations

Before running the SQL Server Upgrade Wizard, you should consider these upgrade issues:

Software Requirements

In order to upgrade a SQL Server 6.x database, the SQL Server computer must have the following software installed:

• Microsoft Windows NT Server Enterprise Edition 4.0 with Service Pack 4 (SP4) or later, Windows NT Server 4.0 with SP4 or later, or Windows NT Workstation 4.0 with SP4 or later
• SQL Server 6.0 with SP3 or SQL Server 6.5 with SP3 or later
• SQL Server 7

Network Protocols

The SQL Server 6.5 and SQL Server 7 installations must have Named Pipes installed as a network library, even if you are using the tape upgrade option. Both SQL Server 6.x and SQL Server 7 must listen on the default pipe, \\.\pipe\sql\query.

SQL Server Versions to Upgrade

You can upgrade only SQL Server 6.x databases to SQL Server 7. You cannot upgrade SQL Server 4.2 software or databases directly to SQL Server 7. Instead, you must upgrade the SQL Server 4.2 software and databases to SQL Server 6.5 and then upgrade to SQL Server 7.

Disk Space Requirements

In addition to the hard disk space used by SQL Server 7, you should have free disk space amounting to approximately 1.5 times the space used by the existing SQL Server 6.x databases.

You can use the SQL Server Upgrade Wizard to estimate the disk space needed to upgrade the SQL Server 6.x server to SQL Server 7. The wizard can estimate the following space requirements:

• Size of SQL Server 7 databases
• Size of SQL Server 7 logs
• Amount of disk space required for the tempdb database in SQL Server 7

NOTE

Replication and Upgrading

When upgrading enterprise servers involved in replication, you must upgrade the Distributor before you upgrade any other 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

Upgrade Tasks

After you install SQL Server 7, and before you use the SQL Server Upgrade Wizard, you must perform the following tasks:

• Run the Database Consistency Checker (DBCC) on all of the SQL Server 6.x databases to make sure they are in a consistent state, and then back up all of the SQL Server 6.x databases (including the system databases).
• Set the tempdb database in SQL Server 6.x to at least 10 MB. The recommended size for the tempdb database when upgrading is 25 MB.
• Create logins in the master database for all database users and ensure that the default database for each login is a database that is being upgraded. The SQL Server Upgrade Wizard examines the master database when determining which database users and objects to import. If the default database for a login is not being upgraded, that login will not be created in SQL Server 7. Objects in a user database cannot be imported if the login for the object owner is not listed as a user for the 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 set to run automatically at startup may cause the upgrade process to hang.
• 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 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 of the computers involved in the upgrade. (The local system account is sufficient for a one-computer upgrade.)
• Shut down the server by stopping replication and ensuring that the replication log is empty and by shutting down all applications, including all services that depend 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 SQL Server 6.x master database as follows:

• Change references from the earlier server name to the current server name in the SQL Server 6.x master database.
• Update the device file locations in the SQL Server 6.x master database.
• Make sure that all users of the transferred database have logins in the SQL Server 6.x master database.

Lesson Summary

SQL Server 7 has many features designed to make it as easy as possible to upgrade from earlier versions. The SQL Server Upgrade Wizard allows you to upgrade from SQL Server 6.x with minimum work. The process uses the new SQL Server 7 installation and the old SQL Server 6.x installation to copy across all of the old databases and database objects that you select. The process includes the ability to switch back to the old version on the same machine. The upgrade should proceed cleanly if you prepare carefully by checking all of the requirements before you start.