Skip to content

Latest commit

 

History

History
116 lines (80 loc) · 18.2 KB

compatibility-certification.md

File metadata and controls

116 lines (80 loc) · 18.2 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords monikerRange
Compatibility Certification
Compatibility certification eliminates risks of application compatibility, which allows you to upgrade a SQL Server database on-premises and in the cloud.
rwestMSFT
randolphwest
05/24/2022
sql
install
conceptual
compatibility [SQL Server], databases
compatibility levels [SQL Server], after upgrade
Database Engine [SQL Server], upgrading
Databases [SQL Server], upgrading
compatibility [SQL Server], certification
compatibility level [SQL Server], upgrades
>=sql-server-2016

Compatibility certification

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

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 [!INCLUDE ssazure-sqldb] (including Azure SQL Managed Instance). This shared [!INCLUDEssDE-md] means that a user database can be moved seamlessly between on-premises [!INCLUDEssNoVersion] and [!INCLUDE ssazure-sqldb], 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 [!INCLUDEsssql16-md]. Because compatibility levels force specific [!INCLUDEtsql] functional and query optimization behaviors, a database certified to work on [!INCLUDEsssql16-md] was implicitly certified on database compatibility level 130. This database can work as-is on a more recent version of [!INCLUDEssNoVersion] (such as [!INCLUDE sssql19-md]) and [!INCLUDE ssazure-sqldb], as long as the database compatibility level is kept as 130.

This is a fundamental principle for [!INCLUDEmsCoName] [!INCLUDE ssazure-sqldb] 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].

This is also how SharePoint Server 2016 and SharePoint Server 2019 certify on [!INCLUDEssNoVersion] and [!INCLUDEssazuremi], allowing you to deploy any [!INCLUDEssDEnoversion] that can use the supported database compatibility levels for those SharePoint Server versions. For more information, see Hardware and software requirements for SharePoint Server 2016 and Hardware and software requirements for SharePoint Server 2019.

Manage upgrade risk with compatibility certification

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 [!INCLUDE ssazure-sqldb], but decouple the application lifecycle from the database platform lifecycle. This allows companies to keep the [!INCLUDEssDEnoversion] upgraded as needed by lifecycle policies, using new scalability and performance enhancements that aren't code dependent, and connecting applications maintain their functional status through upgrades.

The possibilities of adversely affecting functionality and performance are the main risk factors for any upgrade. Compatibility Certification represents peace of mind in terms of managing these upgrade risks:

  • In what relates to [!INCLUDEtsql] behavior, any change means that an application needs to be recertified for correctness. However, the database compatibility level setting provides backward compatibility with earlier versions of [!INCLUDEssNoVersion] only for the specified database, not for the entire server. Keeping the database compatibility level as-is ensures that existing application queries continue to display the same behavior before and after a [!INCLUDEssDE-md] upgrade. For more information about [!INCLUDEtsql] behavior and compatibility levels, see Using compatibility levels for backward compatibility.

  • In what relates to performance, because improvements in the Query Optimizer are introduced with every version, it could be expected to encounter query plan differences between different [!INCLUDEssDE-md] versions. Query plan differences in the scope of an upgrade usually translate to risk, when there is potential that some changes may be detrimental for a given query or workload. In turn, this risk is what usually drives the need for application recertification, which can delay upgrades and pose lifecycle and support challenges.

    Mitigating upgrade risks is why Query Optimizer improvements are gated to the default compatibility level of a new release (in other words, the highest compatibility level available for any new version). Compatibility Certification includes query plan shape protection: the notion that maintaining a database compatibility level as-is immediately after a [!INCLUDEssDE-md] upgrade translates into using the same query optimization model in the new version, as it was before the upgrade, and the query plan shape shouldn't change.

    For more information, see the Why query plan shape? section in this article.

For more information about compatibility levels, see Using compatibility levels for backward compatibility.

Important

For an existing application that was already certified for a given compatibility level, upgrade the [!INCLUDEssDEnoversion] and maintain the previous database compatibility level. There is no need to re-certify an application in this scenario. For more information, see Compatibility levels and Database Engine upgrades later in this article.

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 re-certify your application to work with that compatibility level. For more information on upgrading the database compatibility level, see Best Practices for upgrading Database Compatibility Level.

Why query plan shape?

Query plan shape refers to the visual representation of the various operators that make up a query plan. This includes operators like seeks, scans, joins, and sorts, as well as the connections between them that indicate the flow of data and the order of the operations that must be executed to produce the intended result set. The query plan shape is determined by the Query Optimizer.

To keep query performance predictable during an upgrade, one of the fundamental goals is to ensure the same query plan shape is used. This can be achieved by not changing the database compatibility level immediately after an upgrade, even though the underlying [!INCLUDEssDE-md] has different versions. If nothing else changed in the query execution ecosystem, such as significant changes in available resources, or data distribution in the underlying data, a query's performance should remain unchanged.

However, keeping a query plan's shape isn't the only factor that may have performance implications after an upgrade. If you move the database to a newer [!INCLUDEssDE-md] and also make environmental changes, you may be introducing factors that will have immediate impact on a query's performance, even if the query plan retains the same shape across versions. These environmental changes may include the new [!INCLUDEssDE-md] having more or less memory and CPU resources available, changes to server or database configuration options, or changes to data distribution that affect how a query plan is created. This is why it's important to understand that maintaining the database compatibility level protects against changes in the query plan shape, but offers no protection from other environmental aspects that influence query performance, some of which are user-initiated changes.

For more information, see the Query Processing Architecture Guide.

Compatibility certification benefits

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's 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 [!INCLUDE ssazure-sqldb] can be done with no code changes by keeping the same compatibility level as the source system, and no immediate need to recertify until such time when the application needs to use enhancements that are only available in a higher database compatibility level.
  • Improve manageability and scalability without requiring application changes, using enhancements that aren't gated by database compatibility level. In [!INCLUDEssNoVersion] these include for example:

New databases are still set to the default compatibility level of the [!INCLUDEssDE-md] version. But when a database is restored or attached from any earlier version of [!INCLUDEssNoVersion] to a new version of [!INCLUDEssNoVersion] or [!INCLUDE ssazure-sqldb], the database retains its existing compatibility level.

Important

Before moving a database to a new version of [!INCLUDEssNoVersion] or [!INCLUDE ssazure-sqldb], 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.

Compatibility levels and database engine upgrades

To upgrade the [!INCLUDEssDE-md] 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).

This can be easily done 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. If changes are required to ensure your database will work in the new version, then DMA will allow you to pinpoint where changes are needed, and what workarounds are available. For more information, see Overview of Data Migration Assistant.

Tip

This functional validation is especially important when moving a database from a legacy version (such as [!INCLUDEsql2008r2] or [!INCLUDEssSQL11]) into a new version of [!INCLUDEssNoVersion] or [!INCLUDE ssazure-sqldb], because your application code may be using discontinued [!INCLUDEtsql] that is not protected by database compatibility level. But when moving from a more recent version (such as [!INCLUDEssSQL15]) to [!INCLUDE sssql19-md] or [!INCLUDE ssazure-sqldb], there is no discontinued [!INCLUDEtsql] to worry about. For more information about discontinued [!INCLUDEtsql], see Using compatibility level for backward compatibility.

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.

Important

[!INCLUDEmsCoName] provides query plan shape protection when:

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.

See also