| title | Compatibility Certification | Microsoft Docs | |||||||
|---|---|---|---|---|---|---|---|---|
| ms.custom | ||||||||
| ms.date | 08/26/2019 | |||||||
| ms.prod | sql | |||||||
| ms.reviewer | ||||||||
| ms.technology | install | |||||||
| ms.topic | conceptual | |||||||
| helpviewer_keywords |
|
|||||||
| ms.assetid | 3c036813-36cf-4415-a0c9-248d0a433856 | |||||||
| author | pmasl | |||||||
| ms.author | pelopes | |||||||
| monikerRange | >=sql-server-2016||=sqlallproducts-allversions |
[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx-md]
Compatibility certification allows businesses to upgrade and modernize a [!INCLUDEssNoVersion] database on-premises, in the cloud, and on the edge, eliminating risks of application compatibility.
The same [!INCLUDEssde_md] powers both [!INCLUDEssNoVersion] and [!INCLUDEssazure_md] (including Managed Instance). This shared [!INCLUDEssde_md] means that a user database can be moved seamlessly between on-premises [!INCLUDEssNoVersion] and [!INCLUDEssazure_md], while the application code that executes in the database as [!INCLUDEtsql] continues to work as it would in its source system.
For each new release of [!INCLUDEssNoVersion], the default compatibility level is set to the version of the [!INCLUDEssDE]. But the compatibility level of previous versions is preserved for continued compatibility of existing applications. This compatibility matrix can be seen here. Therefore, an application that was certified to work with a given [!INCLUDEssNoVersion] version was in fact certified to work on that version's default compatibility level.
For example, database compatibility level 130 was the default in [!INCLUDEssSQL15]. Because compatibility levels force specific [!INCLUDEtsql] functional and query optimization behaviors, a database certified to work on [!INCLUDEssSQL15] was implicitly certified on database compatibility level 130. This database can work as-is on a more recent version of [!INCLUDEssNoVersion] (such as [!INCLUDEssCurrent]) and [!INCLUDEssazure_md], as long as the database compatibility level is kept as 130.
This is a fundamental principle for [!INCLUDEmsCoName] [!INCLUDEssazure_md] continuous integration operation model. The [!INCLUDEssde_md] is continuously improved and upgraded in Azure, but because existing databases keep their current compatibility level, they continue to work as designed even after upgrades to the underlying [!INCLUDEssde_md].
Using Compatibility Certification is a valuable approach to database modernization. By certifying based on compatibility level, developers set the technical requirements for an application to be supported on [!INCLUDEssNoVersion] and [!INCLUDEssazure_md], but allow the [!INCLUDEssDEnoversion] to be upgraded as needed by lifecycle policies, as well as leveraging new scalability and performance enhancements that are not code dependant.
Applications lifecycle is decoupled from platform lifecycle, and connecting applications maintain their functional status through upgrades. Besides maintaining [!INCLUDEtsql] functionality, performance is also a risk factor for any upgrade. With changes in the Query Optimizer being introduced in every version, it could be expected to encounter query plan differences between [!INCLUDEssde_md] versions, which translate to risk when there is potential that some changes may be detrimental for a given query. However, Compatibility Certification includes query plan shape protection. Knowing that a given query that is used by an application will have the same query plan shape, for the same database, after it is moved to a new version represents peace of mind in terms of managing upgrade risks. Certifying based on compatibility level, and maintaining a database compatibility level as-is immediately after a [!INCLUDEssde_md] upgrade means that the query optimization model used to create query plans in the new version is the same as it was before the upgrade. For more information about query plan shape protection, see the Using compatibility levels for backward compatibility section in this article.
As long as the application does not need to leverage enhancements that are only available in a higher database compatibility levels, it is a valid approach to upgrade the [!INCLUDEssDEnoversion] and maintain the previous database compatibility level, with no need to recertify an application.
For new development work, or when an existing application requires use of new features such as Intelligent Query Processing, as well as some new [!INCLUDEtsql], plan to upgrade the database compatibility level to the latest available in [!INCLUDEssNoVersion], and certify your application to work with that compatibility level. For more details on upgrading the database compatibility level, see Best Practices for upgrading Database Compatibility Level.
There are several immediate benefits to database certification as a compatibility-based approach rather than a named-version approach:
- Decouple application certification from the platform. Because of its shared [!INCLUDEssde_md], for applications that just need to execute [!INCLUDEtsql] queries, there is no need to maintain separate certification processes for Azure and on-premises.
- Reduce upgrade risks because during database platform modernization, application and database platform layer upgrade cycles can be separated for less disruption, and improved change management.
- Upgrade with no code changes. Upgrading to a new version of [!INCLUDEssNoVersion] or [!INCLUDEssazure_md] can be done with no code changes by keeping the same compatibility level as the source system, and no immediate need to recertify.
- Improve manageability and scalability without any requirement for application changes, using enhancements that are not gated by database compatibility level. These include for example:
- Rich monitoring and troubleshooting improvements, with new System Dynamic Management Views and Extended Events.
- Improved scalability with Automatic Soft-NUMA.
New Databases are still set to the default compatibility level of the [!INCLUDEssde_md] version. But when a database is moved from any earlier version of [!INCLUDEssNoVersion] to a new version of [!INCLUDEssNoVersion] or [!INCLUDEssazure_md], the database retains its existing compatibility level.
Important
Before moving a database to a new version of [!INCLUDEssNoVersion] or [!INCLUDEssazure_md], verify if the database compatibility level is still supported. The database compatibility level support matrix can be seen here.
Upgrading a database with a compatibility level lower than the allowed level (for example, 90 which was the default in [!INCLUDEssVersion2005]), sets the database to the lowest compatibility level allowed (100).
To determine the current compatibility level, query the compatibility_level column of sys.databases.
To upgrade the [!INCLUDEssDEnoversion] to the latest version, while maintaining the database compatibility level that existed before the upgrade and its supportability status, it is recommended to perform static functional surface area validation of the application code in the database (programmability objects such as stored procedures, functions, triggers, and others) and in the application (using a workload trace that captures the dynamic code sent by the application), by using the Microsoft Data Migration Assistant tool (DMA). The absence of errors in the DMA tool output, about missing or incompatible functionality, protects application from any functional regressions on the new target version. For more information, see Overview of Data Migration Assistant.
Note
DMA supports database compatibility level 100 and above. [!INCLUDEssVersion2005] as source version is excluded.
Important
[!INCLUDEmsCoName] recommends that some minimal testing is done to validate the success of an upgrade, while maintaining the previous database compatibility level. You should determine what minimal testing means for your own application and scenario.
Note
[!INCLUDEmsCoName] provides query plan shape protection when:
- The new [!INCLUDEssNoVersion] version (target) runs on hardware that is comparable to the hardware where the previous [!INCLUDEssNoVersion] version (source) was running.
- The same supported database compatibility level is used both at the target [!INCLUDEssNoVersion] and source [!INCLUDEssNoVersion].
Any query plan shape regression (as compared to the source [!INCLUDEssNoVersion]) that occurs in the above conditions will be addressed. Please contact Microsoft Customer Support if this is the case.
The database compatibility level setting affects behaviors only for the specified database, not for the entire server. Database compatibility level provides backward compatibility with earlier versions of [!INCLUDEssNoVersion] in what relates to [!INCLUDEtsql] and query optimization behaviors.
Starting with compatibility mode 130, any new query plan affecting features have been intentionally added only to the new compatibility level. This has been done in order to minimize the risk during upgrades that arise from performance degradation due to query plan changes potentially introduced by new query optimization behaviors.
From an application perspective, the goal should still be to upgrade to the latest compatibility level at some point in time, in order to inherit some of the new features such as Intelligent Query Processing, but to do so in a controlled way. Use the lower compatibility level as a safer migration aid to work around version differences, in the behaviors that are controlled by the relevant compatibility level setting.
For more details, including the recommended workflow for upgrading database compatibility level, see Best Practices for upgrading Database Compatibility Level.
Important
Discontinued functionality introduced in a given [!INCLUDEssNoVersion] version is not protected by compatibility level. This refers to functionality that was removed from the [!INCLUDEssDEnoversion].
For example, the FASTFIRSTROW hint was discontinued in [!INCLUDEssSQL11] and replaced with the OPTION (FAST n ) hint. Setting the database compatibility level to 110 will not restore the discontinued hint.
For more information on discontinued functionality, see Discontinued Database Engine Functionality in SQL Server 2016, Discontinued Database Engine Functionality in SQL Server 2014, and Discontinued Database Engine Functionality in SQL Server 2012.
Important
Breaking changes introduced in a given [!INCLUDEssNoVersion] version may not be protected by compatibility level. This refers to behavior changes between versions of the [!INCLUDEssDEnoversion]. [!INCLUDEtsql] behavior is usually protected by compatibility level. However, changed or removed system objects are not protected by compatibility level.
An example of a breaking change protected by compatibility level is an implicit conversion from datetime to datetime2 data types. Under database compatibility level 130, these show improved accuracy by accounting for the fractional milliseconds, resulting in different converted values. To restore previous conversion behavior, set the database compatibility level to 120 or lower.
Examples of breaking changes not protected by compatibility level are:
- Changed column names in system objects. In [!INCLUDEssSQL11] the column single_pages_kb in sys.dm_os_sys_info was renamed to pages_kb. Regardless of the compatibility level, the query
SELECT single_pages_kb FROM sys.dm_os_sys_infowill produce error 207 (Invalid column name). - Removed system objects. In [!INCLUDEssSQL11] the
sp_dboptionwas removed. Regardless of the compatibility level, the statementEXEC sp_dboption 'AdventureWorks2016', 'autoshrink', 'FALSE';will produce error 2812 (Could not find stored procedure 'sp_dboption').
For more information on breaking changes, see Breaking Changes to Database Engine Features in SQL Server 2017, Breaking Changes to Database Engine Features in SQL Server 2016, Breaking Changes to Database Engine Features in SQL Server 2014, and Breaking Changes to Database Engine Features in SQL Server 2012.
ALTER DATABASE COMPATIBILITY LEVEL
View or Change the Compatibility Level of a Database
Best Practices for upgrading Database Compatibility Level