Is Microsoft SQL Server 2022 a Big Release?

SQL Server 2022

Normally when y’all post questions here on Pollgab, I let ’em pile up and I eventually do an Office Hours webcast where I talk through my answers. I check in on the list every couple of days just out of curiosity, though, and one of ’em jumped right out at me.

The question, posed by Brent’s Tasty Beverage (nicely done) was:

My friends feel announcement from MS regarding SQL22 were only relatively small changes (since we didn’t see too much of multiple plans technically or demo), nothing groundbreaking or revolutionary. What are your thoughts?

There are a few questions here.

Should a Microsoft SQL Server release be groundbreaking or revolutionary? No. And I mean, no isn’t even strong enough of a word – absolutely, positively hell no. Microsoft SQL Server is one of the world’s most popular relational databases, trusted by companies from banks to hospitals to manufacturers to corner shops. This isn’t some tiny project used by a few hobbyists. Whatever Microsoft ships in SQL Server is going to be installed by thousands of companies and relied on for decades. If you want something groundbreaking or revolutionary, check out the poop that gets flung at the wall the new services that get created in the cloud. When Microsoft creates something brand new in the cloud, that’s where they can experiment with crazy groundbreaking stuff like CosmosDB. If 14 people use it and get burned, life goes on. (I’m being humorous here – at Microsoft’s scale, anytime they sign their name to anything, even an open source project, they’re signing a check for a ton of support workload and technical debt.)

Are SQL Server 2022’s changes relatively small? Oh my gosh, no. Even when I look at just the four big-picture changes that I listed in the What’s New in SQL Server 2022 post:

  • Failover from SQL Server 2022 to Azure SQL DB Managed Instances and back, plus related, the ability to restore versionless databases from Azure SQL DB Managed Instances down to on-premises SQL Server 2022
  • Azure Synapse Link to avoid complex ETL jobs between SQL Server and Azure Synapse
  • SQL Server Ledger – blockchain immutable histories of tables
  • Parameter-sensitive plan optimization that caches multiple plans per stored procedure

Those are way, way bigger than they look at first glance, and I’m really excited at each of them. I don’t think Microsoft will do a perfect job of shipping each of them by any means, nor am I saying I’m going to agree with how much (or little) work they put into each feature, but all four of those are bigger, harder work than they look like at first glance, and they have the potential to be really big. It’s going to be up to us – the real world users – as to how well they’re adopted, and in the following thousands of words – there’s going to be a lot, because I just opened this bottle of wine – I’m going to talk about which ones I think will catch on.

Did Microsoft demo much of the changes? From a very high level yes, and I’m perfectly fine with that because this isn’t SQL Server 2021. I don’t expect the features to be fully baked yet, and I expect the team to still be frantically making decisions about what parts they want to show, and which parts they want to hide under the rug. I’m not saying the code isn’t fully baked – by now, it is – but the decisions about marketing the features are just beginning. Plus, when the features are just unveiled, the team hasn’t had time to build solid marketing demos yet. They’re still focused on building the features and fixing the bugs. (And sometimes, early-announced features don’t even make the final release build – remember Always On Availability Groups for Kubernetes?)

I love, LOVE this question from Brent’s Tasty Beverage – and I’m laughing now as I write this because I’m under the influence of a Tasty Beverage – Famous Gate by Domaine Carneros, one of my favorite wineries – because the question is thought-provoking. Let’s talk through all the thoughts it provokes.

I think Microsoft made good choices,
and they’ll make more sense long term.

When I judge a release, I think about a few criteria:

  • Is Microsoft trying to tackle real problems?
  • Do I think the overall strategy of the solution is good? (Forget small implementation details for now – just are we on the right track?)
  • Do I think a lot of users are actually going to adopt the solution?

For example, if I think back a decade ago when Microsoft first released Always On Availability Groups, I thought they were trying to tackle the right problems, and I thought the solution was good, and I thought a lot of people could actually adopt the solution. On the flip side, when 2008R2’s data-tier applications come out, I told you that there was nothing to see here, move along.

