Considerations for Upgrading the Database Engine
You can upgrade the SQL Server Database Engine to SQL Server 2008. For information about supported upgrade paths, see Version and Edition Upgrades.
The SQL Server Setup program can upgrade an earlier version of SQL Server with minimal user intervention. However, the correct preparation and a familiarity with the upgrade process can make it easier to prevent or solve any issues that occur.
This topic provides the information that 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.
Links to procedural topics for migrating databases to SQL Server.
Considerations for failover clusters.
Post-upgrade tasks and considerations.
Known Upgrade issues
Before upgrading the Database Engine, review SQL Server Database Engine Backward Compatibility. For information about supported upgrade scenarios and upgrade known issues, see Version and Edition Upgrades. For backward compatibility content for other SQL Server components, see Backward Compatibility.
Important
Before you upgrade from one edition of SQL Server to another, verify that the functionality that you are currently using is supported in the edition to which you are upgrading.
Pre-Upgrade Checklist
Upgrading SQL Server from an earlier version is supported by the SQL Server Setup program. You can also migrate databases from previous SQL Server versions. Migration can be from one SQL Server instance to another on the same computer, or from a SQL Server instance on another computer. Migration options include use of the Copy Database Wizard, Backup and restore functionality, use of the SQL Server Integration Services Import and Export Wizard, and bulk export/bulk import methods.
Before upgrading the Database Engine, make sure to do the following:
Review Hardware and Software Requirements for Installing SQL Server 2008.
Review Check Parameters for the System Configuration Checker.
Review Security Considerations for a SQL Server Installation.
Review Version and Edition Upgrades.
Review Migrating Query Plans.
Review the following issues and make changes before you upgrade SQL Server:
When upgrading from a 64-bit edition of SQL Server to a 64-bit edition of SQL Server 2008, you must upgrade Analysis Services before you upgrade the Database Engine.
When upgrading instances of SQL Server where SQL Server Agent is enlisted in MSX/TSX relationships, upgrade target servers before you upgrade master servers. If you upgrade master servers before target servers, SQL Server Agent will not be able to connect to master instances of SQL Server.
Back up all SQL Server database files from the instance to be upgraded, so that you can restore them, if it is required.
Run the appropriate Database Console Commands (DBCC) on databases to be upgraded to ensure that they are in a consistent state.
Estimate the disk space that is required to upgrade SQL Server components, in addition to user databases. For disk space that is required by SQL Server components, see Hardware and Software Requirements for Installing SQL Server 2008.
Ensure that existing SQL Server system databases - master, model, msdb, and tempdb - are configured to autogrow, and ensure that they have sufficient 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 might 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 retains the configuration value for max worker threads. However, we recommend that you change the max worker threads value to 0 before upgrading, to let the Database Engine calculate the optimal number of threads. For more information, see max worker threads Option in SQL Server Books Online.
Quit all applications, including all services that have SQL Server dependencies. Upgrade might fail if local applications are connected to the instance being upgraded.
If you utilize Database Mirroring, see How to: Minimize Downtime for Mirrored Databases When Upgrading Server Instances in SQL Server Books Online.
Upgrading the Database Engine
You can overwrite an installation of SQL Server 2000 or SQL Server 2005 with a version upgrade. If an earlier version of SQL Server is detected when you run SQL Server Setup, all previous SQL Server program files are upgraded, and all data stored in the previous SQL Server instance is preserved. In addition, earlier versions of SQL Server Books Online will remain intact on the computer.
Warning
When you upgrade SQL Server, 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 by using the SQL Server Installation Wizard.
Warning
If your computer has SQL Server 2000 Management Tools and a default instance of SQL Server 2008 installed, SQL Server Setup will let you install a default instance of SQL Server 2000. However, doing this will disable the installed instance of SQL Server 2008. Therefore, do not install a default instance of SQL Server when SQL Server 2000 Management Tools and a default instance of SQL Server 2008 already exist on the computer.
Database Compatibility Level After Upgrade
The compatibility levels of the tempdb, model, msdb and Resource databases are set to 100 after upgrade. The master system database retains the compatibility level it had before upgrade, unless that level was less than 80. If the compatibility level of master was less than 80 before upgrade, it is set to 80 after upgrade.
If the compatibility level of a user database was 80 or 90 before upgrade, it remains the same after upgrade. If the compatibility level was 70 or less before upgrade, in the upgraded database, the compatibility level is set to 80, which is the lowest supported compatibility level in SQL Server 2008.
Note
New user databases will inherit the compatibility level of the model database.
Migrating Databases
You can move user databases to an instance of SQL Server using backup and restore or detach and attach functionalities in SQL Server.
Important
A database that has the identical name on both source and destination servers cannot be moved or copied. In this case, it will be noted as "Already exists."
For more information, see Using the Copy Database Wizard or How to: Upgrade SQL Server with the Copy Database Wizard.
After Upgrading the Database Engine
After upgrading the Database Engine, complete the following tasks:
Re-register your servers. For more information about registering servers, see the Registering Servers topic in SQL Server Books Online.
Re-populate full-text catalogs. This operation enhances the performance of your SQL Server installation. For more information, see the sp_fulltext_catalog (Transact-SQL) topic in SQL Server Books Online.
After you upgrade from SQL Server 2000, update statistics on all databases. Use the sp_updatestats stored procedure to update statistics in user-defined tables in SQL Server databases. This step is not necessary for upgrading from SQL Server 2005.
Run DBCC UPDATEUSAGE on all databases to correct any incorrect row or page counts.
Configure the SQL Server installation. To reduce the attackable surface area of a system, SQL Server selectively installs and enables key services and features.
Validate or remove USE PLAN hints that are generated by SQL Server 2005 and applied to queries on partitioned tables and indexes.
SQL Server 2008 changes the way queries on partitioned tables and indexes are processed. Queries on partitioned objects that use the USE PLAN hint for a plan that is generated by SQL Server 2005 might contain a plan that is not usable in SQL Server 2008. We recommend the following procedures after you upgrade to SQL Server 2008.
When the USE PLAN hint is specified directly in a query:
Remove the USE PLAN hint from the query.
Test the query.
If the optimizer does not select an appropriate plan, tune the query, and then consider specifying the USE PLAN hint with the desired query plan.
When the USE PLAN hint is specified in a plan guide:
Use the sys.fn_validate_plan_guide function to check the validity of the plan guide. Alternatively, you can check for invalid plans by using the Plan Guide Unsuccessful event in SQL Server Profiler.
If the plan guide is not valid, drop the plan guide. If the optimizer does not select an appropriate plan, tune the query, and then consider specifying the USE PLAN hint with the query plan that you want.
A plan that is not valid will not cause the query to fail when the USE PLAN hint is specified in a plan guide. Instead, the query is compiled without using the USE PLAN hint. For more information about query processing on partitioned objects, see Query Processing Enhancements on Partitioned Tables and Indexes.
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
Change History
Updated content |
|---|
In the section "After Upgrading the Database Engine," changed the recommendation to update statistics after upgrading from SQL Server 2000. |