close

Windows

Cloud

Microsoft 365

PowerShell

Active Directory

Security

Windows Server

Video

If you are a large enterprise, don't miss our IT cost-cutting webinar!

Home

SQL Server

New Features in SQL Server 2022

Michael Otey

|
Network Security

SQL Server is Microsoft’s premier enterprise database platform. But in today’s world of rapid-fire releases and continuous updates, the new SQL Server 2022 release seems like it has been a long time in coming. But it includes some great new features.

SQL Server 2019 was the last major release of SQL Server. With every release, Microsoft continues to optimize the core SQL Server engine. Today, most SQL Server innovations start off in the Azure cloud and then make their way to the on-premises editions of SQL Server. Let’s take a closer look at the upcoming editions of SQL Server 2022 and their new features.

SQL Server 2022 new features

The new features in SQL Server 2022 include:

  • Parameter Sensitive Plan Optimization
  • Query Store enhancements
  • Link to Azure SQL Managed Instance
  • Contained Availability Groups
  • Azure Synapse Link for SQL
  • Multi-Write Replication
  • Azure Active Directory authentication
  • Azure Purview integration
  • SQL Server Ledger
  • AWS S3 storage integration

Now let’s look at each one in detail.

Parameter Sensitive Plan Optimization

This is a new intelligent query processing enhancement that’s designed to improve the performance of existing workloads without changes to the application code. Previously, SQL Server cached a single plan per each stored procedure. This was based on a set of parameters and it was called parameter sniffing.

However, some parameterized queries are sensitive to the input parameter values that are used. And the generated plan was not optimal for all possible values those parameters can take. The new Parameter Sensitive Plan Optimization caches multiple active execution plans in the plan cache for a single parameterized query.

These cached execution plans will accommodate different data sizes based on the provided runtime parameter values. To enable PSP optimization, you need to set the database compatibility level to 160 for the database you are using.

Query Store enhancements

The Query Store is a performance feature that helps you better track performance history and troubleshoot query plan and performance issues. In previous releases, you had to manually enable the Query Store. With SQL Server 2022, the Query Store will now be enabled by default for new databases providing improved query intelligence and insight.

Query Store will also now be supported on Availability Group read-only replicas providing better performance analytics 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 application code. Previously, Query Store hints were only available on Azure SQL Database and Azure SQL Managed Instance.

Link to Azure SQL Managed Instance 

Like you might guess, Link to Azure SQL Managed Instance is a hybrid cloud feature that is designed to enhance availability. It provides near real-time data replication between SQL Server 2022 and the link feature in Azure SQL Managed Instance. It enables you to shift workloads to read-only secondaries on Azure, allowing you to take advantage of cloud performance and scale for those workloads.

SQL Server 2022 Azure SQL Managed Instance diagram

SQL Server 2022 – Azure SQL Managed Instance (Image Credit: Microsoft)

 

You can also use it to migrate databases with minimum downtime. The Link to Azure Managed Instance is also supported with SQL Server 2019 Enterprise and Developer edition, and SQL Server 2016 Enterprise, Developer, and Standard editions.

This feature is based on the technology from distributed availability groups. However, it does not require the involved setup and configuration of availability groups. Currently, the replication of user databases from SQL Server to Azure SQL Managed Instance is one-way. And it can only be used with user databases.

User databases from SQL Managed Instance can’t be replicated back to SQL Server, although you can restore a migrated database back to SQL Server 2022 on-premise. Microsoft has stated that the general availability of the bidirectional disaster recovery (DR) failover capability of the Link feature for Azure SQL Managed Instance will be released after the general availability of SQL Server 2022.

Contained Availability Groups

Another availability feature, Contained Availability Groups addresses the limitations found in the previous versions of Availability Groups (AGs). AGs consist of one or more user databases intended to operate as a coordinated group where they are replicated to different nodes in a cluster. When there is a failure in the node that hosts the primary copy, the databases in the AG are moved as a unit to another replica node in the AG.

Previous version of AGs did not include other objects like users, logins, and permissions. This required the administrator to manually make sure that these objects are duplicated across all replica instances in the AG.

Contained availability groups extend the concept of the group of databases being replicated to include relevant portions of the master and msdb databases. The contained AG environment includes settings that would affect the application. Relying on them like users, logins, permissions, and SQL Agent jobs at the AG level using specialized contained system databases within the availability group.

Like the user databases, these objects will automatically be consistent across replicas in the AG. This eliminates the need for the administrator to manually make, create, and update these objects manually across the different AG replicas.

Azure Synapse Link for SQL 

Azure Synapse Link for SQL is a new analytics feature that provides ETL-free (extract, transform, load) near real time analysis and reporting for SQL Server relational data. It captures the changes to databases in SQL Server 2022 and feeds them to Azure Synapse Analytics for real time analysis. There is seamless integration between SQL Server 2022 relational databases and Azure Synapse Analytics dedicated SQL pools.

SQL Server 2022 new features powered by Azure

SQL Server 2022 new features powered by Azure (Image Credit: Microsoft)

It works by continuously replicating the 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 source databases. And without requiring any ETL processes.

Multi-Write Replication

SQL Server 2022’s Multi-Write Replication automates the last-writer wins rule for replication conflict resolution. With earlier versions of SQL Server, when a conflict arose between data that was written to different replicas, the conflict would need to be addressed manually.

Multi-Write Replication solves this issue by implementing the last-write wins rule. If a conflict were to occur between replicas, the last write would take precedence and be written to all replicas. Multi-Write Replication uses the timestamp recorded in UTC time to avoid timezone issues.

