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.

drinkin and bloggin pic.twitter.com/e11rEAQMtg

— Brent Ozar (@BrentO) November 7, 2021

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.