Lesson 2: Performing a Version Upgrade from SQL Server 7.0

3 4

When you install SQL Server 2000 on a computer running SQL Server 7.0, you are given the option during setup to upgrade your SQL Server 7.0 installation to SQL Server 2000. After installation, there are several tasks that you should perform to ensure maximum performance from your upgraded installation. Finally, you need to understand that certain items are not upgraded at all and that other items must be upgraded separately.

---

After this lesson, you will be able to

• Perform a version upgrade of SQL Server 7.0 to SQL Server 2000
• Understand the tasks you should perform after the version upgrade is complete
• Manually upgrade the Meta data Services Information Models and repository database

Estimated lesson time: 15 minutes

---

Performing a Version Upgrade

You perform a version upgrade by running the SQL Server 2000 Setup program. When the Setup program detects an installed version of SQL Server, you are given the option to upgrade, remove, or add components to an existing instance of SQL Server on your computer. See Figure 4.1.

Figure 4.1

Upgrade, remove, or add installation option.

To upgrade your SQL Server 7.0 installation, choose the default instance on your computer and then choose to upgrade your existing installation. See Figure 4.2.

Figure 4.2

Upgrade option.

After you choose to upgrade your SQL Server 7.0 installation, you define the authentication mode for the Setup program to use to connect to SQL Server 7.0. The Setup program verifies that it can successfully connect using this connection information (starting SQL Server 7.0 if necessary). Next, you must choose the licensing mode (see Chapter 2). Thereafter, SQL Server 7.0 is upgraded to SQL Server 2000. The system databases are upgraded using a series of scripts and the registry is updated. MDAC 2.6 and client tools are also upgraded at this point, unless a previously installed SQL Server 2000 named instance already performed this task.

Performing Post-Upgrade Tasks

After the Setup program completes the upgrade process, there are a number of tasks that the database administrator should perform. These are:

• Review the SQL Server error logs and the Sqlstp.log file if troubleshooting is necessary.
• Repopulate all full-text catalogs if full-text search is being used. The upgrade process disables all full-text catalogs because of a format change that occurs during the upgrade. Repopulation can be time-consuming, so planning an appropriate amount of time is important. Maintaining full-text catalogs is covered in Chapter 12.
• Update statistics. This process can also be time-consuming on large databases, but using SQL Server 7.0 statistics with SQL Server 2000 could result in poor query performance. Updating statistics is covered in Chapter 12.
• Registering servers. Servers registered with SQL Server Enterprise Manager for SQL Server 7.0 are not registered with the new SQL Server Enterprise Manager for SQL Server 2000 (other than the local instance that was upgraded). Registering servers is covered in Chapter 12.

Manually Upgrading Meta Data Services Tables and the Repository Database

When you upgrade your SQL Server 7.0 installation to SQL Server 2000, the Meta Data Services Information Models required by DTS are not updated as part of this process. You must perform a manual update of the information to save and retrieve DTS package versions to and from Meta Data Services. This update modifies the Meta Data Services table structure to support the new functionality and features available with SQL Server 2000 and preserves existing repository data in the new table structure. The precise commands and syntax for manually upgrading the information model are available in the DTS Information model section of Books Online.

In addition, the repository database used by Meta Data Services must also be upgraded manually to take advantage of the new repository engine 3.0 installed during the upgrade to SQL Server 2000. The precise commands and syntax for manually upgrading the repository database are available in the "Upgrading and Migrating a Repository Database" section of Books Online. DTS packages, Meta Data Services, and the Meta Data Services repository are covered in Chapter 7.

Lesson Summary

The SQL Server 2000 Setup program is used to perform a version upgrade of SQL Server 7.0 to SQL Server 2000. After the upgrade is complete, SQL Server 7.0 is completely replaced by SQL Server 2000. The database administrator should update statistics and repopulate all full-text catalogs if full-text search is being used, to optimize the performance of the upgraded installation. Finally, the information models and the repository used by Meta Data Services must be upgraded manually to take advantage of the new features and functionality provided by SQL Server 2000.