Joey on SQL Server

Microsoft Finally Releases SQL Server 2022

Now generally available, SQL Server 2022's feature set brings it closer to parity with Azure SQL. Here are the biggest additions to Microsoft's RDBMS software you need to know.

• By Joey D'Antoni
• 11/16/2022

At the PASS Community Summit in Seattle on Wednesday, Microsoft announced the general availability of SQL Server 2022, the latest version of its popular relational database management (RDBMS) software.

Microsoft's stated goal for this release is to get the SQL Server experience as close to the Azure SQL experience as possible. Through several features and services, you can take advantage of features like Microsoft Defender for SQL and Azure Active Directory (AAD) authentication. Beyond connectivity for Azure, there are numerous key improvements that will make your life easier as a DBA, wherever you run SQL Server.

There are a lot of new features in SQL Server 2022, more than I have space for in this column, but I wanted to call out some of my favorites and the ones I think will be most useful. I especially wanted to spotlight some of the new T-SQL commands, particularly the GENERATE_SERIES function that my colleague Meagan said “would have changed the way I've built every date table ever.”

Security
As mentioned, SQL Server 2022 supports Azure AD authentication, in addition to the traditional Active Directory authentication. The biggest benefit of AAD authentication for SQL Server is that it supports multifactor authentication and a number of advanced security features. Another Azure component new to on-premises is Azure Purview, Microsoft's data catalog offering, which can automatically classify the data within your database. Governance is a key component of data security, and I really like the direction of this collection of features. The catalog for Purview is located within Azure, and Purview supports a number of services beyond SQL Server and can provide a unified data catalog across all of your data.

Microsoft has also implemented ledger tables in SQL Server 2022. This feature uses blockchain technology to guarantee the integrity of data, which is especially useful for sharing data between partners. Finally, there are new built-in server level roles to better support separation of duties for administrators. Additionally, as part of Azure Arc or Azure SQL VM, agents support Microsoft Defender for SQL, which can protect your workloads against SQL injection attacks and other attack vectors.

Performance
Starting with SQL Server 2016, Microsoft began making several major improvements around query data collection and performance. This started with the introduction of the Query Store in SQL Server 2016 and developing better optimizer behavior around traditional anti-patterns like user-defined scalar functions and table-valued functions in SQL Server 2017 and 2019. SQL Server 2022 continues this journey. Notably, the Query Store is now enabled by default for newly created databases (if you restore a backup from a previous version, this will not apply) and is also available for readable secondary databases in Always On availability group replicas.

There are a few other performance enhancements that you should take note of. I've already written about two of them in more detail: Cardinality Estimation feedback and Parameter Sensitive Plan (PSP) optimization. These improvements, particularly PSP optimization, can help resolve a lot of problematic performance issues that commonly crop up when data skew happens. An additional enhancement is degree of parallelism (DOP) feedback, which can change the number of worker threads used by a query operator on successive executions of a query—much like how memory grant feedback, introduced in SQL Server 2017, could change memory grants for subsequent executions without fully recompiling the execution plan. Another change reduces the amount of page contention for workloads that make heavy use of the tempdb database. Tempdb used to be a common performance bottleneck for SQL Server. By taking advantage of the In-Memory OLTP technology introduced in SQL Server 2014 and configuration enhancements in later versions, Microsoft has nearly eliminated this bottleneck.

Availability
One of the most requested features that was originally announced for SQL Server 2019 but didn't make it into the 2022 edition is support for system databases in Always On Availability Groups. This ensures that log-ins and jobs that are stored in the master and msdb databases are always kept in sync, erasing a major headache for DBAs. This feature is known as contained availability groups. There are some networking improvements to distributed availability groups to provide improved networking performance for distributed availability groups. Another nice improvement is the ability to seed an availability group replica using snapshot-based backup, which is a common request for customers with very large databases.

The other interesting component is failover to Azure SQL Managed Instance. This feature set has a couple of components. You will be able to back up and restore databases from Azure SQL Managed Instance to SQL Server 2022. You may also use Managed Instance as a disaster recovery target. This takes advantage of the distributed availability group functionality without the headache of configuring a cluster. You will need a VPN connection to Azure, but once you have a network connection in place, the configuration is straightforward, as shown below.

For customers with Software Assurance or subscription-based licensing, the SQL licensing component of the Managed Instance pricing is not charged (only compute and storage) which represents around a 55 to 60 percent reduction in pricing for the service, based on typical usage. This can be a cost-effective way to provide database disaster recovery without requiring a lot of infrastructure or cost.

Object-Based Storage
Microsoft also introduced connectivity to S3 compatible storage. While the S3 protocol is most associated with the Amazon Web Services (AWS) storage offering of the same name, S3 is an open source protocol that is adopted by several vendors, including MinIO, Pure Storage and Cloudian, among others. This takes two forms, the most common being backup and restore. SQL Server has long supported backing up databases to Azure Blob Storage, but now supports backup and restore to and from S3 (this can be in AWS or to your own S3 storage). I spoke with a colleague from a storage vendor, and they have seen exceptional backup and restore performance. The other aspect of this feature is the addition PolyBase connectivity for data ingestion to and from S3, which allows for querying and ingesting data from external tables. Another nice addition to the PolyBase service, which comes from Azure Synapse Analytics, is “create table as select,” which can be used to easily materialize those external tables.

Should You Go to SQL Server 2022 Right Away?
There are a lot of new features in SQL Server 2022. Some of them are big and may require some changes to your architecture to support. However, others, like Parameter Sensitive Plans, will just work right out of the box and deliver key performance improvements. For every high-level marketing-ready feature , there are multiple features and additions that will improve your development, performance and availability options.

The final question is, what does it cost? For that, you should read my SQL Server 2022 licensing column.