What’s new in SQL Server 2022?

 

This article is an overview of the recently released features in SQL Server 2022 and an understanding of how each functions as a user. SQL Server 2022 has made significant advancements in its engine, and each feature deserves a dedicated article for in-depth discussion. However, since this article is just an overview, I will not go into the details of each feature.

 

Enhancements in terms of integration with Azure, S3 Blob, and deployment on Linux and Kubernetes are beyond the scope of this article.

 

New features in SQL Server 2022 include:

 

• Parameter Sensitive Plan Optimization

 

This is a new intelligent query processing enhancement designed to improve the performance of existing workloads without changes to the application code.  However, some parameterized queries are sensitive to the input parameter values ​​used. And the generated plan was not optimal for all possible values ​​those parameters could take. The new Parameter Sensitive Plan Optimization caches multiple active execution plans in the plan cache for the same parameterized query. To enable PSP optimization, you must set the database compatibility level to 160 for the database you are using.

 

• Query Store Enhancements

 

Query Store is a performance feature that helps you better track performance history and troubleshoot query plans and performance issues. In previous releases, you had to manually enable Query Store. You will also be able to use Query Store hints to shape query plans and enhance query execution without changing the application code. Previously, Query Store hints were only available on Azure SQL Database and Azure SQL Managed Instances.

 

• Link to Azure SQL Managed Instances

 

Link to Azure SQL Managed Instances is a hybrid cloud feature designed to increase availability. It provides near real-time data replication between SQL Server 2022 and the Link feature in Azure SQL Managed Instances. It also enables you to move workloads to read-only secondaries on Azure, allowing you to take advantage of cloud performance and scale for those workloads.

 

• Azure Synapse Link for SQL

 

Azure Synapse Link for SQL is a new analytics feature that provides ETL-free Extract, Transform, and Load near-real-time analysis and reporting for SQL Server relational data. It captures changes in the database in SQL Server 2022 and feeds them to Azure Synapse Analytics for real-time analysis. There is seamless integration between SQL Server 2022 relational database and Azure Synapse Analytics dedicated SQL pool. It works by continuously replicating data from Azure SQL Database or SQL Server 2022 to Azure Synapse Analytics. This integration enables you to run analytics, business intelligence, and machine learning scenarios on your operational data with minimal impact on the source database. And without the need for any ETL process.

 

• Multi-Write Replication

 

Multi-write replication solves this problem by enforcing the last write wins rule. If a conflict occurs between replicas, the last write will take precedence and will be written to all replicas. Multi-write replication uses timestamps recorded in UTC to avoid time zone issues.

 

• Azure Active Directory Authentication

 

Previous versions of SQL Server required you to use Windows Server Active Directory (AD) or SQL Server authentication – even if you’re using SQL Server in a hybrid Azure environment. 

 

• Azure Purview Integration

 

Azure Purview is an integrated data governance and management service that runs in Azure. SQL Server 2022 is integrated with Microsoft Purview for greater data discovery, permit to break down data silos, and provide policy management for access control. Purview can classify data using built-in and custom classifiers as well as Microsoft Information Security sensitivity labels.

 

• SQL Server Ledger

 

SQL Ledger is another feature that was first introduced on Azure SQL Database and then later passed to SQL Server 2022. SQL Ledger uses blockchain to verify changes made to your database. It enables you to maintain an immutable record of data, cryptographically linking data and their changes to a blockchain structure. This shows any data tampering and makes data verifiable. Ledger can help customers streamline audits, providing auditors with cryptographic proof of data integrity and eliminating the need for manual forensics. Ledger also helps to protect data from any attacker or high-privileged user including database administrator. Many cloud vendors are providing database administrators. If a row is updated in the database, its previous value is maintained and preserved in the history table. Ledger provides a history of all the changes made to the database over time.

 

• AWS S3 Storage Integration

 

SQL Server 2022 provides new object storage integration, allowing you to integrate SQL Server with AWS S3-compatible object storage in addition to Azure storage.