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.

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 Hardware and Software Requirements for Installing SQL Server 2005.
  
• Review Check Parameters for the System Configuration Checker.
  
• Review Security Considerations for a SQL Server Installation.
  
• Review Using Upgrade Advisor to Prepare for Upgrades.
  
• Review SQL Server 2005 Database Engine Backward Compatibility.
  

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.
  

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.

Caution:
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).

Caution:

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

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

• Using the Copy Database Wizard
  
• How to: Upgrade to SQL Server 2005 with the Copy Database Wizard
  

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 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

Concepts

Other Resources

Help and Information