So with those criteria in mind, let’s think about the four SQL Server 2022 features I mentioned.

Failover back/forth from SQL Server 2022 and Azure SQL DB Managed Instances

Is Microsoft trying to tackle a real problem? Absolutely. For decades, people have asked, “How do I restore a database to an earlier version of SQL Server?” And for decades, we simply haven’t had an answer. This posed a real problem when Microsoft started hosting databases in the cloud under the brand name of Azure SQL DB because they kept saying, “Azure SQL DB is the next version of SQL Server.” When the cloud is running vNext, that also means you can’t restore the databases back down to your local machine.

Do I think the overall strategy of the solution is good? Yes, and it wasn’t as easy as it looks. Microsoft had to do a spectacular amount of work to plan out a long-term solution: they had to standardize on a file format that would work for multiple versions in a row, not just today but for the coming decades. My mind boggles at the meetings and consensus that’d be required by this. This isn’t five people coming to an agreement in a Github issue – this is dozens or hundreds or thousands of stakeholders around the world coming to an agreement about a balance between the agility of changing the database file format versus the stability of a future-compatible file format. SQL Server 2022’s announcements by no means make it easier to restore a SQL Server 2022 database down to 2019, nor should it. It’s hard enough just to get the cloud and the on-premises databases to agree on a file format, and I’m completely in awe that they managed to pull this off. It’s hard.

Do I think a lot of users are going to adopt it? Well, here’s where we hit a problem. The solution relies on not just an Availability Group, but a Distributed Availability Group (DAG). The demos look point-and-click easy, but:

  • They skip over a lot of stuff (like provisioning the Azure SQL DB Managed Instance and getting connectivity working between your on-premises SQL Server and the Managed Instance)
  • The demos look like one database per Availability Group and Distributed Availability Group, which is going to be really painful management at scale
  • When things go wrong, troubleshooting a DAG is awful. The DAG documentation page looks impressively long at first until you realize that’s pretty much all there is, and heaven forbid something go wrong with your DAC. These things are troubleshooting nightmares. Throw in a mix of an on-premises AG mixed with the DAG up in Azure, and you’ve got the devil’s cookbook.

But despite these gotchas, I’m relatively happy with the big picture direction. Building v1 of anything is tough. This isn’t v1 of Availability Groups, but it’s v1 of something very ambitious. I like it. It also has the possibility of increasing Azure SQL DB Managed Instance adoption as a new easy default option for disaster recovery. (In reality, it’s gonna be anything but easy if they don’t start putting a hell of a lot more work into manageability though.)

Azure Synapse Link

Is Microsoft trying to tackle a real problem? Yep. The pain: ask any enterprise report writer, and they’ll tell you that they wish they had more real-time access to data stored everywhere. The instant they have to start dealing with extract-transform-load processes, bam, there goes weeks or months of lead time. Add a new column to a source system? Forget it. It’ll take forever to get into enterprise-level reports. Microsoft is recognizing a very serious pain.

Do I like their solution? I don’t play in this playground – moving data around between databases – so I’m not qualified to judge it. However, the documentation for Azure Synapse Link for Azure Cosmos DB includes this gem:

Synapse Link is not recommended if you are looking for traditional data warehouse requirements such as high concurrency, workload management, and persistence of aggregates across multiple data sources.

Well, that doesn’t sound good.

But as a DBA, if someone told me they wanted to enable this feature, I think I’d be okay with it. The alternatives are things like Change Data Capture, Change Tracking, or rolling your own ETL solution, and frankly, all of those are more work than Azure Synapse Link sounds like. (And all of those solutions have performance overhead.) I see warning signs, but not for the database itself, so … okay with me!

Do I think a lot of users are going to adopt it? I have no idea, but given that limitation above, sure seems like it would backfire hard as it grows. But like I said, I don’t play in this playground.