Azure Active Directory authentication

Previous versions of SQL Server required you to use either Windows Server Active Directory (AD) or SQL Server authentication – even if you were using SQL Server in hybrid Azure environments. With SQL Server 2022, you can now use Azure AD authentication for your SQL Server 2022 connections.

Azure Purview integration

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

SQL Server Ledger

SQL Ledger is another feature that was first introduced on Azure SQL database then later passed on to SQL Server 2022. SQL Ledger uses blockchain to verify changes made to your databases. It enables to you maintain an immutable record of the data, cryptographically linking the data and their changes in a blockchain structure. It shows any data tampering and makes the data verifiable.

SQL Server 2022 Ledger overview

SQL Server 2022 Ledger overview (Image Credit: Microsoft)

Ledger can help customers streamline audits, providing cryptographic proof of data integrity to auditors and eliminating the need for manual forensics. Ledger also helps protect data from any attacker or high-privileged user, including database administrators (DBAs), system administrators, and cloud administrators.

If a row is updated in the database, its previous value is maintained and protected in a history table. Ledger provides a history of all changes made to the database over time.

AWS S3 storage integration

SQL Server 2022 provides new object storage integration, enabling you to integrate SQL Server with AWS S3 compatible object storage in addition to Azure Storage. A new S3 connector has been added to SQL Server. It uses the S3 REST API to connect to any provider of S3-compatible object storage.

SQL Server 2022 extends the existing BACKUP/RESTORE TO/FROM URL syntax by adding support for the new S3 connector using the REST API. There are a number of cloud vendors providing S3 storage including Amazon, Cloudian, Dell, Hitachi, HPE, Netapp, Nutanix, Pure Storage, Red Hat, and more.

SQL Server 2022 on Linux

Beginning with SQL Server 2017, Microsoft released a version SQL Server for Linux. SQL Server on Linux essentially delivers the core relational database engine as the Windows version of SQL Server. And it runs on the most popular Linux distributions like Red Hat, SUSE, and Ubuntu.

The relational engine and performance features are essentially on par between the two versions. And if you want to run SQL Server in a container or with Kubernetes, then you will definitely need to use the Linux version.

While the differences between the Windows editions and Linux editions continue to be reduced with each new release, there are still quite a number of features that are not included with SQL Server 2022 for Linux. Including some of the new features that were covered in this article. The main features that are not part of SQL Server 2022 in Linux are:

  • Azure Arc agent
  • Azure Synapse Link
  • Microsoft Purview integration
  • Merge replication
  • Stretch DB
  • Linked servers to data sources other than SQL Server
  • System extended stored procedures (xp_cmdshell, etc.)
  • FileTable, FILESTREAM
  • CLR assemblies with the EXTERNAL_ACCESS or UNSAFE permission set
  • Buffer Pool Extension
  • SQL Server Agent subsystems: CmdExec, PowerShell, Queue Reader, SSIS, SSAS, SSRS
  • Alerts
  • Database mirroring
  • Windows integrated authentication for linked servers
  • Windows integrated authentication for availability group (AG) endpoints
  • Azure Active Directory authentication
  • Always Encrypted with secure enclaves
  • StreamInsight
  • Analysis Services
  • Reporting Services
  • Data Quality Services
  • Master Data Services

SQL Server 2022 Editions

SQL Server 2022 will continue to follow the same editions of SQL Server as the previous few releases of SQL Server.

  • Enterprise – Designed for the high-end datacenter, the Enterprise edition delivers the highest levels of performance and scalability for mission-critical workloads. It supports the operating system maximums for CPUs and RAM. There is full support for eight node Availability Groups.
  • Standard – Standard edition provides basic data management for departments and small organizations. It is limited to a lesser of 4 sockets or 24 cores and 128 GB of RAM. It provides support for two node Basic Availability Groups.
  • Web – Intended for Web hosting providers, the Web edition provides scalability, affordability, and manageability for small to large scale web properties. It is limited to a lesser of 4 sockets or 16 cores and 64 GB or RAM.
  • Developer – Designed to enable developers build applications using SQL Server as the backend database. It includes all the functionality of Enterprise edition but it is licensed for use only as a development and test system. It cannot be used for production workloads.
  • Express – An entry-level, free database, the Express edition is intended to be used as a learning tool. And for building desktop and small server applications. It is limited to a lesser of 1 socket or 4 cores, 1410 MB of RAM, and it has a maximum database size of 10 GB.

SQL Server 2022 will continue to be available on both Windows and Linux. And the same editions are supported in both Windows and Linux. However, there are several features that are not available in the Linux editions. The main differences between the features available in the two platforms are listed in the feature section above.

Hands-on with SQL Server 2022

At the time of this writing, SQL Server 2022 is still in preview. But Microsoft has made an announcement that it will be generally available in the second half of 2022. In the past, Microsoft has often announced their new releases of SQL Server in conjunction with the PASS Summit conference. And this year’s conference is scheduled for November.

If you want to get some hands-on experience with SQL Server 2022, you can download the SQL Server 2022 preview from Introducing SQL Server 2022. There is a Ubuntu Container at  SQL Server Linux container image on Docker Hub. You can also try SQL Server on Azure for free, with a 180-day trial at SQL Server 2022 or Windows Server 2022. To install SQL Server 2022, check out our guide on Petri!

And you can find a complete list of all the new features in SQL Server 2022 on Microsoft’s website.

Article saved!

Access saved content from your profile page. View Saved