Does upgrading the database compatibility level post migration result in any breaking results?

Question

Link: https://learn.microsoft.com/en-us/sql/database-engine/install-windows/change-the-database-compatibility-mode-and-use-the-query-store?view=sql-server-ver15

I am migrating sql server from 2016 to 2019. I understand that, by default, post migration the databases will retain the existing compatibility level. Upgrading the compatibility level enables new features.

Microsoft recommends following below approach to upgrade the compatibility level to avoid regression:

Does the compatibility level upgrade result in any breaking results? Or is query regression (which I think means query taking more time than usual due to bad execution plan selection) the only negative side effect of the compatibility upgrade?

Answer 1

Query performance regression is the main concern, and the reason why Microsoft recommends first enabling Query Store before changing Compatibility Levels. This allows Query Store to build a performance baseline of your main queries before you change the Compatibility Level to be able to compare after when you change it so that it can provide you information and metrics on which queries regressed (got slower). As the docs mention, you can then use Query Store to force the "last known good plan" as a short-term fix, while you analyze how to optimize that query with a better long-term fix.

Of course it's always possible to run into bugs from changing the Compatibility Level, but I think that's less likely than running into a bug from the actual instance version upgrade. For example, when I upgraded from 2016 to 2019 RTM recently, I ran into a bug with ownership chaining with a Function that was being called across a Linked Server. That was resolved by installing the latest CU at the time which is currently CU 15. So it's very important you patch your 2019 instance to the latest CU to minimize any bugs.

Also this is why it is recommended to do a version upgrade in your development server first, wait a few weeks or months to analyze the outcome, and then apply the same upgrade in production after sufficient testing.

Answer 2

I'd also like to add to the other replies, since you are going from 2016 to 2019, that there has since 2016

• Only performance related changes covered by database compatibility level. I.e., no functional changes. See this and this.
• Pretty minor breaking changes between 2016 and 2017, and no breaking changes between 2017 and 2019.
• The only discontinued features since 2016 are three performance setting related database scoped configurations.

Above are, of course, only what is documented.

Answer 3

Yes, upgrading the compatibility level can cause breaking results.

Here's an excerpt from the Using compatibility level for backward compatibility doc:

Breaking changes introduced in a given SQL Server version may not be protected by compatibility level. This refers to behavior changes between versions of the SQL Server Database Engine. Transact-SQL 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 SQL Server 2012 (11.x) 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_info will produce error 207
  (Invalid column name).
• Removed system objects. In SQL Server 2012 (11.x) the sp_dboption was removed. Regardless of the compatibility level, the statement EXEC sp_dboption 'AdventureWorks2016', 'autoshrink', 'FALSE'; will produce error 2812 (Could not find stored procedure 'sp_dboption').

Relevant content: Discontinued database engine functionality in SQL Server.

Answer 4

Yes, compatibility level can also have consequences on the applications in some edge cases.I have a legacy application that uses MSAccess as a frontend to do data entry on linked SQLServer tables. If the tables (on SQLServer) don't have a timestamp column MSAccess uses doing a column by column comparison as a concurrency check. This mechanism becomes unreliable when changing the compatibility level from 120 to 140. Adding a timestamp column solves the problem, but this is not always possible.