SQL Server 2022: Azure Integration, Query Intelligence, Security, and Data Virtualization

SQL Server 2022 is the most Azure-connected release of SQL Server to date. It introduced bidirectional disaster recovery with Azure SQL Managed Instance, real-time analytics replication via Azure Synapse Link, Microsoft Entra ID authentication for on-premises instances, and a substantially expanded Intelligent Query Processing feature set. This article covers each major pillar: Azure integration, query performance, security, high availability, data virtualization, and T-SQL enhancements, with current status notes where features have evolved since the 2022 release.

The Managed Instance link connects an on-premises or IaaS SQL Server instance to Azure SQL Managed Instance using Distributed Availability Groups under the hood. SQL Server 2022 introduced a significant capability increase over earlier versions: bidirectional failover with fail back.

The link operates at the database level, not the instance level. Each database requires its own link. The Managed Instance must be configured with a matching update policy: a SQL Server 2022 instance requires the SQL Server 2022 update policy on the Managed Instance, and a mismatch prevents replication and failover.

Fail back options from Managed Instance to SQL Server 2022 include online fail back using the link directly (with minimal downtime) and offline fail back by taking a backup on Managed Instance and restoring to SQL Server. The online fail back option was in preview as of late 2025; offline fail back is generally available.

Azure Synapse Link for SQL Server 2022 replicates operational data from SQL Server 2022 to a Synapse Analytics dedicated SQL pool using the change feed mechanism, without requiring a separate ETL pipeline. Changes are replicated in near real time, allowing analytical queries to run against a Synapse pool while OLTP workloads continue uninterrupted on SQL Server.

The change feed works by reading the SQL Server transaction log, similar to CDC. When Synapse Link is enabled on a database, the aggressive log truncation feature of Accelerated Database Recovery (ADR) is automatically disabled for that database, because the change feed needs access to the transaction log. This can cause the transaction log to grow faster than usual under high write volume and must be monitored.

Synapse Link supports two replication modes: continuous (changes replicated with low latency as they occur) and batch (changes accumulated and replicated at a configured interval, lower cost). Synapse Link is not a high availability or disaster recovery feature. It is a data replication path for analytics.

SQL Server 2022 introduced support for Microsoft Entra ID authentication for on-premises SQL Server instances on Windows and Linux, and for SQL Server on Azure VMs. This is the first version of SQL Server to support cloud-based identity authentication for instances running outside Azure without additional manual certificate configuration steps (when using Azure Arc registration).

Supported authentication methods include Microsoft Entra Password, Microsoft Entra Integrated (Windows authentication federated through Entra ID), Microsoft Entra Interactive (MFA), Microsoft Entra Managed Identity, and Microsoft Entra Service Principal. The existing SQL authentication and Windows authentication modes remain unchanged and fully supported alongside Entra ID authentication.

SQL Server 2022 integrates with Microsoft Purview for centralized data governance. Purview can discover and catalog on-premises SQL Server data assets, apply sensitivity labels, and manage access policies through the Azure portal. The access policy capability allows granting read or modify access to SQL Server objects directly from Purview without writing T-SQL GRANT statements on each instance.

This integration requires the SQL Server instance to be registered with Azure Arc. Purview integration is primarily relevant for organizations that need centralized governance across a mixed estate of on-premises SQL Server, Azure SQL, and other data sources under a single policy management surface.

SQL Server 2022 expanded the Intelligent Query Processing (IQP) feature set introduced in SQL Server 2019. IQP features adapt query execution based on runtime feedback, stored in Query Store, without requiring code changes to the application or queries. The enhancements in SQL Server 2022 focus on three feedback mechanisms and a new plan optimization feature.

The feedback values stored in Query Store can be queried directly to monitor what IQP is adjusting:

-- View active IQP feedback entries in Query Store
SELECT
    qsp.query_id,
    qspf.feature_id,
    CASE qspf.feature_id
        WHEN 1 THEN 'Memory Grant Feedback'
        WHEN 2 THEN 'CE Feedback'
        WHEN 3 THEN 'DOP Feedback'
    END                          AS feature_name,
    qspf.feedback_data,
    qspf.state_desc,
    qspf.create_time,
    qspf.last_updated_time
FROM sys.query_store_plan_feedback  AS qspf
JOIN sys.query_store_plan           AS qsp
    ON qspf.plan_id = qsp.plan_id
ORDER BY qspf.last_updated_time DESC;
GO

SQL Server 2022 made two significant changes to Query Store behavior that affect every new database on the instance.

Query Store on by default

For new databases created on SQL Server 2022 instances, Query Store is enabled by default in read-write mode. Databases upgraded from earlier versions or restored from backups are not automatically enabled. This change means baseline query performance data is available from day one on new databases without explicit configuration.

Query Store Hints

Query Store Hints allow applying query hints to specific queries identified by their Query Store query ID, without modifying the application code or the query text. Hints are stored in sys.query_store_query_hints and applied at execution time.

