SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,118 questions
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?
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.
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.
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.
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.
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).
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".
Sign in to comment