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. This was based on a set of parameters and was called parameter sniffing. 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. With SQL Server 2022, Query Store will now be enabled by default for new databases which will provide better query intelligence and insights. Query Store will now also be supported on Availability Group read-only replicas which will provide better performance analysis for your read-only workloads. 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.
- Implicit Availability Groups
Another availability feature, Implicit Availability Groups, addresses limitations found in previous versions of availability groups (AGs). AGs contain one or more user databases that are intended to work as a coordinated group where they are replicated across different nodes in the cluster. When there is a failure in the node hosting the primary replica, the databases in the AG are moved as a single unit to another replica node in the AG. The previous version of AGs did not include other objects such as users, logins, and permissions. This required the administrator to manually ensure that these objects were duplicated across all replica instances in the AG. Implicit Availability Groups extend the concept of a group of databases being replicated to include the relevant parts of the master and MSDB databases. The implicit AG environment includes settings that will affect applications that depend on them, such as users, logins, permissions, and SQL Agent jobs, at the AG level using the specialized implicit system databases within the availability group. Like user databases, these objects will automatically be consistent across replicas in the AG. This eliminates the need for the administrator to manually create, create, and update these objects across different AG replicas.
- 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
SQL Server 2022’s multi-write replication automates with earlier versions of SQL Server, when conflicts arose between data written to different replicas, the conflict needed to be addressed manually. 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.