July 7, 2026 at 1:30 pm
hi, im not a dba.
For a couple of years our dev sql server has been a 2022 vm std instance with varying levels of horsepower depending on how loudly we objected. For many years our prod , 2019 on prem std. both have ssas, ssis, ssrs and of course the engine.
We are looking more closely at "real" testing/deployment etc and the question of how inappropriate this sql partnership really is has come up again and will finally be heard.
i have to look at the backward compatibility of each and honestly dont recall if that means newer functions etc (like concatenate) are forbidden on the newer version in spite of backward compatibility or not.
my warnings to our group will be more along the lines of inappropriateness in ssis and ssas. And the fact that lines of code actually differ in each for the 2 different target instances. ssis most notably in the native sql driver (we manually installed it there a couple of times already undrerstanding its being deprecated) that keeps disappearing off the dev instance every time there is an upgrade there. ssas most notably in the connection that basically tells the server to relax the encryption rules. unfortunately our prod server was set up years ago without encryption and its my understanding this will be a tough road for us to overcome.
can the community comment on what i've shared thus far?
July 7, 2026 at 7:54 pm
I'm not completely sure what you're asking here. In terms of setup, I think you have:
If you want to ensure your code works well, you should have both dev and prod on the same version. Esp for things that aren't in the database engine (SSxS stuff). Doing otherwise is asking for issues.
Ideally, you want to capture all code from dev that is tested into a version control system, so export the files (config, code, RDL, .dtsx, etc.) into a repo and commit them. Then import to prod from the repo, not from the dev server. That will help smooth your process and ensure you can catch errors, go backwards, etc.
July 8, 2026 at 12:16 pm
thx steve i was hoping the community would help me to make a case with examples for not doing this. didnt really want a discussion on integration and deployment. was hoping they'd comment on what backward compatibility really means when it come to new features in a newer version of sql. you confirmed what i said but basing the argument mostly on ci/cd shortcomings doesnt really help. ci/cd can always be rigged to deal with language differences. not saying we want to do that but perhaps you are catching my drift.
July 8, 2026 at 4:46 pm
The problem is, while a newer version might report a particular command is "deprecated," that doesn't mean it's not going to work (your example of CONCATENATE, for instance.) Where the bigger problems come in, and the reason you want Prod and Test and Dev to be the same version of SQL is the under-the-hood changes, and the changes to the add-in features that Steve mentioned.
THOSE are where you can run into a lot of trouble, especially when it comes to trying to troubleshoot issues. Changes to the query estimator will give rise to situations where a query that works great in Dev (on a newer version,) performs incredibly horribly in Prod, yet when you pull a copy of the prod data to dev, guess what? It works fine.
Or the developers start taking advantage of some shiny new feature of the engine (say, for example, native JSON support in SQL 2025,) that simply doesn't exist in the version you have in Prod. Now you've got management ticked off because shiny new feature of the app that worked great when the devs showed it off, can't even be implemented in Prod without spending the time, effort, money, and downtime to upgrade Prod.
Arguably? You're better off if the developers' are on an older version than Prod, because at least then they won't use something that doesn't work in the version Prod's on. But even that has the problem of troubleshooting, because again, differing versions
July 8, 2026 at 5:14 pm
The business case is rework. This is time-consuming and expensive. As Jason noted above, if someone uses a feature that isn't in prod, then this doesn't get detected until a production deployment. This could:
Apart from this, even assuming that the developers don't use new features, there are always potential areas where they do something in SSIS that works differently in 2022 than 2019.
You've mentioned potential driver issues, which could be a problem as well.
Good software engineering tries to minimize change. As a developer, I wouldn't want to create potential issues by using different versions of packages in dev than in prod. that increases my workload in managing software and code, and possibly wasting time tracking down simple issues.
July 8, 2026 at 7:09 pm
thx jasona.work. so you are saying that if both the dev and prod are backward compatible to version xxxx, newer features in the dev's version will still work? features that werent around on xxxx? and like concatenate, not available on our prod engine?
July 8, 2026 at 8:49 pm
No, newer features do not work. Most features are backwards compatible, but if the feature didn't exist in 2019, it won't run there.
July 8, 2026 at 11:39 pm
thx jasona.work. so you are saying that if both the dev and prod are backward compatible to version xxxx, newer features in the dev's version will still work? features that werent around on xxxx? and like concatenate, not available on our prod engine?
OK, we need to be very clear about what we're talking about here.
If you have SQL Server 2025 installed on the dev systems and SQL Server 2019 on prod, there are going to be listed, in the SQL2025 docs, features / commands that have been deprecated. What that means is, Microsoft has decided that, at some point, that feature WILL go away in a newer version of SQL, but for now it exists and still works. So THOSE will continue to work.
BUT newer versions of SQL ALSO add NEW features that simply do not exist in previous versions of SQL Server. The example I gave, native support for a JSON data type, is an example. Which means if your developers write code in their SQL2025 instance that USE native JSON data types, there is NOTHING in the world, no amount of code-tweaking, CI/CD pipeline tweaking, NOTHING that will get that code to work in your SQL2019 Prod environment, full stop.
So be very careful throwing around "backward compatible," because, while yes, SQL2025 is largely "backward compatible" with any code used from SQL2019, there ARE things in SQL2025 that are NOT going to be compatible (or, exist.) Further, even some of the code that is backward compatible, might have slightly new behavior in a newer version of SQL, and won't behave *quite* the same in an older version and now you've got a troubleshooting nightmare, the classic "it works fine in Dev, it's ops problem now" from the devs.
This is why you really, really, really, really want Prod and Dev to be the same version and ideally, edition, because different editions of SQL (Standard / Enterprise) have some different ENGINE features that could bite you if your devs get clever.
On top of all of THAT, as Steve pointed out, SSIS / SSAS will also have differences in behavior and features between versions (note, I'm not familiar at all with SSAS as we don't use it, and sort of / kind of familiar with SSIS)
All of that is why using the phrase "backward compatible" needs to be approached cautiously. Probably the easiest example (non-SQL) where "backward compatible" breaks down would be Windows itself. Sure, Win11 is "backwards compatible" with older software from the WinXP days, but does that mean that software is going to WORK? Maybe, maybe not (ex: older games with certain anti-piracy methods, WILL NOT work in newer versions of Windows.)
July 9, 2026 at 11:38 am
I think you're raising good points. SQL Server 2022 can still let you use features that aren't available in SQL Server 2019, so it's possible for something to work in development but fail in production. I'd be more concerned about SSIS and SSAS too. Things like driver changes in SSIS or different encryption settings in SSAS can easily cause deployment issues.
If possible, it's always better to keep the development and production servers on the same SQL Server version. If not, testing everything on a SQL Server 2019 server before deployment can save a lot of headaches.
July 9, 2026 at 4:28 pm
thx jasona.work. by backward compatibilty i meant that flavor that we used to set explicitly. its been a long time but as i recall you can set it manually to get certain behavior out of your sql server.
July 9, 2026 at 6:23 pm
thx jasona.work. by backward compatibilty i meant that flavor that we used to set explicitly. its been a long time but as i recall you can set it manually to get certain behavior out of your sql server.
But, that doesn't tell SQL Server "this new feature, you cannot use." All that does (generally speaking) is tell the engine "behave like you're version X-1 instead of X when evaluating a query." So it won't use the newer query estimator engine, but it also won't prevent your devs from using a feature / command that doesn't exist in an older version.
Setting the compatibility level of a database is not going to "fix" your problem of differing versions of SQL, the only thing that will fix that is either re-install the development server to use SQL 2019, or upgrade the Prod server to SQL 2022, anything else is just asking for problems.
July 9, 2026 at 6:58 pm
Don't use "flavor" here. It confuses the issue. You can set database compatibility, as Jason noted, but that doesn't fix issues. MS doesn't always turn off things with dbcompat and issues can slip through.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply