Lesson 1: Preparing to Upgrade | MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228

3 4

If you are using either SQL Server 6.5 or 7.0 in production, you will need to plan and prepare to upgrade your existing installation to SQL Server 2000. For this process to be successful and as painless as possible, you first need to understand the upgrade options available to you. This includes determining whether you need to keep your existing installation intact. You also need to determine how to minimize downtime. Next, you need to understand the hardware and software requirements for an upgrade. Finally, you need to prepare your existing installation for the actual upgrade.

After this lesson, you will be able to

Work with multiple versions of SQL Server
Choose the appropriate upgrade process and method
Determine whether any hardware or software upgrades are necessary
Prepare your existing installation for the actual upgrade

Estimated lesson time: 30 minutes

Working with Multiple Versions of SQL Server on the Same Computer

SQL Server 2000 is designed to support multiple versions of SQL Server simultaneously on the same computer. It accomplishes this in two different ways. The first method is through the use of version switching, and the second method is through the use of multiple instances. Through the use of these two methods, SQL Server 6.5, SQL Server 7.0, and SQL Server 2000 can coexist on the same computer, although only two versions may be running at any given time (one of which must be SQL Server 2000).

Version Switching

Version switching allows SQL Server 7.0 or SQL Server 2000 to be installed as the default instance on a computer on which SQL Server 6.5 is already installed. You can then use the Vswitch.exe utility to switch between SQL Server 6.5 and either SQL Server 7.0 or SQL Server 2000 (but not both). Using version switching allows you to switch between default instances, controlling which version of SQL Server (including any version-specific tool) is running as the default instance at any given point in time. It does not allow multiple instances or versions to run simultaneously. The Vswitch utility is available from the Start menu, in the Microsoft SQL Server - Switch program group. It is also available in the \Program Files\Microsoft SQL Server\Mssql\Binn folder.

Note

You cannot version-switch between SQL Server 7.0 and SQL Server 2000. Version switching is available only between SQL Server 6.5 and either SQL Server 2000 or SQL Server 7.0.

Named Instances

Using a named instance allows you to install and run SQL Server 2000 as a named instance on a computer on which either SQL Server 6.5 or SQL Server 7.0 is installed without performing a version upgrade of that installation. This allows you to keep your existing version of SQL Server intact and running while also running SQL Server 2000 on the same computer. Installing SQL Server 2000 as a named instance on a computer on which SQL Server 7.0 is installed is the only way to maintain the default instance of SQL Server 7.0 on that computer. When you install SQL Server 2000 as a named instance on a computer on which SQL Server 7.0 is also installed, all SQL Server 7.0 client tools are upgraded to SQL Server 2000 client tools for all instances.

Note

You can install SQL Server 6.5 or 7.0 only as default instances. Only SQL Server 2000 can be installed as a named instance.

Choosing the Appropriate Upgrade Process and Method

When you decide to upgrade SQL Server 6.5, you have only one upgrade process available. However, you have several methods to choose from. Your need to have SQL Server 6.5 running simultaneously with SQL Server 2000 after the upgrade might determine this decision. When you decide to upgrade SQL Server 7.0, you have two separate upgrade processes to choose between. Your need to migrate server and replication settings, as well as the need to minimize server downtime, might determine this choice. You must also determine whether to use a default or a named instance for your SQL Server 2000 installation. Your need to have multiple versions of SQL Server running simultaneously on the same computer might determine this choice.

SQL Server 6.5

If you are using SQL Server 6.5 and you want to upgrade your installation to SQL Server 2000, you must perform a version upgrade using the SQL Server Upgrade Wizard (this requires SQL Server 6.5 SP5). When you perform a version upgrade, you can choose to upgrade some or all of your user databases along with the system databases. The SQL Server Upgrade Wizard can transfer replication settings, SQL Executive settings, and most server configuration settings. The upgrade process includes built-in recovery methods to restart and resume an upgrade if it fails during the upgrade process. During this upgrade process, SQL Server 6.5 must be offline and unavailable to users. To perform an upgrade from SQL Server 6.5, you must also install SQL Server 6.5 SP5.

