What Is SQL Server 2022 and What Does It Actually Deliver?

SQL Server 2022 is Microsoft's most cloud-connected release to date, introducing native Azure Arc integration, expanded Linux support, intelligent query processing improvements, and built-in machine learning capabilities. For organisations running on-premises SQL Server infrastructure, it represents a meaningful step forward, not just a version bump with minor refinements.

The challenge with any major SQL Server release is separating genuine capability improvements from marketing noise. This article breaks down what SQL Server 2022 actually delivers, what's worth paying attention to, and what the practical implications are for DBAs and IT managers managing real production environments.

Azure Arc-Enabled SQL Server: Why It Matters for Hybrid Environments

The most operationally significant feature in SQL Server 2022 is Azure Arc integration. This allows you to register on-premises and multi-cloud SQL Server instances with Azure Arc, bringing them under a single management plane alongside your cloud resources.

In practical terms, this means you can:

  • Apply Azure Policy to on-premises SQL Server instances
  • Use Microsoft Defender for Cloud to assess security posture across hybrid environments
  • Enable automatic patching and update management from a central console
  • Access Azure Active Directory authentication for on-premises instances without requiring full Azure AD Domain Services
  • Collect telemetry and performance data into Azure Monitor

For organisations managing SQL Server across multiple sites, cloud providers, or a mix of physical and virtual infrastructure, this is a genuine operational improvement. Before SQL Server 2022, managing patching compliance across a hybrid estate typically required third-party tooling or significant custom scripting. Arc-enabled management brings that under a Microsoft-native framework.

It's worth noting that Arc integration requires connectivity to Azure endpoints. Fully air-gapped environments or those with strict outbound firewall rules will need to evaluate whether this is feasible before treating it as a core part of their management strategy.

What Changed with Linux and Cross-Platform Support?

SQL Server on Linux has been available since SQL Server 2017, but 2022 extends and matures that support. The 2022 release adds support for running on Ubuntu 22.04 LTS and Red Hat Enterprise Linux 9, and improves compatibility with Active Directory authentication in Linux environments.

For organisations that standardised on Linux infrastructure for cost or operational reasons, this matters. Running SQL Server on Linux is no longer a niche configuration. Many production environments are doing it successfully, and Microsoft's investment in the Linux platform has continued with each release.

Container deployments also benefit. SQL Server 2022 container images are available for both Linux and Windows, and the improved Kubernetes operator support makes it more practical to run SQL Server in containerised workloads at production scale.

Intelligent Query Processing: What's New in SQL Server 2022?

SQL Server 2022 extends the Intelligent Query Processing (IQP) feature set that was introduced in SQL Server 2019. These are query optimiser improvements that activate at database compatibility level 160, the default for SQL Server 2022.

Key additions include:

Degree of Parallelism Feedback. The query optimiser can now adjust the degree of parallelism for repeated queries based on observed execution history. If a query is consistently running with excessive parallelism and causing contention, the optimiser learns to reduce it over time. This is particularly useful in environments where DOP settings are inconsistently configured.

Cardinality Estimation Feedback. The optimiser can now correct cardinality estimation errors for repeated queries by storing feedback in the Query Store. Over multiple executions, plans can improve without manual intervention.

Memory Grant Feedback Persistence. Memory grant feedback was introduced in SQL Server 2019 but was reset on service restart. SQL Server 2022 persists this feedback in the Query Store, meaning the improvements survive restarts and failovers.

Parameter Sensitive Plan Optimisation. This addresses a long-standing problem with parameter sniffing. Rather than caching a single plan for a parameterised query, SQL Server 2022 can create multiple plans for different parameter ranges and select the appropriate one at execution time.

These features don't replace the need for proper index design and query tuning. They do, however, reduce the frequency of performance regressions from plan changes and make the optimiser more adaptive over time. Enabling database compatibility level 160 is the primary prerequisite, and it's worth testing your workload at that level before rolling it out to production.

