Does upgrading the database compatibility level post migration result in any breaking results?
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?
Marcee last edited by
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 https://www.microsoft.com/en-us/download/details.aspx?id=100809 . 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.