Announcing SQL Server 2022 preview: Azure-enabled with continued performance and security innovation

The rise of data represents a tremendous opportunity and also poses challenges. Companies are seeing their relational and nonrelational data proliferate exponentially on-premises, in the cloud, at the edge, and in hybrid environments. The most transformative companies drive predictive insights on current data, whereas others may struggle to drive even reactive insights to their historical data. Information may be siloed across geographies and divisions.

To empower customers amid this environment, Microsoft offers an end-to-end data platform of products and services that come together to meet these challenges. Operational databases cover all possible deployment locations, including SQL Server and Azure Arc-enabled data services, Azure SQL fully-managed cloud databases, and Azure SQL Edge for IoT devices. To enable real-time insights, Azure Synapse Analytics brings together data integration, enterprise data warehousing, and big data analytics, and customers can visualize their data with Power BI. Customers can discover, catalog, and govern their data wherever it resides with Azure Purview.

SQL Server 2022 integrates with Azure Synapse Link and Azure Purview to enable customers to drive deeper insights, predictions, and governance from their data at scale. Cloud integration is enhanced with disaster recovery (DR) to Azure SQL Managed Instance, along with no-ETL (extract, transform, and load) connections to cloud analytics, which allow database administrators to manage their data estates with greater flexibility and minimal impact to the end-user. Performance and scalability are automatically enhanced via built-in query intelligence. There is choice and flexibility across languages and platforms, including Linux, Windows, and Kubernetes.

Azure-enabled

Bi-directional HA/DR to Azure SQL

To ensure uptime, SQL Server 2022 is fully integrated with the new link feature in Azure SQL Managed Instance. With the new link feature for Azure SQL Managed Instance, you now get all the benefits of running a PaaS environment applied to disaster recovery—allowing you to spend less time on setup and management even when compared to an IaaS environment. This works by using a built-in Distributed Availability Group (DAG) to replicate data to a previously deployed Azure SQL Managed Instance as a DR replica site. The instance is ready and waiting for whenever you need it—no lengthy configuration or maintenance is required. You can also use this link feature in read scale-out scenarios to offload heavy requests that might otherwise affect database performance. And we are working on building out more capabilities to support bi-directional data movement.

Azure Synapse Link

Previously, moving data from on-premises databases, like SQL Server, to Synapse required you to use ETL. As we all know, setting up and running an ETL pipeline takes a lot of work, and insights lag behind what is happening at any moment. Azure Synapse Link for SQL Server 2022 provides automatic change feeds that capture the changes within SQL Server and feed those into Azure Synapse Analytics. It provides near real-time analysis and hybrid transactional and analytical processing with minimal impact on operational systems. Once the data hits Synapse, you can combine it with many different data sources regardless of their size, scale, or format and run powerful analytics over all of it using your choice of Azure Machine Learning, Spark, or Power BI. Since the automated change feeds only push what is new or different, data transfer happens much faster and now allows for near real-time insights, with minimal impact on the performance of the source database in SQL Server 2022.

Azure Purview integration

We recently announced the general availability of Azure Purview as a unified data governance and management service. We are excited to highlight that SQL Server is also integrated with Azure Purview for greater data discovery, allowing you to break down data silos. Through this integration you will be able to:

• Automatically scan your on-premises SQL Server for free to capture metadata.
• Classify data using built-in and custom classifiers and Microsoft Information Protection sensitivity labels.
• Set up and control specific access rights to SQL Server.

Enhancements to performance, security, and availability

Performance

SQL Server offers differentiated performance, with number one OLTP performance¹ and number one Non-Clustered DW performance on 1TB², 3TB³, 10TB⁴, and 30TB⁵ according to the independent Transaction Processing Performance Council. Built-in query intelligence in SQL Server 2022 innovation includes:

• For Query Store, we are adding support for read replicas and enabling query hints to improve performance and quickly mitigate issues without having to change the source T-SQL.
• For Intelligent Query Processing, we’re expanding more scenarios based on common customer problems. For example, the “parameter sensitive plan” problem refers to a scenario where a single cached plan for a parameterized query is not optimal for all possible incoming parameter values. With SQL Server 2022’s Parameter Sensitive Plan optimization feature, we automatically enable the generation of multiple active cached plans for a single parameterized statement. These cached execution plans will accommodate different data sizes based on the provided runtime parameter values.

Security

Over the past ten years, SQL Server has had fewer vulnerabilities than the competition.⁶ Building on this, the new ledger feature creates an immutable track record of data modifications over time. This protects data from tampering by malicious actors and is beneficial for scenarios such as internal and external audits.

Availability

With the move to a more global distribution of workers and customers, many organizations are moving to a multi-write environment that allows changes to be made to the local database and pushed out to other replicas in a two-way flow of updates. However, if multiple people change the same row in the database and the different write replicas have different information in them, previously this peer-to-peer replica conflict would stall the whole operation until it was addressed. With SQL Server 2022, we are automating the last-writer wins rule. Now, when a conflict is detected, the most recent modification time will be chosen to be persisted on all replicas. This helps keep your multi-write scenarios running smoothly.