Bewerken

Compatibility certification

  • Artikel

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Compatibility certification allows businesses to upgrade and modernize a SQL Server database on-premises, in the cloud, and on the edge, eliminating risks of application compatibility.

The same Database Engine powers both SQL Server and Azure SQL Database (including Azure SQL Managed Instance). This shared Database Engine means that a user database can be moved seamlessly between on-premises SQL Server and Azure SQL Database, while the application code that executes in the database as Transact-SQL continues to work as it would in its source system.

For each new release of SQL Server, the default compatibility level is set to the version of the Database Engine. 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 SQL Server version was in fact certified to work on that version's default compatibility level.

For example, database compatibility level 130 was the default in SQL Server 2016 (13.x). Because compatibility levels force specific Transact-SQL functional and query optimization behaviors, a database certified to work on SQL Server 2016 (13.x) was implicitly certified on database compatibility level 130. This database can work as-is on a more recent version of SQL Server (such as SQL Server 2019 (15.x)) and Azure SQL Database, as long as the database compatibility level is kept as 130.

This is a fundamental principle for Microsoft Azure SQL Database continuous integration operation model. The Database Engine 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 Database Engine.

This is also how SharePoint Server 2016 and SharePoint Server 2019 certify on SQL Server and Azure SQL Managed Instance, allowing you to deploy any SQL Server Database Engine 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 SQL Server and Azure SQL Database, but decouple the application lifecycle from the database platform lifecycle. This allows companies to keep the SQL Server Database Engine 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 Transact-SQL 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 SQL Server 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 Database Engine upgrade. For more information about Transact-SQL 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 Database Engine 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 Database Engine 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 SQL Server Database Engine 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 Transact-SQL, plan to upgrade the database compatibility level to the latest available in SQL Server, 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 Database Engine 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 Database Engine 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 Database Engine 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 Database Engine, for applications that just need to execute Transact-SQL 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 SQL Server or Azure SQL Database 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 SQL Server these include for example:

New databases are still set to the default compatibility level of the Database Engine version. But when a database is restored or attached from any earlier version of SQL Server to a new version of SQL Server or Azure SQL Database, the database retains its existing compatibility level.

Important

Before moving a database to a new version of SQL Server or Azure SQL Database, 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 SQL Server 2005 (9.x)), 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 Database Engine 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 SQL Server 2008 R2 (10.50.x) or SQL Server 2012 (11.x)) into a new version of SQL Server or Azure SQL Database, because your application code may be using discontinued Transact-SQL that is not protected by database compatibility level. But when moving from a more recent version (such as SQL Server 2016 (13.x)) to SQL Server 2019 (15.x) or Azure SQL Database, there is no discontinued Transact-SQL to worry about. For more information about discontinued Transact-SQL, see Using compatibility level for backward compatibility.

Note

DMA supports database compatibility level 100 and above. SQL Server 2005 (9.x) as source version is excluded.

Important

Microsoft 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

Microsoft provides query plan shape protection when:

  • The new SQL Server version (target) runs on hardware that is comparable to the hardware where the previous SQL Server version (source) was running.
  • The same supported database compatibility level is used both at the target SQL Server and source SQL Server.
  • The same database and workload is used both at the target SQL Server and the source SQL Server.

Any query plan shape regression (as compared to the source SQL Server) that occurs in the above conditions will be addressed. Please contact Microsoft Customer Support if this is the case.

See also