What Compatibility Levels will SQL Server 2022 support?

Zach 21

What Compatibility Levels will SQL Server 2022 support? I know the Database CL will be 160 (new CL level) but what will be the supported CL's?
Judging by historic trend, looks like maybe CL 100 will be dropped for 2022?? That is my main concern.

Accepted answer

Erland Sommarskog 100.9K Reputation points MVP

2022-02-11T22:28:52.043+00:00

SQL 2022 is only in private preview right now, and testing on a current build of SQL 2022 would really say anything any way, since Microsoft may make decisions between now and the official release.

However, I can still offer an answer from gut feeling and that is that compatibility level 100 will remain supported. With release of SQL 2008, Microsoft established that they would support two compat levels back. That introduced some problems when they released SQL 2012 and they no longer supported compat level 80, and moreover did not support reading backups from SQL 2000.

As a consequence, SQL 2019 can read backups from SQL 2005 and later.

When it comes to the compat level, they stayed with the policy for one more version, and SQL 2014 does not support compat level 100. But starting with the release of SQL 2016, they have not dropped any compat levels at all. My gut feeling is that this will not change.

I'd be curious to know why you are anxious to have compat level 100 around?
Please sign in to rate this answer.

1 person found this answer helpful.
Zach 21 Reputation points

2022-02-12T16:04:11.337+00:00

Thank you, Erland. This is an interesting take that I hope comes true. I keep hearing that 100 most likely will be dropped (by looking at the trend), but I was hoping Microsoft would have heard enough negative feedback over the years to not drop supported compat levels.

The software we sell packages a SQL database, call it a "template" per se. All our customers must use this DB to use our product; therefore, we have to choose a compat level that would be a best fit.
Considering we definitely want to support the latest versions of SQL Server, if 2022 drops 100, we would have to change the compat level of our DB to 110, thus loosing support for 2012 which a lot of our customer base still uses.

It wouldn't be the end of the world, but it would have a large customer impact. The type of software industry we are in involves very tight monthly deadlines and upgrading SQL versions is a huge task for many of our larger customers. If we leave our compat level, no one would be forced to upgrade but we wouldn't be able to support 2022, and I believe some of our customers are already gearing up to switch to 2022.

We are planning to to some testing and figure out what best to do.

Erland Sommarskog 100.9K Reputation points MVP

2022-02-12T17:00:01.677+00:00

Compat level 100 corresponds to SQL 2008. SQL 2012 is 110.

Apart from that, I don't really see any restriction. The template database you ship is obviously created on an old version of SQL Server. Say that this version is SQL 2008. When you attach/restore that database on a higher version, the format of the database will be upgraded to the version of the database, but the compatibility level will not be changed as long as that level is supported on the version you attach to.

Thus, let's assume for a second that SQL 2022 will not support compat level 100. When the database is restored on SQL 2022, the compatibility level will be set to 110 (assuming that that level is still supported).

I can't but see that you will still be able to support customers on all versions from SQL 2008 to SQL 2022. The one thing you would need to do is to verify that your code still works with compat level 110.

My recommendation would be that when you install the application is that you always set the compatibility level to the level which is the most recent for the version you are restoring to. This may require that you will have to do some more testing. But this also means that customers on new version can take benefit of performance enhancements in later versions.

I worked with an ISV for many years, and we always had the policy to stick to the highest compatibility level, even if we had to support older SQL Server versions, since some customers were slow to upgrade.

Zach 21 Reputation points

2022-02-12T17:09:09.283+00:00

Wow, much appreciated! I'm not in development, rather I'm in Product Management so I know basics but this information is very helpful. My lead developer wants to upgrade our CL that we release. But you're right, the better option would be to leave it at 100 and test to ensure that if a customer attaches to 2022, and CL hence gets upgraded, that our code still works. The best option, and you mention this as well, is to provide the highest CL so that customers who've upgraded SQL server can actually enjoy the benefits of better performance/security that comes with high server versions.

Sorry to keep asking more questions, but when you say that when you worked for an ISV your policy to was to stick to the highest CL, even if that means supported older SQL versions...if we were to take that approach (which I really want to as some customers are calling us out for using 100) would we have to provide different DB options with different versions and CLs (because SQL DBs/servers are not backwards compatible)? (e.g. If we provide a 2022 DB with CL 160, only clients with 2022 will be able to use it, so we would need to provide at the very least a second option, maybe what we have today, a 2012 DB with CL 100) This option seems like more work but has most benefit to our clients.

Erland Sommarskog 100.9K Reputation points MVP

2022-02-12T17:30:25.103+00:00

Just to clarify: We did not send a template database to the customer, but rather an install packages that built the database from scripts. (This is a high-end system, but with a fairly small set of customers, so each customer gets their install with their configuration.)

