Jaa

Upgrading the Database Engine

  • Artikkeli

You can upgrade the Microsoft SQL Server Database Engine from Microsoft SQL Server version 7.0 or Microsoft SQL Server 2000 to Microsoft SQL Server 2005. For information about supported upgrade paths, see Version and Edition Upgrades.

The SQL Server 2005 Setup program can upgrade a previous version of SQL Server with minimal user intervention. However, proper preparation and a familiarity with the upgrade process can make it easier to prevent or solve any issues that arise.

This topic provides the information you will need to prepare for and understand the upgrade process; it covers:

  • Known upgrade issues.
  • Pre-Upgrade tasks and considerations.
  • Links to procedural topics for upgrading the Database Engine to SQL Server 2005.
  • Links to procedural topics for migrating databases to SQL Server 2005.
  • Considerations for failover clusters.
  • Post-upgrade tasks and considerations.

Known Upgrade issues

Before upgrading the Database Engine to SQL Server 2005, review SQL Server 2005 Database Engine Backward Compatibility. For backward compatibility content for other SQL Server components, see Backward Compatibility.

Important

Before upgrading from one edition of SQL Server 2005 to another, verify that the functionality you are currently using is supported in the edition to which you are upgrading. For more information, see the section for your components in Features Supported by the Editions of SQL Server 2005.

Pre-Upgrade Checklist

Upgrading to SQL Server 2005 from a previous SQL Server version is supported by the SQL Server 2005 Setup program. You can also migrate databases from previous SQL Server versions to an instance of SQL Server 2005. Migration can be from one SQL Server instance to another on the same computer, or from a SQL Server instance on another server. Migration options include use of the Copy Database Wizard, Backup and restore functionality, use of the SQL Server 2005 Integration Services Import and Export Wizard, and bulk export/bulk import methods.

Before upgrading the Database Engine, make sure to do the following:

Review documentation related to upgrading to SQL Server 2005:

Review the following issues and make changes, as necessary, before upgrading to SQL Server 2005:

  • When upgrading from a 64-bit edition of Microsoft SQL Server 2000 to a 64-bit edition of Microsoft SQL Server 2005, you must upgrade Analysis Services before upgrading the Database Engine.
  • Back up all SQL Server database files from the instance to be upgraded, so you can completely restore them, if necessary.
  • Run the appropriate Database Console Commands (DBCC) on databases to be upgraded to ensure they are in a consistent state.
  • Estimate the disk space required to upgrade SQL Server components, as well as user databases. For disk space required by SQL Server 2005 components, see Hardware and Software Requirements for Installing SQL Server 2005.
  • Ensure that existing SQL Server system databases - master, model, msdb, and tempdb - are configured to autogrow, and ensure that they have adequate hard disk space.
  • Ensure that all database servers have logon information in the master database. This is important for restoring a database, as system logon information resides in master.
  • Disable all startup stored procedures, as the upgrade process will stop and start services on the SQL Server instance being upgraded. Stored procedures processed at startup time may block the upgrade process.
  • Stop Replication and make sure that the replication log is empty.
  • Upgrading an instance of the SQL Server 2000 Database Engine to SQL Server 2005 retains the configuration value for max worker threads. However, we recommend changing the max worker threads value to 0 before upgrading, to allow the Database Engine to calculate the optimal number of threads. For more information, see max worker threads Option.
  • Quit all applications, including all services with SQL Server dependencies. Upgrade may fail if local applications are connected to the instance being upgraded. For more information, see Troubleshooting an Installation of the SQL Server Database Engine.

Upgrading the Database Engine

You can overwrite an installation of SQL Server 7.0 or SQL Server 2000 with a version upgrade to SQL Server 2005. If a previous version of SQL Server is detected when you run SQL Server 2005 Setup, all previous SQL Server program files are upgraded, and all data stored in the previous SQL Server instance is preserved. In addition, previous versions of SQL Server Books Online will remain intact on the machine. To access previous versions of SQL Server Books Online, see How to: Access Books Online for SQL Server 7.0, or How to: Access Books Online for SQL Server 2000.

Warning

When you upgrade to SQL Server 2005, the previous SQL Server instance will be overwritten and will no longer exist on your computer. Before upgrading, back up SQL Server databases and other objects associated with the previous SQL Server instance.

You can upgrade the Database Engine using the SQL Server Installation Wizard (Setup.exe in User Interface mode) or from the command prompt (Setup.exe).