Note

If you merely want to migrate your data to SQL Server 2000 rather than upgrade your installation, you can use the DTS graphical tool, use the bulk copy program (Bcp) command-prompt utility, or perform a query between linked servers. These data transfer methods are covered in Chapter 7.

The SQL Server Upgrade Wizard can upgrade a computer running SQL Server 6.5 to SQL Server 2000 using either the local hard drive or a local tape drive. Using the local hard drive gives the best performance and is the most reliable. Use a tape drive only when you do not have sufficient hard drive space. The SQL Server Upgrade Wizard can also upgrade from one computer running SQL Server 6.5 to another computer running SQL Server 2000. Regardless of the method you choose, the SQL Server Upgrade Wizard can only upgrade to the default instance; it cannot upgrade SQL Server 6.5 to a named instance.

After the SQL Server Upgrade Wizard finishes the upgrade, you will have two independent installations of SQL Server with two independent sets of identical data (at least initially). SQL Server 6.5 is not removed during the upgrade process (although all client tools are upgraded). You should verify the success of the upgrade before you remove the SQL Server 6.5 installation.

Note

If you want to have SQL Server 6.5 running simultaneously with the SQL Server 2000 installation after the upgrade is complete (in order to verify the upgrade), you must upgrade SQL Server 6.5 from one computer to another.

SQL Server 7.0

If you are using SQL Server 7.0 and you want to upgrade your installation to SQL Server 2000, you have a choice of two processes. Your first choice is to perform a version upgrade of SQL Server 7.0 to SQL Server 2000 using the SQL Server 2000 Setup program. Version switching back to SQL Server 7.0 after the upgrade is not an option. During the version upgrade process, the Setup program replaces and overwrites your SQL Server 7.0 installation. All program files (including all tools and utilities) are upgraded and all databases are converted to SQL Server 2000. Replication settings, SQL Server Agent settings, and most server configuration settings are retained. However, SQL Server 7.0 Profiler traces and servers registered with SQL Server Enterprise Manager are not retained. During (and after) this upgrade process, SQL Server 7.0 must be offline and unavailable to users.

Your second choice is to use the Copy Database Wizard to perform an online database and associated meta data upgrade of selected SQL Server 7.0 user databases. Meta data information includes such things as logon information and user-specific objects associated with user databases. Performing an online database upgrade allows you to upgrade any or all of your user databases without having to shut down SQL Server 7.0 during the upgrade. In addition, your SQL Server 7.0 installation remains intact after the upgrade. If you plan to use this process, you can install SQL Server 2000 as a named instance on the same computer that is running SQL Server 7.0 as the default instance. In addition, the Copy Database Wizard can upgrade databases from remote servers. Finally, you can also schedule the online database upgrade to occur at a specified time because the Copy Database Wizard creates a DTS package that is scheduled as a job. DTS packages and jobs are covered in Chapter 7.

Note

An online database upgrade does not upgrade SQL Server Agent or server configuration settings, and cannot be used with databases involved in replication.

Replication Issues

When upgrading servers involved in replication, you must upgrade the server functioning as the Distributor first, followed by the Publisher and finishing with the Subscribers. If you update servers in this sequence, you can continue to publish and replicate data during this process even though servers are running different versions of SQL Server. If you are using the immediate updating functionality or are using File Transfer Protocol (FTP), additional upgrade steps are required. Refer to "Replication and Upgrading" in SQL Server Books Online for more detail.

Note

For databases involved in replication, you must perform a version upgrade; on SQL Server 6.5, it must be a single computer version upgrade.

Determining Hardware and Software Requirements

