Upgrade Database Engine

Article
10/26/2015

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

Note

When you upgrade to SQL Server 2014 from a prior version of SQL Server Enterprise edition, choose between Enterprise Edition: Core-based Licensing and Enterprise Edition. These Enterprise editions differ only with respect to the licensing modes. For more information, see Compute Capacity Limits by Edition of SQL Server.

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 ServerIntegration Services Import and Export Wizard, and bulk export/bulk import methods.

Before upgrading the Database Engine, review the following:

Review Hardware and Software Requirements for Installing SQL Server 2014.
Review Check Parameters for the System Configuration Checker.
Review Security Considerations for a SQL Server Installation.
Review Supported Version and Edition Upgrades.
Review Use Upgrade Advisor to Prepare for Upgrades.
Review Use the Distributed Replay Utility to Prepare for Upgrades.
Review SQL Server Database Engine Backward Compatibility.
Review Migrate Query Plans.

Review the following issues and make changes before you upgrade SQL Server:

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.
When upgrading from a 64-bit edition of SQL Server to a 64-bit edition of SQL Server 2014, you must upgrade Analysis Services before you upgrade the Database Engine.
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 2014.
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.
Make sure that Replication is current and then stop Replication.
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 use Database Mirroring, see Minimize Downtime for Mirrored Databases When Upgrading Server Instances.

Upgrading the Database Engine

You can overwrite an installation of SQL Server 2005 or later 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 running the SQL Server 2014 setup program, the SQL Server instance is stopped and restarted as part of running the pre-upgrade checks.

Caution

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.

Database Compatibility Level After Upgrade

The compatibility levels of the tempdb, model, msdb and Resource databases are set to 120 after upgrade. The master system database retains the compatibility level it had before upgrade.

If the compatibility level of a user database was 100 or higher before the upgrade, it remains the same after upgrade. If the compatibility level was 90 before upgrade, in the upgraded database, the compatibility level is set to 100, which is the lowest supported compatibility level in SQL Server 2014.

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. For more information, see Copy Databases with Backup and Restore or Database Detach and Attach (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 Use 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 Register Servers.
Re-populate full-text catalogs to ensure semantic consistency in query results.

SQL Server 2014 installs new word breakers for use by Full-Text and Semantic Search. The word breakers are used both at indexing time and at query time. If you do not rebuild the full-text catalogs, your search results may be inconsistent. If you issue a full-text query that looks for a phrase that is broken differently by the word breaker in a previous version of SQL Server and the current word breaker, a document or row containing the phrase might not be retrieved. This is because the indexed phrases were broken using different logic than the query is using. The solution is to repopulate (rebuild) the full-text catalogs with the new word breakers so that index time and query time behavior are identical.

For more information, see sp_fulltext_catalog (Transact-SQL).
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 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 2014. We recommend the following procedures after you upgrade to SQL Server 2014.

When the USE PLAN hint is specified directly in a query:
1. Remove the USE PLAN hint from the query.
2. Test the query.
3. 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:
1. 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.
2. 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.

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;

Share via