SQL Server Ledger

Is Microsoft trying to tackle a real problem? Yes. I know, blockchain is a meme by now, but there are legitimate business needs for a bulletproof history of a row. Take airlines: when there’s a crash and a particular part is suspect, they need to know the complete history of that particular part, and they need to know that the history hasn’t been fudged by a supplier or a mechanic.

Do I like their solution? Yes. There’s a slight challenge because any solution here is going to involve keeping ledgers of the transactions involved for a long period of time. Think about that for a second. (And while you’re thinking, make a note of what you think about, and keep that log around for forever, ha ha ho ho.) In our industry, we’ve already got a ton of problems with people trying to shrink databases. Imagine if the transaction log never went away – and think about the space requirements you’d be dealing with. This is going to present a space problem for the coming decades. To understand the implications of how it would work, check out Azure SQL Database ledger.

Do I think it’ll get widespread adoption? For the people who need it, yes. For the people who like this kind of thing, this is the kind of thing they like. Due to the size requirements, this is never gonna be a switch that just gets flipped on everywhere, but where you need it, you need it. I also think this is the kind of feature that can actually increase license sales because if it works to the point where it’s legally defensible in court, then it’s an edge that less expensive databases don’t have. It can increase licensing sales.

Parameter-sensitive plan optimization

Is Microsoft trying to tackle a real problem? Yes, I know dang well that this is a huge problem out in the real world because my Fundamentals of Parameter Sniffing and Mastering Parameter Sniffing classes are amongst my biggest sellers. (There’s a reason I didn’t teach my FPS class for free this month.)

Do I like their solution? I think it’s a great down payment. It’s only focusing on equality searches (like @Country = ‘US’ versus @CountryCode = ‘Iceland’), not range searches like @StartDate and @EndDate. It’s also only looking at outliers that are immediately obvious by examining the statistics histogram, which still only contains 201 buckets – so it doesn’t catch problems with not enough buckets, outdated statistics, joins, etc. But you know what? That’s totally okay! It’s a hell of a down payment, and I think it’s gonna help way, way more than it hurts. You can learn more about it by watching Pedro Lopes’ demo-filled session on it and the other query plan improvements.

Do I think it’ll get widespread adoption? Yes, because it kicks in automatically at compat level 160 (2022) and it doesn’t require Query Store to be turned on. It doesn’t need to fix every parameter sniffing problem – even if it just fixes 1 in 10 parameter sniffing problems, that’s forward progress, and I’ll totally take it.

This is the one feature that by itself won’t directly increase licensing or Azure revenue. (Indirectly, it might: it proves that Microsoft can continue to iterate over the engine to make queries faster, and that might make someone more likely to host their new application in Microsoft databases. I wouldn’t count on that to happen quickly, though.) I’m happy with the mix of down payment features and features that will make folks like us happy.

I like the direction of all of these.

And these are only some of the highlights – there are more features, and I’m sure we’ll hear more about ’em over time.

Are they groundbreaking or revolutionary? No, I’d say they’re more evolutionary – they represent logical next steps, even if they’re big next steps, for a big, stable relational database platform.

And that’s totally okay.

SQL Server isn’t supposed to implement dramatically different behavior by default, because default behavior is risky. SQL Server is massively popular, and when you’ve got a database platform this big, you need to make conservative bets rather than betting the farm on something risky. I, for one, am glad that Microsoft continues to make the right conservative bets.

Previous Post
Updated First Responder Kit and Consultant Toolkit for November 2021
Next Post
Free Live Class This Week: Fundamentals of Columnstore

