In early November 2021, Microsoft announced the private preview of its flagship database product, SQL Server 2022. (You can read the full announcement blog post at https://cloudblogs.microsoft.com/sqlserver/2021/11/02/announcing-sql-server-2022-preview-azure-enabled-with-continued-performance-and-security-innovation). At some point in 2022, we will see SQL Server 2022 released as a general availability (GA) product, but the specific release date has not yet been disclosed. We can expect to see a variety of the announced features in the next major release deployed to Azure SQL customers. Microsoft has a long history of deploying new features to the Azure cloud far in advance of the on-prem products. This approach gives customers the advantage of powerful new capabilities prior to the GA of the on-prem product. It also offers Microsoft the opportunity to very carefully monitor the user experience and performance impact of every new feature.
There are many new features in SQL Server 2022, and there are a variety of videos available online that have been produced by Microsoft going into the details. However, you will get only highlights and demos of key features from the videos, rather than a full list of all new capabilities. The only way to get the full list of fresh, new features and upgrades is to apply to join the private preview for early adopters at https://aka.ms/EAPSignup.
What’s New in SQL Server 2022?
Let’s look at new features for performance, high availability, as well as security and governance.
Performance Improvements
There are two notable new performance features in SQL Server 2022. First, we have a new feature set known as Intelligent Query Processing. This feature enables SQL Server to build better execution plans and potentially multiple execution plans, the performance of which depends on the parameter values that are provided at runtime.
Older versions of SQL Server would frequently use a single execution plan for a query despite having runtime parameter values optimized only for the first execution of the query, thus ensuring that all subsequent runs of the query underperform.
Second, Microsoft has added much-needed improvements to Query Store. For example, Query Store is now enabled for all new databases. Another cool feature is that the Query Store can now help resolve performance issues related to the MAXDOP (maximum degree of parallelism) setting, memory grants, and the cardinality estimator using a feedback cycle to adapt and improve query execution plans. Finally, Query Store now supports availability group (AG) read-only replicas and enables query hints directly within the Query Store to improve the performance of queries without making actual code changes.
High Availability and Connection Improvements
High availability in SQL Server 2022 gets better by allowing you to quickly and easily create a distributed AG between an on-prem SQL Server and an Azure SQL Managed Instance (MI) for use as a disaster recovery backup server or use as a readonly replica for reporting workloads. You can also manually failover to the MI and back again to the on-prem SQL Server with just a few clicks. (Note that distributed AGs are not identical to standard AGs. Prerequisites and features may vary.)
Another connection built between SQL Server 2022 and Azure Synapse Analytics, called Azure Synapse Link, allows SQL Server 2022 to automatically feed changes to data occurring in SQL Server directly to Azure Synapse Analytics without the creation of a new and major ETL pipeline.
Security and Governance Improvements
Remember all that hype about blockchain? Although I’ve never been a fan of this technology, it does offer useful features for specific applications. Enter SQL Server Ledger. The new feature creates an immutable track record of data modifications over time using “blockchainish” technologies. This protects data from tampering, which is useful for certain situations and use cases, and also offers advantages for internal and external audits. Lastly, in an earlier article, I described a new governance tool called Azure Purview (www.dbta.com/Columns/SQL-Server-Drill-Down/Introducing-Azure-Purview-Microsofts-Next-Generation-of-Data-Governance-145167.aspx). Microsoft has expanded its initial feature set into a broader data governance platform. Purview now provides tighter integration with SQL Server and Azure SQL so that you can automatically scan SQL Server to capture metadata, classify data using common classifier labels and tagging (such as PII data or HIPAA data), and configure and control specific access rights and privileges to SQL Server from a single Azure Purview console.
Dive in today by watching the videos from the Microsoft Ignite conference or from the PASS Data Community Summit. And take the new release for a test drive by applying to join the private preview.