Machine Learning and AI Capabilities in SQL Server 2022

The original article described an "AI-powered query optimizer" - the reality is more nuanced and worth explaining accurately.

SQL Server 2022 does not include a general-purpose AI engine embedded in the database engine itself. What it provides is:

  1. Intelligent Query Processing improvements (described above) that use historical execution data to improve query plans. These are deterministic, rule-based improvements informed by observed statistics, not a neural network.

  2. Machine Learning Services, which has been available since SQL Server 2019 and continues in 2022. This allows you to run Python and R scripts directly within SQL Server using sp_execute_external_script. Common use cases include anomaly detection on time-series data, forecasting models, and text classification.

  3. Azure Machine Learning integration, which allows models trained in Azure ML to be deployed and scored within SQL Server via the PREDICT function. This is genuinely useful for organisations that want to operationalise ML models without moving data out of the database.

  4. Extensibility Framework improvements that reduce the overhead of calling external runtimes, making in-database ML execution more practical for production workloads.

For most organisations, the practical starting point for machine learning in SQL Server 2022 is the PREDICT function combined with a pre-trained model. Training models directly in SQL Server is possible but typically better handled in a dedicated ML environment, with the trained model then deployed back into SQL Server for scoring.

Security Improvements Worth Knowing About

SQL Server 2022 includes several security enhancements that matter in production:

Ledger tables provide tamper-evident history for sensitive data. Rows in a ledger table generate cryptographic hashes that are stored in a blockchain-style structure, making it possible to verify that historical data has not been modified. This is relevant for financial records, audit trails, and compliance scenarios.

Azure Active Directory authentication for on-premises instances (via Arc) removes the dependency on SQL logins for environments that want to centralise identity management.

Always Encrypted with secure enclaves has been extended, allowing richer query operations on encrypted columns without decrypting data outside the enclave.

Contained availability groups simplify the management of logins and jobs in Always On environments by making the availability group self-contained rather than dependent on instance-level configuration.

Should You Upgrade to SQL Server 2022?

The decision to upgrade depends on your current version, your licensing position, and which specific features are relevant to your workload.

If you're running SQL Server 2016 or earlier, SQL Server 2016 reached end of mainstream support in 2021 and extended support ends in July 2026. Upgrading is a near-term necessity regardless of feature interest.

If you're on SQL Server 2019, the Intelligent Query Processing improvements in 2022, particularly parameter sensitive plan optimisation and DOP feedback, are worth evaluating for workloads with complex parameterised queries or parallelism contention.

If you're on SQL Server 2017, you're in a similar position to 2019 in terms of support lifecycle, but you'd gain the full IQP feature set from 2022.

Any upgrade should include compatibility level testing. Running at database compatibility level 160 unlocks the IQP improvements, but it can also expose queries that relied on older optimiser behaviour. A structured test cycle against your actual workload, not just synthetic benchmarks, is essential before committing to production.

Key Takeaways

  • SQL Server 2022's most operationally significant feature is Azure Arc integration, which enables unified management of hybrid SQL Server estates from a single control plane.
  • Intelligent Query Processing improvements at compatibility level 160 include parameter sensitive plan optimisation and persistent memory grant feedback, both of which reduce common performance issues without manual intervention.
  • Machine learning in SQL Server 2022 is practical via the PREDICT function and Azure ML integration, but in-database model training remains a niche use case for most production environments.
  • Ledger tables provide cryptographically verifiable audit history, making SQL Server 2022 a stronger option for compliance-heavy workloads.
  • Any upgrade to SQL Server 2022 requires careful compatibility level testing. Features like IQP are only activated at level 160, and plan regressions are possible without proper pre-production validation.

If you're evaluating an upgrade to SQL Server 2022 or want an independent assessment of whether your current environment is ready, DBA Services provides SQL Server health checks and upgrade assessments for Australian organisations. Our team has managed SQL Server environments through multiple major version upgrades and can help you identify risks, plan the migration, and validate performance before go-live.