33 Comments. Leave new

  • This is what I love about you Brent. When a new version comes out you get to the heart of the changes and sometimes find ones that most of us wont see or understand.

  • Hi Brent, what kind of features do you think are missing or went missing in action?

    I can think of one: utf8 support. It promised to half storage (maybe memory?) of n(var)char fields, but nothing heard of it anymore.

    And although a link between SQL Server 2022 and Synapse is nice, I was hoping that it would show up between Azure SQL DB & Synapse. But that still can happen 😉

  • I also liked that along with Intel that compression should be less invasive for CPU and compress even better than currently. This could be a big win.

  • Brent, thanks for this post. On Synapse link, it is different from CosmosDB in that we store the data directly in SQL Pools so you get the full “warehouse” capabilities of DW. Also, someone asked a question about Synapse link for Azure SQL. Stay tuned.

  • About exactly what I was saying in an interview on Wednesday when asked about my thoughts. Either great minds think alike or the blind following the blind.

    • Steve – when your column came out the other day, I thought, “Dang it, he said the same thing first, I should trash my column.” But then I thought no, there’s a value in the community hearing independent voices saying the same thing – this is a Good Release™, or at least it appears to be so far. I’m a happy camper.

      Great minds think alike. 😀

  • Hi Brent – even though I’m looking forward towards retirement and probably won’t see any of this (been around since Sybase involved!) I agree with what you are saying; these features will be great for whomever needs them. In my case I’ll be sitting on the beach here in Marsh-Vegas, MA (insider joke) enjoying a cool one when these get released so really won’t worry. MAybe for who takes my place :)!!

  • Failover back/forth from SQL Server 2022 and Azure SQL DB Managed Instances
    Love this….
    This totally changes us using Azure for failover….
    Couldn’t be any happier.

  • Write access to the Query Store from readable secondaries. This is huge! I am so excited about this!

  • Apart from multi plan cache on same query to address parameter sniffing issue, I want to see more features in t-sql enhancement. Recent SQL server releases haven’t rolled out exciting features on t-sql including the upcoming release.

  • Failover between the instances… Would it be too far fetched to expect a simple recovery model to work with this? Full recovery has always been the pain, low local server drive space and so on…

  • Has anyone heared if the GREATEST() / LEAST() functions will be added to SQL 2022 too? They are already on Azure but I’m very sure, that I’ll find queries that would run better with those functions than with the current workaround…

  • Well, the SQL Ledger for blockchain feature won’t be keeping transaction log of your whole database, but just the table where you need to enable it. So you would keep the table thin with only the required columns where you need auditing. To me, this feature looks very similar to Temporal-Table feature that came in SQL Server 2016.

    • Right, but it won’t likely be just one table. In environments where they need this kind of legally defensible compliance, folks will say “just protect everything to be safe.”

  • Bruce W Cassidy
    November 23, 2021 10:13 pm

    Nup. I just don’t get it. I tried for years to develop a taste for Pinot Noir, but it’s still the red wine that I fail on. 🙁

    The SQL Server 2022 stuff is good though! I think that there’s way more to be done to make data movement across multiple types of data stores (not just SQL Server) easy and practical, and Microsoft’s current CDC solution is limited. It’s good to see them working in this space, even if they’re not there yet.

    I completely agree that SQL Server releases should be evolutionary, not revolutionary. Having said that, Cosmos DB isn’t enough and it would be good to see Microsoft doing more revolutionary stuff in the data space.

  • Stephen Morris
    August 17, 2022 7:58 pm

    I’d like to second what Amy said

    ”Write access to the Query Store from readable secondaries. ”

    This is a great feature & Querystore has developed in a great way since 2016, particularly pleased to see it being used to persist the proven auto tunings between restarts

  • Thanks for the clear write up and opinion Brent? I am super excited about Buffer Pool Parallel Scans! As we’re seeing more and more companies with 1TB+ of RAM, single threading scans of memory are more and more of a problem.

  • “Parameter-sensitive plan optimization that caches multiple plans per stored procedure” This looks like it could be awesome and disasterous at the same time. LOL I wonder how this will affect plan efficiency, plan cache size…etc. Will have to go dig into this when I get some free time.


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.