SQL Server vNext

We now know when the next version of SQL Server is coming. At Ignite last week, Microsoft announced SQL Server 2022, which comes nearly 3 years after the last version, SQL Server 2019. Apparently, the pandemic’s effects include delays in product development as it’s been nearly three years since SQL Server 2019. The new version is in preview, with a few new features announced. There’s also a video version from the Ignite site, but if you want a quick summary of things, read on.

There were a few items that get me a bit excited about the changes. The first is that we get some query improvements with regard to parameter sniffing in stored procedures. This has been a constant problem for many databases and workloads for years. This is because only one plan has been kept in the cache. That changes in SQL Server 2022, as the instance can now keep multiple plans around. It will be interesting to see if this helps many customers.

The Query Store was an interesting edition to SQL Server and plenty of people have found it valuable. However, its use was optional and you had to enable it. In SQL Server 2022, Query Store is now on by default and there is support for read replicas as well. This change is there to enhance the intelligent query processing (IQP) work that has been growing in each new version. This new version gets MAXDOP and Cardinality Estimator being incorporated into the feedback loop. IQP isn’t perfect, and it might cause you some issues with certain workloads, but many of the changes made in the last few versions do seem to be helping customers.

There was a quick note in the Ignite video about multi-write replication is now available. Not true peer to peer, but this version should automate the last write wins rule, based on UTC timing. I’ve been skeptical of using peer-to-peer replication in the past, mostly because of the need to code conflict scenarios, but a last-writer wins scenario might work well for some applications. If you can accept this rule, and you have good resources for propagating data, this might be something you can use.

Blockchain is coming to SQL Server. There is a Ledger feature that provides an immutable ledger of data changes. This ensures that data integrity is maintained with full auditability. Trusted storage is needed to ensure this meets all audit and regulatory requirements, though if the data has been tampered with, you might only know something happened. It’s not perfect, but it does provide the ability to prove there is trust in the data you are watching. I suspect this will be a feature like TDE that auditors want enabled, even if it isn’t perfect. I do wonder what this will do to storage costs and complexity.

Azure Purview is being integrated into SQL Server 2022. The big takeaway here is that you can scan on-premises data for free. Now I don’t know if this means you can use your data for free in any way, but I do think that this does start to make governance easier. I think that’s a big deal for all of us in the future if companies were to better know what data is risky and work on protecting it. Normalizing the way we look at data governance is a big step forward. This certainly is a long way from most applications using the “sa” account.

One of the challenges I’ve seen in many companies is dealing with reports. Often companies try to run complex queries on their OLTP system, or they spend a lot of time and effort to build ETL structures. Synapse Link provides automatic change feeds from a SQL Server to a SQL pool in Synapse. This is like an automatic replication of the table(s) into your data warehouse. It’s been available for CosmosDB, but now it comes to SQL Server. Given the cost of development and managing ETL jobs, I could see some organizations upgrading to SQL Server 2022 just for this feature. If it works well and quickly.

Disaster Recovery (DR) is something I’ve always been concerned about. I’ve had too many issues over time to treat this lightly. In this new version, you can set Azure SQL Managed Instance as a target for a DR recovery, with failover to the cloud through a distributed availability group. That’s has been possible before, though now this seems easier than ever (as it should be). The failover is a good feature, but a lot of us wouldn’t necessarily want to live in the cloud, so the big announcement here is that you can restore the backup from MI back to an on-premises SQL Server 2022 instance. We can restore back from the cloud to our own systems, which has been a hassle for a long time.

Now, this isn’t failback. I have no idea if logs can be backed up and restored, or if this means a lot of downtime to leave the cloud if you have a big database. From the demo’s, it doesn’t look practical to backup and restore a database of any size to fail back. My guess is this is really a first cut, but the ability to move from MI back on-premises is a huge step forward. If for no other reason than you can backup things for dev work inside your organization. That’s a big win for a lot of customers I know, at least it is if Azure SQL DB and Azure SQL MI are close in functionality from the inside-the-database perspective.

There are other enhancements as well. The cloud offerings from Microsoft gain some scale, which is good. Most of us don’t have huge systems, but more scale usually trickles down with better pricing for the mid levels. Azure Arc grows, allowing you to run Azure SQL MI and Hyperscale PostgreSQL on your own hardware. You’ll pay something to Microsoft, but having these systems stand up, keeping data local, and having them managed easily is a big deal. I expect that more companies will consider things like Azure SQL DB on their own hardware over time, especially as standing up and managing a Kubernetes cluster becomes easier. This feels like the early days before vSphere when we struggled to manage lots of virtual machines. I suspect we will find better ways to deploy and manage container orchestrators on-premises over time.

It feels like a long time since we talked about SQL Server. Certainly the last 18 months have felt like the world is frozen and not much has happened. I know I’ve done some things, but it’s been a strange feeling of suspension for much of life. For me, the announcement of a new version of SQL Server brings some excitement. It feels as though the world is moving forward, and I’m looking forward to learning and writing about the future. I’m also hoping to get the chance to deliver some presentations on something new in SQL Server in 2022.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged , . Bookmark the permalink.