I would say it is up to you. You can still use a single template database, but you change the compat level at installation. (I'm assuming that you are having some sort of install package for your product.)

The only reason you would have different template databases for different version of SQL Server is if you want to benefit from features not available in earlier versions of SQL Server. But as this would make your development a lot more complex, I would not really expect you to do this, except possibly only for add-on modules that you only support on newer versions.

Zach 21 Reputation points

2022-02-12T17:47:21.027+00:00

That makes perfect sense, and yes we have an install package. I haven't considered or asked our dev team about the possibility of just changing the install so that the appropriate compat level is set upon installation which includes DB creation based off our template. I'm certain that's possible. We would do full testing on different compat levels in addition to our different SQL server testing.

And agreed, we don't really have a need to have different templates internally, that would definitely complicate development! We ship updates every month as well and have internal tools that create these updates so it would be too much to have multiple templates at this time. We would also have to ensure our update scripts will work with any compat level, but that shouldn't be a problem either to test for prior to release each month.

I think I'm good from here! I have enough to raise during discussions with prd mgmt and dev teams. I greatly appreciate the advise! Hope you enjoy the rest of your weekend, kind sir.

Erland Sommarskog 100.9K Reputation points MVP

2022-02-12T17:50:39.413+00:00

We would also have to ensure our update scripts will work with any compat level, but that shouldn't be a problem either to test for prior to release each month.

You would have to test with all versions of SQL Server you support anyway. Yes, Microsoft may make promises about compat level, but if they have a bug and you did not test, the customer that runs into an issue may not have a friendly voice when they say "you did not test?".

(Of course, taken to the extreme, you would have to test with every CU level, but that's a little easier to talk yourself out of).

Zach 21 Reputation points

2022-02-12T18:03:14.837+00:00

Absolutely, and currently, we have already been testing with all versions of SQL server that we support (2012-2019), we would have to consider adding monthly testing for CL but agree that might be overboard.

It'd be much easier to just force customers to have the latest SQL server than all of these points are null. But that's never going to happen unfortunately. We have over 1200 end users. We've posed this possibility before (must have latest SQL Sever version) and have gotten strong responses along the lines of "don't force us to upgrade".

Scott Townsend 0 Reputation points

2023-10-04T00:02:13.89+00:00

We have an older Accounting system (Written in Visual FoxPro 9) that currently uses SQL Compatibility 100. To upgrade that accounting system would cost us $1 Million or more. We have an Intranet CRM DB that we have custom built that has code that reaches into the Accounting System, DB and we have been upgrading the DBMS along the way for our CRM and have had to keep the Accounting system at 100 as 110 and higher breaks a several things. If Compatibility Level 100 is dropped in the next version we would be unable to upgrade. Since there are Many Cross DB Joins, this causes lots of coding issues if we need to separate them into different DBMS Servers.
Sign in to comment

1 additional answer

Michael Taylor 47,806 Reputation points

2022-02-11T20:08:53.973+00:00

Since SQL 2022 is still in preview I think it is a little early to say affirmatively either way. However heuristics say it won't be supported. Based upon the compatibility certification docs it says that SQL is verified to support upgrades from older versions of SQL up to a point. SQL 2019, for example, supports SQL 2012+ so those would be in the compatibility matrix.

SQL 2012 is supported through 12 July 2022 with extended security updates after that until 2025. SQL 2008 left extended support in 2019 which would have been right around when SQL 2019 was released so that was probably the last version to support the (now unsupported) SQL 2008 level. I would wager SQL 2022 will probably support 110+.

The purpose of compatibility is to allow you to get your DB onto the newer server first and then test and fix issues before "switching it over" to the newer DB level in your production environment. It isn't designed for long-term compatibility. If you were to install this older DB level on SQL 2022 then it would be auto-updated to the newer compatibility level since the previous level wouldn't be supported.

Between now and the release of SQL 2022 it might be a good idea to try to identify and resolve compatibility issues with SQL 2019 and get your DB upgraded. We went through this process across all our databases (we have a lot) and we only ran into a few index/query tuning issues that our DBA had to resolve. You have to do it at some point anyway.
Please sign in to rate this answer.

1 person found this answer helpful.
Zach 21 Reputation points

2022-02-12T16:07:40.633+00:00

Thank you for the reply.
It's not my company that I'm concerned for, it's our clients. We package a DB that's CL 100 because currently it has the widest range of SQL servers that support it (all of them) that means our customers could use any SQL server version they'd like.
If 2022 drops 100, and we do nothing, our clients won't be able to use 2022. If we change our DB's CL to 110, clients lose ability to use 2012.
We have been instructing clients to upgrade to the latest version of SQL server for 20+ years but that doesn't always happen quickly nor is feasible in the industry we are in.

Michael Taylor 47,806 Reputation points

2022-02-12T16:21:00.713+00:00

If 2022 drops 100, and we do nothing, our clients won't be able to use 2022. If we change our DB's CL to 110, clients lose ability to use 2012.

No. SQL 2008/R2 is no longer supported by MS through extended support. Your application not supporting a DB that MS doesn't support makes sense.
Setting level to 110 would set your database to support SQL 2012 or newer so your application would change its system requirements to say it requires SQL 2012+ which is in extended support until later this year. Once 2012 ends your software should ideally be updated to level 120 and changed to only support SQL 2014+ which is good through 2024.

Zach 21 Reputation points

2022-02-12T17:13:48.22+00:00

My fault, I forgot that 110 is good for 2012. That makes sense. And we've already told clients we've dropped support for 2008, but we haven't changed CL yet. Moving it to 110 makes sense and we are planning to do that in the next month. In that case, 110 might still be good for 2022. And then we do like you said, when 2012 is dropped, update to 120...and so on.
Much appreciated!
I also agree with the other answer below, we really should provide the latest and greatest DB version and CL because leaving it at the lowest common CL does not allow customers who have upgraded to the newest SQL server to enjoy the performance enhancements that comes along with that (I read that CL really drives the behavior, not DB/Server version...so using a 100 in 2016 really means you are getting the performance of a 2008...not good! That's why I'm trying to figure out the best move here while we are looking at all of our options).
Sign in to comment