SQL Server 2022 is generally available: what to prioritise when you upgrade

SQL Server 2022 is generally available as of today. Microsoft announced it alongside the PASS Data Community Summit and calls it "the most Azure-enabled release of SQL Server yet". That label is accurate, but it hides the fact that some of the most useful changes need no Azure subscription at all.

New major versions tend to land with a long feature list and very little guidance on where to begin. So instead of repeating the list, here is how I would sort it: what helps almost every workload, what helps specific scenarios, and what can wait.

Availability and licensing

Developer and Express editions can be downloaded now. Enterprise and Standard are available through Volume Licensing (Enterprise Agreement and EA Subscriptions) and MPSA from today, while CSP, OEM and SPLA purchasing starts in January 2023.

There is also a new billing option. Through Azure Arc, you can pay by the hour for consumption spikes and ad hoc usage, with no upfront investment. That is worth a conversation with whoever owns your licensing, especially for test environments and seasonal workloads.

The Azure-connected features

Three features carry the hybrid story.

• Azure Synapse Link for SQL. A change feed moves data from SQL Server 2022 into Azure Synapse Analytics dedicated SQL pools in near real time, with minimal effect on the source. The point is to replace the nightly ETL pipeline that copies operational data into the warehouse.
• Link to Azure SQL Managed Instance. A built-in distributed availability group replicates databases to a pre-deployed managed instance, for disaster recovery, migration or read scale-out. Note the footnote in the announcement: the bidirectional disaster recovery capability is in limited public preview, with general availability at a later date.
• Microsoft Purview integration. Purview can scan on-premises SQL Server for free, capture metadata, classify data with built-in and custom classifiers and sensitivity labels, and apply access policies to instances enrolled in Azure Arc.

These all depend on the Azure extension for SQL Server, which setup can now install for you. If your organisation has a firm rule against outbound connections from database servers, settle that discussion before you plan around any of the above.

Query Store and Intelligent Query Processing

This is where I expect most teams to see value first, because it requires no application changes.

Query Store is now enabled by default for new databases. That matters, because several of the new Intelligent Query Processing features depend on it:

• Parameter Sensitive Plan optimization allows multiple active cached plans for a single parameterized statement, so a procedure that serves both a tiny customer and a huge one no longer has to live with one plan for both. It currently works with equality predicates.
• Degree of parallelism feedback adjusts parallelism for repeating queries where it is inefficient.
• Cardinality estimation feedback corrects plans for repeating queries when the estimation model's assumptions are wrong.
• Memory grant feedback gains percentile and persistence modes, so feedback survives plan cache evictions.

Query Store hints, previously only in Azure SQL, also arrive. You can shape a query plan without touching the code, which is a real help when the code belongs to a vendor.

One important detail: Parameter Sensitive Plan optimization requires database compatibility level 160. Databases that you restore or upgrade in place keep their previous Query Store settings, and Microsoft advises evaluating the compatibility level separately, because some of these features are enabled by it. Upgrading the engine alone is not the whole job.

Contained availability groups and ledger

Contained availability groups solve an old annoyance. The group manages its own users, logins, permissions and SQL Agent jobs, with its own contained system databases. Anyone who has chased a missing login after a failover will appreciate that.

Ledger adds tamper evidence to your data. You can cryptographically attest to auditors or business partners that data has not been tampered with. It is not something every database needs, but for financial records, audit trails and regulated data, it is worth a proof of concept.

What to prioritise when upgrading

If I were planning an upgrade from SQL Server 2016, 2017 or 2019 today, this is the order I would follow:

1. Check your drivers. SQL Server Native Client is no longer shipped with SQL Server 2022. Find applications and linked servers that still use SQLNCLI or the legacy SQLOLEDB provider, and plan a move to the current ODBC or OLE DB drivers.
2. Upgrade the engine, keep the compatibility level. Get a baseline in Query Store on the old compatibility level first.
3. Raise to compatibility level 160, one database at a time. Compare Query Store data before and after, and use Query Store hints or plan forcing on the few queries that regress.
4. Look at contained availability groups if you run Always On and spend time syncing logins and jobs between replicas.
5. Evaluate the Azure features against a real need. Synapse Link if you already run Synapse dedicated SQL pools and fight with ETL latency. The Managed Instance link if disaster recovery or migration to Azure is on the roadmap.
6. Pilot ledger on one table where tamper evidence has a clear business owner.

Also get the matching tools. SSMS 19 is the recommended version for SQL Server 2022.

Takeaway

After two decades in data, I find that the boring parts of a release often deliver the most. Here that means Query Store on by default and the new feedback features, which reward a careful compatibility level upgrade. The Azure links are useful too, but only when they answer a question you already have.

Which part of SQL Server 2022 would make the biggest difference in your estate: the query processing improvements, contained availability groups, or the Azure links?

Sources

• SQL Server 2022 is now generally available (SQL Server blog)
• What's new in SQL Server 2022 (Microsoft Learn)
• Parameter Sensitive Plan optimization (Microsoft Learn)