Warning

If your computer has SQL Server 2000 Management Tools and a default instance of SQL Server 2005 installed, SQL Server Setup will permit you to install a SQL Server 2000 default instance. However, doing so will disable the installed instance of SQL Server 2005. Therefore, do not install a default instance of SQL Server when SQL Server 2000 Management Tools and a default instance of SQL Server 2005 already exist on the computer.

Database Compatibility Level After Upgrade

After an upgrade, SQL Server 2005 automatically sets the database compatibility level to the level of the previous version of SQL Server. Therefore, if you upgrade from SQL Server 7.0 through SQL Server 2000 and then to SQL Server 2005, the compatibility level will be set to SQL Server 2000.

To upgrade the Database Engine to SQL Server 2005

Migrating Databases to SQL Server 2005

You can move user databases to an instance of SQL Server 2005 using backup and restore or detach and attach functionalities in SQL Server 2005.

Important

A database with the identical name on both source and destination servers cannot be moved or copied. In this case, it will be noted as "Already exists."

To migrate user databases to SQL Server 2005

Failover Clustering Considerations when Upgrading the Database Engine

SQL Server 2005 failover clustering provides high-availability support for an entire SQL Server instance. A failover cluster is a combination of one or more physical disks in a Microsoft Cluster Service (MSCS) cluster group, known as a resource group, that are participating nodes of the cluster. The resource group is configured as a virtual server that hosts an instance of SQL Server.

A SQL Server virtual server appears on the network as if it were a single computer, but has functionality that provides failover from one node to another if one node becomes unavailable. For example, during a hardware failure, operating system failure, or planned upgrade, you can configure an SQL Server instance on one node of a failover cluster to fail over to any other node in the disk group. You can use failover clustering to reduce system downtime and provide higher application availability.

To upgrade a SQL Server instance to a SQL Server 2005 failover cluster, the instance being upgraded must be a failover cluster. To upgrade a stand-alone instance of SQL Server to a SQL Server 2005 failover cluster, install a new SQL Server 2005 failover cluster and then migrate user databases from the stand-alone instance using the Copy Database Wizard. For more information on upgrade, see How to: Upgrade to a SQL Server 2005 Failover Cluster Instance (Setup). For more information on database migration, see Using the Copy Database Wizard.

For more information about failover clustering, see Failover Clustering and Installing a Failover Cluster. For more information about upgrading to a SQL Server 2005 failover cluster, see How to: Upgrade to a SQL Server 2005 Failover Cluster Instance (Setup).

After Upgrading the Database Engine

After upgrading the Database Engine to SQL Server 2005, complete the following tasks:

  • After upgrading to SQL Server 2005 from another edition of SQL Server 2005, you must re-apply any hotfix or service pack updates to the upgraded SQL Server instance.
  • Register your servers - Upgrade removes registry settings for the previous SQL Server instance. After upgrading, you must reregister your servers. For more information about registering servers, see the Registering Servers topic in SQL Server 2005 Books Online.
  • Update statistics - To help optimize query performance, we recommend that you update statistics on all databases following upgrade. Use the sp_updatestats stored procedure to update statistics in user-defined tables in SQL Server 2005 databases.
  • Update usage counters - In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. To correct any invalid row or page counts, we recommend that you run DBCC UPDATEUSAGE on all databases following upgrade.
  • Configure your new SQL Server installation - To reduce the attackable surface area of a system, SQL Server 2005 selectively installs and activates key services and features. For more information on how to activate SQL Server 2005 features, see SQL Server Surface Area Configuration.

Any databases that were marked full-text enabled or disabled before the upgrade will maintain that status after upgrade. After the upgrade, the full-text catalogs will be rebuilt and populated automatically for all full-text enabled databases. This is a time-consuming and resource-consuming operation. You can pause the full-text indexing operation temporarily by running the following statement:

EXEC sp_fulltext_service 'pause_indexing', 1

To resume full-text index population, run the following statement:

EXEC sp_fulltext_service 'pause_indexing', 0

See Also

Concepts

Working with Multiple Versions of SQL Server
Upgrading Replicated Databases

Other Resources

Backward Compatibility

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
Changed content:
  • In "After Upgrading the Database Engine," removed bulleted item about repopulating full-text catalogs. The paragraph about full-text catalogs being automatically rebuilt explains the correct behavior.

17 July 2006
New content:
  • Added links to topics for registering servers and repopulating full-text catalogs.