SQL Server databases have had a compatibility level in them for a long time. This is a number that essentially corresponds to a version. We had 70 for version 7.0, and then we went to 80 for SQL Server 2000, 90 for 2005, and we currently are at 150, which you just have to know maps to 2019. I really miss real version numbers.
In any case, there is this statement on the Compatibility Certification page that says this: ” As long as the application does not need to leverage enhancements that are only available in a higher database compatibility level, it is a valid approach to upgrade the SQL Server Database Engine and maintain the previous database compatibility level, with no need to recertify an application. ”
That’s a good statement, but it feels soft to me. This doesn’t really seem to me that Microsoft wants to stand behind the compatibility level as a guarantee that the way all code works on SQL Server 2017 with compat level 140, is the way it will work on SQL Server 2021 with compat level 140. That pages does say that the queries display the same behavior, and query shapes are protected, but what I, and most people, want to know is that our application will run the same.
What about statistics behavior? What about encryption algorithms (that’s already been an issue)? What about the Cardinality Estimator? In my mind, there are still lots of unknowns, and while I appreciate MS moving in this direction, I’m not sure they are as confident as they’d like you to be in their statement.
I’m sure some of you have used the database compatibility level to upgrade an instance, but keep a database behaving as it would on the prior version. If that’s worked well, let us know. If that caused issues, what did you do or was there a workaround? Trace flags, perhaps some database or instance settings might help, and if they did, I’d like to know. In fact, I think Microsoft would like to know, so leave a comment on how you feel, with our without details.
Moving forward, would you consider upgrading the engine, without much testing, and assume that your application will still work well? Do you think vendors would provide support with a different version, but the right compat level? I’m not sure they will.