The process of upgrading an existing SQL Server installation to SQL Server 2000 has certain hardware and software requirements, in addition to the hardware and software requirements for installing SQL Server 2000. These requirements differ depending on the version of SQL Server being upgraded, and on the type of upgrade process being performed.

SQL Server 6.5

If you are upgrading a SQL Server 6.5 installation running on Windows NT 4.0 and plan to upgrade on the same computer, you must apply Service Pack 5 or later and Internet Explorer 5.0 or later to Windows NT 4.0 before upgrading to SQL Server 2000. You must also apply SQL Server 6.5 Service Pack 5 or later to your SQL Server 6.5 installation. However, if you are performing a computer-to-computer upgrade, you need only apply SQL Server 6.5 Service Pack 3 or later to your SQL Server 6.5 installation. Regardless of the upgrade method, the SQL Server Upgrade Wizard uses named pipes. SQL Server 6.5 and SQL Server 2000 must be set to use the default pipe (\\.\pipe\sql\query). Finally, upgrading SQL Server 6.5 to SQL Server 2000 requires available hard drive space equal to approximately 1.5 times the size of the SQL Server 6.5 user databases. This additional hard drive space is required only during the upgrade process. You can use the SQL Server Upgrade Wizard to estimate the amount of space required to complete the upgrade.

SQL Server 7.0

If you are upgrading a SQL Server 7.0 installation running on Windows NT 4.0 and plan to perform a version upgrade, you must apply Service Pack 5 or later and Internet Explorer 5.0 or later to Windows NT 4.0 before upgrading to SQL Server 2000. You do not need to apply any service packs to SQL Server 7.0 prior to upgrading to SQL Server 2000. Performing a version upgrade of SQL Server 7.0 requires the use of named pipes. SQL Server 7.0 and SQL Server 2000 must be set to use the default pipe (\\.\pipe\sql\query). However, an online database upgrade (using the Copy Database Wizard) does not require named pipes; rather, it will use any available Net-Library. Finally, an upgrade of SQL Server 7.0 to SQL Server 2000 does not require any additional hard drive space, although the Copy Database Wizard might require additional space if the database is copied rather than moved.

Preparing for the Actual Upgrade

You must perform the following tasks on your SQL Server installation prior to commencing the upgrade:

Terminate all user activity in the database and obtain exclusive use of all files in the database.
Back up all system and user databases (including master) to ensure recoverability.
Run the appropriate Database Console Commands (DBCC) to ensure database consistency (such as DBCC CHECKDB).
For SQL Server 6.5, set the tempdb system database size to at least 10 MB (25 MB is recommended).
For SQL Server 6.5, verify that the master database has at least 3 MB of free space.
For SQL Server 6.5, verify that the master database contains logon information for all users.
For SQL Server 6.5, disable any startup stored procedures. If you do not disable them, the upgrade process might stop responding.
Disable all jobs.
Close all open applications, particularly all that are dependent on SQL Server.
Stop replication and ensure that the replication log is empty.
Ensure that there is enough hard disk space available to perform the upgrade.
Make sure that you upgrade all databases that have cross-database dependencies at the same time. This will ensure that, for example, logon information for owners of objects with cross-database dependencies will be created, which, in turn, will ensure that their objects can also be created.

Lesson Summary

You can perform a version upgrade of SQL Server 6.5 to SQL Server 2000 on a single computer or between computers. You must upgrade to the default instance; you cannot upgrade to a named instance. When installing SQL Server 2000 on the same computer, you can use the version switch utility to switch between versions to verify the upgrade. You can perform either a version upgrade of SQL Server 7.0 on a single computer or an online database upgrade of one or more databases between instances on a single computer from a remote computer. If you perform an online database upgrade, SQL Server 7.0 remains available to users during the database upgrade process. However, an online database upgrade does not upgrade server settings and cannot be used when replication is involved. Finally, you need to prepare the production databases for the upgrade and make backups to ensure recoverability in case of a failure.