blog

What’s new in SQL Server 2022

Rajendra Gupta

Published December 8, 2023

SQL Server 2022 is the latest database version released by Microsoft in November 2022. The release builds upon previous versions to add more choice for SQL Server users when it comes to development languages, data types, replication to and from Azure, and more.

You will find full support for the release in ClusterControl, as of version 1.9.7.

Here, we will highlight the new features, functionalities, and improvements introduced in SQL Server 2022 that add value for anyone interested in using this particular version.

Let’s dive in.

New features and functionalities in SQL Server 2022

SQL Server 2022 includes a number of new features and performance enhancements. Let’s take a look at each one in more detail.

Azure Synapse Link for SQL

Azure Synapse Link for SQL 2022 is a feature that enables you to replicate data from SQL Server 2022 to Azure Synapse Analytics in near real-time. It allows you to run analytics, business intelligence, and machine learning scenarios on your operational data without impacting the performance of your source database.

The feature replicates data from SQL Server 2022 to Azure Synapse Analytics in near real-time. As a result, it lets you get insights into your data faster and make better decisions.

Since Azure Synapse Link for SQL 2022 uses change tracking to replicate data from your SQL Server 2022 database, it means that there is minimal impact on the performance of your source database.

Azure Synapse Link for SQL 2022 can be used for analytics, business intelligence, and machine learning.

Object storage integration

SQL Server 2022 can now be integrated with S3-compatible object storage and Azure Storage. It allows you to store your data more cost-effectively and cost-effectively. The object storage integration can be done in the following ways.

You can integrate SQL Server with S3 compatible storage using the Backup to URL feature. You can use this storage for BACKUP and restoration purposes with a s3 connection using REST API.
SQL Server 2022 now supports Data Lake Virtualization, which allows you to query and analyze data in Azure Data Lake Storage without moving the data to SQL Server.

Data Virtualization

SQL Server 2022 adds support for querying external data using polybase with Oracle TNS files, MongoDB API for Cosmos DB, and ODBC.

Azure SQL Managed Instance Link

SQL Server 2022 provides a link for Azure SQL Managed Instance that enables you to do real-time data replication from SQL Server to Azure SQL Managed instance. Using this functionality, you can do the following tasks:

Scale read-only workloads
Offload analytics and reporting to Azure
Migrate data to Azure cloud
Disaster recovery

Contained Availability Group SQL Server

Before SQL Server 2022, you need to manage users, logins, permissions, and SQL Server agent jobs in case of Always on Availability groups. SQL Server 2022 introduces contained availability groups for the following tasks:

Managing the system objects such as users, logins, permissions, and SQL Server agent jobs at the availability group level.
Support for a particular availability group for the contained system databases.

Enhanced security

SQL Server 2022 includes new security features for safeguarding the database systems. A few essential security features are below.

Microsoft Defender for cloud integration: It helps to protect the SQL Servers environment on on-premise, hybrid, and cloud environments.
Microsoft Purview integration: You can use Microsoft Purview policies to SQL servers enrolled in Azure Arc, and Microsoft Purview Data use management.
Ledger: The Ledger provides a highly secure database with tamper evidence capabilities in the database.
Azure Active Directory authentication: You can configure Azure Active Directory authentication for SQL Server database connections.
Always encrypted with secure enclaves: You can use JOIN, ORDER BY, and GROUP BY clauses for UTF-8 collation in confidential queries using the secure enclaves.
Support for MS-TDS 8.0 protocol: The SQL Server 2022 supports TFS 8.0 and TLS 1.3 support for data encryption.

Query Store hints and intelligent query processing

Query Store hints allow you to provide SQL Server with additional information about your queries, which can help to improve performance.
SQL Server 2022 includes a number of new intelligent query processing features, such as adaptive query optimization, Memory Grant feedback, Parameter Sensitive plan optimization, Degree of parallelism(DOP), and query parallelization.

Performance Improvements

SQL Server 2022 includes performance improvement features as below.

Improved in-memory OLTP performance: SQL Server 2022 performance improvements for in-memory OLTP include faster startup times and improved query performance.
Reduced I/O: SQL Server 2022 improved buffer pool management and better compression.
Optimized query processing: SQL Server 2022 includes a number of optimizations to query processing, such as improved query plan caching and better adaptive query optimization.
Improved performance for columnstore indexes, temporal tables, and machine learning workloads

Wrapping up

SQL Server 2022 is a significant release that includes a number of new features and enhancements that add a lot of value for anyone already using SQL Server, as well as anyone curious to try it out for the first time.

ClusterControl provides full lifecycle support for both SQL Server 2022 and the corresponding enterprise binary, which you can try in a sandbox demo of the platform.

Stay on top of all things SQL Server by subscribing to our newsletter below.