Događaji
M03 31 23 - M04 2 23
Najveći događaj učenja jezika SQL, platforme Fabric i platforme Power BI. 31. mart - 2. april Koristite kod FABINSIDER da uštedite 400 dolara.
Registrirajte se danasOvaj preglednik više nije podržan.
Nadogradite na Microsoft Edge da iskoristite najnovije osobine, sigurnosna ažuriranja i tehničku podršku.
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.
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.
Važno
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.
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.
There are several immediate benefits to database certification as a compatibility-based approach rather than a named-version approach:
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.
Važno
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.
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.
Savjet
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.
Bilješka
DMA supports database compatibility level 100 and above. SQL Server 2005 (9.x) as source version is excluded.
Važno
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.
Važno
Microsoft provides query plan shape protection when:
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.
Događaji
M03 31 23 - M04 2 23
Najveći događaj učenja jezika SQL, platforme Fabric i platforme Power BI. 31. mart - 2. april Koristite kod FABINSIDER da uštedite 400 dolara.
Registrirajte se danasObučavanje
Modul
Evaluate strategies for migrating to Azure SQL - Training
Evaluate strategies for migrating to Azure SQL
Certifikacija
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.
Dokumentacija
Change the database compatibility level and use the Query Store - SQL Server
This article explains the place of using the Query Store to establish a baseline and changing the database compatibility level in a SQL Server upgrade.
Supported version and edition upgrades (SQL Server 2022) - SQL Server
The supported version and edition upgrades for SQL Server 2022.
Supported version and edition upgrades (SQL Server 2019) - SQL Server
The supported version and edition upgrades for SQL Server 2019.