This article talks about the next version of SQL Server which is SQL Server 2022. This will cover the latest announcement, release, and all the exciting features SQL Server 2022 offers.
In a world where organizations are digitizing hugely, processing and analyzing data promptly is a huge priority. Microsoft is strategically moving towards a future where they have a priority to help users in the best possible manner. Organizations have been moving in a direction where they have made many changes in their modern data platform. It requires a stable, secure, performance-optimized, Highly available database with seamless connectivity and analytical capabilities. Microsoft SQL Server is continuously evolving its feature and capabilities with every new release.
Microsoft publicized the private preview of SQL Server 2022 on 2nd November 2021. The release offers Azure-enabled capabilities using the integration with Azure Managed SQL instance, Azure Synapse Analytics, Azure preview.
This blog is intended to summarize the features of the coming release of SQL Server 2022.
SQL Server 2022 preview features
SQL 2022 is loaded with exciting new features and enhancements to the core database engine, performance, and security. The following diagram gives a high-level representation of SQL Server 2022.
Let’s explore these features and their advantages over existing SQL Server versions.
On-Prem SQL instance Business Continuity using Azure SQL Managed Instance
Disaster recovery is always a prime focus for any organization. Currently, SQL Server Always On Availability or log shipping is mainly used as a DR solution for on-premises SQL Servers. The SQL Server 2022 provides a new fully integrated link feature to the Azure SQL managed instance for ensuring uptime for your database environment.
- The Azure SQL Managed instance as a new link feature simplifies the configuration and management for disaster recovery. Currently, you can use multi-subnet clusters or distributed availability groups that require complex configurations. The new link feature uses a built-in distributed availability group (DAG) to replicate data to existing Azure SQL quickly Managed instances. DBAs do not spend much time on lengthy configurations or DR instance maintenance
- The link-in feature enables you to read scale-out queries as well to the DR servers. Therefore, you can offload heavy read requests to the Azure SQL Managed Instance
- You can failover from on-premises SQL Instance to Azure SQL Managed instance (DR) or vice-a-versa
You can restore (move) the database from the Azure SQL Managed Instance (DR) to on-premises SQL Server
- Backup database from Managed instance to the storage account
- Restore database in the on-prem instance from the backup file stored in a storage account
Seamless analytics over on-prem operational data
Currently, to move data from an on-premises database to Azure Synapse, you use the ETL (Extract-Transform-Load) tool. It requires ETL pipeline configuration. However, there is a lag in the real-time data processing. Therefore, the Azure Synapse link in SQL 2022 enables you with automatic change feeds. This change feed captures the real-time changes in the SQL Server database and feeds that data into the Azure Synapse Analytics.
SQL Server 2022 Azure Synapse Link breaks the wall between operational and analytical stores with Azure Synapse Link for SQL Server. You get the following benefit using this feature.
- Hybrid transactional processing
- Analytical processing
- Near real-time analysis
- Minimal impact on the source (operational) system
You can leverage synapse data using Azure Machine Learning, Power BI, Spark, combine data with many different data sources such as Azure Data Lake Storage, SQL pools.
Azure Purview integration
Azure Purview is a unified data governance and management service. The Azure Purview provides the following features integrated SQL 2022.
- Automatically scan your on-premises SQL Server for free to capture metadata
- It can automatically scale on-premises SQL Server for capturing metadata
- You can use in-built or custom data classifiers and use Microsoft Information Protection sensitivity labels
- Configure and control access rights to SQL Server
SQL Server Database Ledger with blockchain functionality
SQL Server 2022 introduces a new ledger feature for creating immutable track records of data modifications. It can prevent data tampering events by malicious activities. It is suitable for applications holding critical financial data such as Banking transactions.
Industry-leading performance and availability
SQL Server 2022 introduces the following performance and availability enhancements to the existing SQL Server.
- Intelligent Query Processing for a parameterized query: Parameter sniffing is a problematic issue in stored procedures with different parameters. SQL Server stores(cache) an execution plan based on a parameter that might not work efficiently with another parameter. The current SQL Server version can cache a single execution plan for a stored procedure. Therefore, the query execution with a different parameter can create a performance issue. SQL 2022 Parameter Sensitive Plan Optimization feature enables storing multiple cache plans for a single parameterized statement. SQL Server chooses the optimized execution plan based on the incoming parameter value. You do not require any code change for using the Parameter Sensitive Plan Optimization feature. Change the compatibility level 160 for the required database and this feature is automatically enabled as a database scoped feature
- Query Store: SQL Server 2022 enables the Query Store feature, by default, for every database
- Peer-to-Peer replica conflict: In the multi-write database environment, users can change the local database data and be replicated in a two-way flow of updates to remain. However, if multiple customers update the same row simultaneously at different write replicas, it can create a data conflict that could stall the whole operation
SQL 2022 automates the last write wins rule for these scenarios. For example, if SQL Server detects any conflicts, it uses (persists) the value of the most recent modification for all replicas. Therefore, you can run a multi-write environment smoothly without conflicts impacting the operation.
What are the next steps for using SQL Server 2022?
Currently, the SQL Server 2022 is in Private preview. Therefore, Microsoft offers a limited number of customers to access the preview before the public preview and general availability. To use the preview version and to work with the Microsoft engineering team, fill the form on https://aka.ms/EAPSignup and answer a few questions such as:
- Please describe the application or workload that you will be testing with SQL Server 2022
- What specific features will you test and validate?
- Which platform do you plan to run SQL Server 2022 on?
- What is your timeline for validation testing or a proof of concept with your application or solution?
- Upon successful validation, when could you deploy SQL Server 2022 in Production or offer SQL Server 2022 as an option in your ISV/SI solution?
Would you be willing to participate in a case study or customer reference that Microsoft can mention in future SQL 2022 announcements?
Microsoft will validate, process your application, and share the next steps over email. We need to wait for the public preview or general availability for the next year, 2022.
Interested to learn about SQL Server 2022
You can watch Bob Ward, a Principal Architect at Microsoft, video talking about the SQL 2022 new features and enhancements using the link https://www.youtube.com/watch?v=ncF-zFzBDAY
To read more about SQL Server 2022 and stay tuned with future enhancements, refer to the following links:
I am delighted to announce that Microsoft SQL Server 2022 Private Preview is now available for early access. This blog covers a snapshot of SQL 2022 features. If you are keen to participate in a private preview (early access), please sign up for the Private Preview. You can collaborate with the Microsoft Engineering team for product review, feedback, early critical bug fixes, critical new features, documentation review.
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023
- Use of the RESTORE FILELISTONLY command in SQL Server - December 21, 2022