-- Apply a MAXDOP hint to a specific query via Query Store
-- Find the query_id first in sys.query_store_query

EXEC sys.sp_query_store_set_hints
    @query_id   = 1234,
    @query_hints = N'OPTION (MAXDOP 4)';
GO

-- View active Query Store Hints
SELECT
    query_hint_id,
    query_id,
    query_hint_text,
    last_query_hint_failure_reason_desc,
    is_enabled_by_system
FROM sys.query_store_query_hints;
GO

Ledger for SQL Server provides tamper-evident tables that generate cryptographic proofs of data integrity. Two table types are available: updatable ledger tables, which record a full history of all changes with cryptographic hashes, and append-only ledger tables, which allow only INSERT operations and are suited to audit and event log scenarios.

Each ledger table generates a corresponding history table and a ledger view that exposes the full before-and-after record for every row change. The database ledger stores block digests (SHA-256 hashes of transaction data) that can be stored externally in Azure Confidential Ledger or Azure Blob Storage and used to verify that historical data has not been altered.

-- Create an updatable ledger table
CREATE TABLE dbo.AccountBalances
(
    AccountID   INT          NOT NULL PRIMARY KEY,
    AccountName NVARCHAR(50) NOT NULL,
    Balance     DECIMAL(18,2) NOT NULL
)
WITH (SYSTEM_VERSIONING = ON, LEDGER = ON);
GO

-- Verify database ledger digest
EXEC sys.sp_generate_database_ledger_digest;
GO

Contained Availability Groups address a long-standing limitation of standard AGs: only user databases are replicated. Logins, SQL Agent jobs, DB Mail profiles, and linked servers stored in master and msdb do not replicate automatically, requiring manual synchronization between replicas. Contained AGs solve this by maintaining AG-local versions of master and msdb that replicate as part of the AG.

When a contained AG named ProductionAG is created, SQL Server creates two additional databases: ProductionAG_master and ProductionAG_msdb. Logins and Agent jobs created through the AG listener are stored in these AG-local system databases and replicate to all replicas automatically.

SQL Server 2022 expanded PolyBase data virtualization with REST API-based connectors built directly into the database engine, removing the need for the PolyBase connector services for Azure and S3 targets. The S3-compatible object storage connector is the headline addition: SQL Server 2022 can query CSV, Parquet, and Delta files stored in any S3-compatible storage (AWS S3, MinIO, Dell ObjectScale, NetApp StorageGRID, and others) using standard T-SQL.

Supported external data sources in SQL Server 2022 via REST API connectors:

The PolyBase service installation is still required for ODBC-based connectors (Oracle, Teradata, MongoDB, generic ODBC). For Azure and S3 REST API targets, the capability is in the engine and requires only that the polybase enabled configuration option is set.

-- Enable PolyBase
EXEC sp_configure 'polybase enabled', 1;
RECONFIGURE;
GO

-- Create a database scoped credential for S3
CREATE DATABASE SCOPED CREDENTIAL s3_cred
WITH IDENTITY = 'S3 Access Key',
     SECRET   = 'AccessKeyID:SecretKeyID';
GO

-- Create an external data source pointing to an S3 bucket
CREATE EXTERNAL DATA SOURCE S3_DataLake
WITH (
    LOCATION   = 's3://s3.us-east-1.amazonaws.com/your-bucket',
    CREDENTIAL = s3_cred
);
GO

-- Query a Parquet file in S3 with T-SQL
SELECT *
FROM OPENROWSET(
    BULK 'sales/2024/transactions.parquet',
    DATA_SOURCE = 'S3_DataLake',
    FORMAT = 'PARQUET'
) AS parquet_data;
GO

SQL Server 2022 also added BACKUP TO URL and RESTORE FROM URL support for S3-compatible storage, allowing backups to be written directly to on-premises or cloud S3 endpoints without Azure Blob Storage as an intermediary.

-- Backup to S3-compatible storage
BACKUP DATABASE AdventureWorks2022
TO URL = 's3://s3.us-east-1.amazonaws.com/sql-backups/AdventureWorks2022_full.bak'
WITH CREDENTIAL = 's3_cred', COMPRESSION, STATS = 10;
GO

SQL Server 2022 introduced a set of T-SQL functions and syntax improvements across JSON handling, date and time, string processing, and bit manipulation.

-- DATETRUNC example: truncate to start of month
SELECT DATETRUNC(MONTH, GETDATE()) AS start_of_month;

-- GENERATE_SERIES: produce a date series
SELECT CAST(DATEADD(DAY, value - 1, '2024-01-01') AS DATE) AS series_date
FROM GENERATE_SERIES(1, 365);

-- JSON_OBJECT: build JSON without string concatenation
SELECT JSON_OBJECT(
    'name'   : FirstName,
    'dept'   : Department,
    'salary' : Salary
)
FROM dbo.Employees;

-- STRING_SPLIT with ordinal (preserves token order)
SELECT value, ordinal
FROM STRING_SPLIT('alpha,beta,gamma', ',', 1)
ORDER BY ordinal;
GO