--- title: "SQL Server 2025 Upgrades: Avoid These Breaking Changes" description: "Avoid SQL Server 2025 upgrade failures. Master the breaking changes in encryption, TDS, and replication before you plan your migration." canonical: "https://adamtheautomator.com/sql-server-upgrade-breaking-changes/" --- # SQL Server 2025 Upgrades: Avoid These Breaking Changes > Avoid SQL Server 2025 upgrade failures. Master the breaking changes in encryption, TDS, and replication before you plan your migration. Source: https://adamtheautomator.com/sql-server-upgrade-breaking-changes/ --- ATA Learning Tap to hide [ ATA Learning ](/) * [Home](/) * [Tutorials](/tutorials/) * [Instructors](/author/) * [Advertising](/advertising/) * [Recommended Resources](/resources/) * [About Adam](/about-adam/) Search for: * [](https://twitter.com/adbertram) * [](https://github.com/Adam-the-Automator) * [](https://www.linkedin.com/company/adam-the-automator-llc) * [](/feed/) ![SQL Server 2025 Upgrades: Avoid These Breaking Changes](https://adamtheautomator.com/wp-content/uploads/publisher/2e05d9c85b2b8184bb24f8beaeb7f165/85751120c0fb4909afa44fc19e072a60152e0de73a44e1bba3c2dc4f2c71b424.webp) # SQL Server 2025 Upgrades: Avoid These Breaking Changes [![](https://secure.gravatar.com/avatar/d0b9d42e21e5622713f8b693aa5c0f9244d5f7dd200ed29b8398f52dee5de337?s=192&d=mm&r=g)Adam Bertram](https://adamtheautomator.com/author/adam-bertram/)18 September 202616 min. read Categories: [IT Ops](/category/it-ops/) Tags:[Microsoft SQL Server](/tag/microsoft-sql-server/)[Databases](/tag/databases/)[Security](/tag/security/) Table of Contents * [Encryption Stopped Being Optional, and That’s What Breaks Your Upgrade](#encryption-stopped-being-optional-and-thats-what-breaks-your-upgrade) * [Full-Text Search Breaks in a Different Way](#full-text-search-breaks-in-a-different-way) * [What SQL Server 2025 Leaves Behind](#what-sql-server-2025-leaves-behind) * [Infrastructure and Analytics Casualties](#infrastructure-and-analytics-casualties) * [Picking Your Upgrade Path From SQL Server 2012 Through 2019](#picking-your-upgrade-path-from-sql-server-2012-through-2019) * [Supported Upgrade Paths by Source Version](#supported-upgrade-paths-by-source-version) * [Stage Compatibility Level Before You Trust the New Optimizer](#stage-compatibility-level-before-you-trust-the-new-optimizer) * [Raise Compatibility Level After You Have a Baseline](#raise-compatibility-level-after-you-have-a-baseline) * [The Standard Edition Math Just Changed](#the-standard-edition-math-just-changed) * [The Standard Developer Edition Closes a Real Testing Gap](#the-standard-developer-edition-closes-a-real-testing-gap) * [SQL Server Stops Treating Vectors as Someone Else’s Problem](#sql-server-stops-treating-vectors-as-someone-elses-problem) * [How the Vector Type Stores and Limits Data](#how-the-vector-type-stores-and-limits-data) * [Generating Embeddings From T-SQL](#generating-embeddings-from-t-sql) * [JSON and Pattern Matching Move Into the Engine Itself](#json-and-pattern-matching-move-into-the-engine-itself) * [Regular Expressions Replace the LIKE Workarounds](#regular-expressions-replace-the-like-workarounds) * [The Optimizer Finally Handles “Maybe This Parameter, Maybe Not”](#the-optimizer-finally-handles-maybe-this-parameter-maybe-not) * [Cardinality Estimation Learns From Its Own Mistakes](#cardinality-estimation-learns-from-its-own-mistakes) * [TempDB Stops Being a Silent Blast Radius](#tempdb-stops-being-a-silent-blast-radius) * [What Happens When a Group Hits Its Limit](#what-happens-when-a-group-hits-its-limit) * [Backups Get Their Own Compression Upgrade](#backups-get-their-own-compression-upgrade) * [Decide Before the Maintenance Window, Not During It](#decide-before-the-maintenance-window-not-during-it) Before you schedule that SQL Server 2025 upgrade, assume every linked server, replication topology, and log shipping pair in your environment will refuse to reconnect on the first attempt. Microsoft changed the default encryption behavior of its OLE DB driver for this release, and the teams who treat a major version upgrade like a routine cumulative update (schedule the window, click through the wizard, go home) are the ones fielding the page when the distributor can’t authenticate at 2 a.m. Do not book the maintenance window yet. This release also moves the Standard edition licensing ceiling and rewrites how the optimizer caches plans, and none of that announces itself in the upgrade wizard. Start with the certificate problem anyway: it is the one that can break right after Setup finishes. ## Encryption Stopped Being Optional, and That’s What Breaks Your Upgrade TLS 1.3 support for SQL Server connections first shipped in SQL Server 2022. SQL Server 2025 extends [TLS 1.3 with TDS 8.0](https://learn.microsoft.com/en-us/sql/relational-databases/security/networking/tls-1-3?view=sql-server-ver17) across nearly every internal component: SQL Server Agent, Always On availability groups and failover cluster instances, linked servers, transactional and merge replication, log shipping, and Database Mail. Under TDS 8.0, encryption starts on the first byte of the connection, which removes the older unencrypted pre-login negotiation phase entirely and, for clients that set `Encrypt=Strict`, means a self-signed certificate that isn’t trusted or pinned fails the connection. This encryption change is what breaks upgrades: OLE DB Driver 19 turns encryption on by default, and OLE DB Driver 18 left it off. Microsoft’s [breaking changes documentation for SQL Server 2025](https://learn.microsoft.com/en-us/sql/database-engine/breaking-changes-to-database-engine-features-in-sql-server-2025?view=sql-server-ver17) lists linked servers, replication (transactional, snapshot, peer-to-peer, and merge), and remote log shipping monitoring as components that can fail after an in-place upgrade. Replication is at risk when the instance is a publisher with a remote distributor and no trusted certificate, and log shipping monitoring breaks when a remote SQL Server 2025 monitor watches instances that run an earlier version. The failure mode isn’t a clean error at the top of the log; replication can keep succeeding on existing subscriptions while changes to the publication itself start failing, and [**Replication Monitor**](https://learn.microsoft.com/sql/relational-databases/replication/monitor/start-the-replication-monitor?view=sql-server-ver17) stops working in [SSMS](https://adamtheautomator.com/sql-server-management-studio/). If you’d rather not chase that down after the fact, provision a certificate from a public commercial CA or your internal CA before you upgrade. If replication already broke after an upgrade, the less secure override is `sp_changedistributor_property @property = N'trust_distributor_certificate', @value = N'yes'`, which lets the publisher trust the distributor’s self-signed certificate; run it on each publisher that uses a remote distributor. ### Full-Text Search Breaks in a Different Way Full-Text Search hits a different kind of breaking change. SQL Server 2025 removes the legacy word breaker and filter binaries entirely and rebuilds Full-Text Search on a modern toolset. Every index that existed before the upgrade gets marked `index_version = 1`, and any full-text query against a version 1 index fails immediately after upgrade with an error that names the real cause directly: ```text Msg 30010, Level 16, State 2, Line 8 An error has occurred during the full-text query. Common causes include: word-breaking errors or timeout, FDHOST permissions/ACL issues... ``` The fix is to rebuild or recreate the affected full-text indexes so they use version 2 components. Permissions were never the problem, so chasing an ACL error here just burns time. What breaks after an upgrade, in short: * Linked servers to SQL Server 2025 instances without a trusted certificate * Transactional, snapshot, peer-to-peer, and merge replication when the publisher uses a remote distributor without a trusted certificate * Remote log shipping monitoring from a SQL Server 2025 instance when other instances in the topology run an earlier version * Full-text queries and index population against pre-upgrade indexes (`index_version = 1`) Password security tightens the same way encryption does. SQL Server 2025 [switches to PBKDF2 for password hashes by default](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql?view=sql-server-ver17), an iterated hash aligned with [NIST Special Publication 800-63B-4](https://csrc.nist.gov/pubs/sp/800/63/b/4/final), which calls for passwords to be salted and hashed with a suitable scheme at a cost factor as high as practical, and that is the guidance your auditor cites when a legacy hash shows up in a control review. Login-specific cache invalidation is the quieter half of the same change: a permission change for an individual login no longer invalidates other logins’ cached entries, though group logins still cause server-level invalidation. Neither change requires application code changes, but both are one more reason a “just click next” upgrade plan is the wrong plan. ## What SQL Server 2025 Leaves Behind Modernizing an engine this size means retiring subsystems that stopped getting real investment years ago. Microsoft Learn states plainly: “Master Data Services (MDS) is discontinued in this version of SQL Server.” Data Quality Services is discontinued alongside it, and Purview access policies (the DevOps and data-owner policy types) are gone too, replaced by fixed server roles like [`##MS_ServerSecurityStateReader##`](https://learn.microsoft.com/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver17#fixed-server-level-roles-introduced-in-sql-server-2022). If your organization still has an MDS or DQS database in production, those databases stop functioning the moment you upgrade the instance; there’s no compatibility shim. SQL Server 2022 continues to support both, so the practical answer for most teams is to keep MDS or DQS on a 2022 instance while migrating the governance workload to something else, like [Microsoft Purview](https://learn.microsoft.com/purview/purview) or a third-party MDM platform, on your own timeline rather than the upgrade’s timeline. ### Infrastructure and Analytics Casualties Synapse Link is also discontinued in this release; Microsoft’s replacement path is [Fabric Mirroring](https://learn.microsoft.com/en-us/fabric/database/mirrored-database/overview), which continuously replicates SQL Server OLTP data into Microsoft Fabric’s OneLake with an architecture different enough that it needs its own migration plan. On the infrastructure side, the Hot-Add CPU feature and the lightweight pooling (fiber mode) configuration option are both deprecated and planned for removal in a future version, which mostly affects older virtualized deployments that never needed either workaround once NUMA-aware scheduling matured. Web edition is gone from new deployments entirely; existing 2022 installs keep support, but new work needs to land on Standard or Azure SQL Database instead. * Master Data Services (MDS): discontinued, still supported on SQL Server 2022 * Data Quality Services (DQS): discontinued, still supported on SQL Server 2022 * Synapse Link: discontinued, replaced by Fabric Mirroring * Purview DevOps and data-owner access policies: discontinued, replaced by fixed server roles * Web edition: discontinued for new deployments * Hot-Add CPU and lightweight pooling (fiber mode): deprecated, planned for future removal ## Picking Your Upgrade Path From SQL Server 2012 Through 2019 [SQL Server 2025 supports direct in-place upgrade](https://learn.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades-2025?view=sql-server-ver17) from 2014 SP3 or later, 2016 SP3 or later, 2017, 2019, and 2022. SQL Server 2012 isn’t on that list; its Extended Security Updates lifecycle ended in July 2025, and there’s no supported direct path to 2025. If you’re still running 2012, the route is a side-by-side migration (build a new 2025 instance and move databases over by backup and restore, or by detach and attach), a hop through an intermediate version, or [a move to Azure SQL Database](https://adamtheautomator.com/migrate-sql-server-azure-sql-database/) instead of another on-premises build. Either way, that’s a materially different project timeline than an in-place upgrade, and it needs its own line item in the plan rather than an assumption that it’ll behave like every other version on the list. ### Supported Upgrade Paths by Source Version Here’s every legacy version in that range next to the path it supports: | Source Version | Direct In-Place Upgrade To 2025 | Notes | | --- | --- | --- | | SQL Server 2012 | Not supported | ESU lifecycle ended July 2025; requires side-by-side migration or a hop through an intermediate version | | SQL Server 2014 SP3+ | Supported | 64-bit target only | | SQL Server 2016 SP3+ | Supported | 64-bit target only | | SQL Server 2017 | Supported | 64-bit target only | | SQL Server 2019 | Supported | 64-bit target only | | SQL Server 2022 | Supported | 64-bit target only | SQL Server 2025 is 64-bit only, and cross-platform upgrade isn’t supported by Setup for any source version, so a 32-bit instance needs the same backup-and-restore (or detach-and-attach) path described above for SQL Server 2012 (provided the databases aren’t published in replication), with logins and other user objects in `master`, `msdb`, and `model` re-created on the new instance. Staying in place is faster and needs no second server, but leaves less rollback room if something goes wrong; moving side by side costs more storage and time, but keeps the old instance intact as a fallback, as the chart below lays out. ![Upgrade path tradeoffs](https://adamtheautomator.com/wp-content/uploads/publisher/39f2e10b9d28ce13e67b55a25c7377cc7435254f84133a97ecece7c81e685aa8.jpg) The version you’re coming from decides the shape of the project. What decides whether the upgrade succeeds is the sequence you run it in. ## Stage Compatibility Level Before You Trust the New Optimizer Run the [SQL Server Management Studio migration component](https://learn.microsoft.com/en-us/ssms/migrate-sql-server-component) against your existing instance first, before you touch the upgrade itself; it needs SSMS 21 or later and doesn’t support databases with transparent data encryption. The [Data Migration Assistant](https://learn.microsoft.com/sql/dma/dma-overview?view=sql-server-ver17) was retired in July 2025 and doesn’t list SQL Server 2025 as a target, so don’t build your plan around it. The migration component produces an assessment report of issues specific to your environment rather than the generic list above, so check that report against the breaking-changes list for MDS databases, 32-bit SSIS packages, deprecated cryptographic protocols, and full-text catalogs, and give each blocking item its own owner and deadline. Provision the certificates the TLS 1.3 and TDS 8.0 changes require second, while the assessment report is still open. Linked servers, and replication where a publisher connects to a remote distributor, should each present a certificate from a trusted CA (a public or an internal CA both work), because the OLE DB Driver 19 connections behind them default to `TrustServerCertificate=False`. Do this before the SQL Server 2025 upgrade: a missing trusted certificate here breaks linked servers and replication the moment TDS 8.0 takes effect. ### Raise Compatibility Level After You Have a Baseline Once the instance is upgraded, the database stays on its old compatibility level until you explicitly raise it. Microsoft built that gap on purpose, so you get a window to run production traffic on the new engine version before new optimizer behavior kicks in. Capture a performance baseline in [Query Store](https://learn.microsoft.com/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver17) before you touch the compatibility level, the discipline behind any [performance tuning](https://adamtheautomator.com/azure-sql-performance-tuning/) effort, then raise it once you’re ready to measure against that baseline: ```sql ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 170; ``` Because the intelligent query processing features in SQL Server 2025 change cardinality estimation and parameter handling, plans will shift. [Optimized Locking](https://learn.microsoft.com/sql/relational-databases/performance/optimized-locking?view=sql-server-ver17) is a separate switch: it is off by default and not gated by compatibility level 170, so you enable it per database with `ALTER DATABASE [YourDatabase] SET OPTIMIZED_LOCKING = ON`, which requires accelerated database recovery (ADR). Turned on, it changes how the engine escalates and holds locks, so blocking patterns shift alongside the plans, and read committed snapshot isolation (RCSI) adds lock after qualification on top. Capture both in the baseline, not just query duration. [Automatic plan correction](https://learn.microsoft.com/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-ver17#automatic-plan-correction), which is part of automatic tuning (Enterprise edition only), reads the baseline Query Store collected and forces the last known good plan back for any query that regresses, and you turn it on per database: ```sql ALTER DATABASE [YourDatabase] SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); ``` Query Store has to be enabled on that database first, or the setting has nothing to read. ## The Standard Edition Math Just Changed SQL Server 2025 (17.x) [raises the compute and memory ceilings for Standard edition](https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2025?view=sql-server-ver17): a single instance can now use the lesser of 4 sockets or 32 cores, up from 24 cores in SQL Server 2022 and earlier, and the buffer pool per instance doubles from 128 GB to 256 GB. The buffer pool extension cap for Standard on physical machines rises too, from 512 GB in 2022 to 1 TB in 2025. That ceiling used to sit low enough that architects routinely pushed transactional workloads onto Enterprise licensing years before those workloads needed a single Enterprise-only feature. A 200 GB working set that used to spill out of Standard’s 128 GB buffer pool and force physical reads now fits comfortably inside 256 GB. If you’ve been paying Enterprise prices to buy yourself memory headroom rather than to use Enterprise features like batch mode on rowstore or online index rebuilds, this release is the moment to re-run that [Standard edition](https://adamtheautomator.com/microsoft-sql-server-standard-edition/) math. [Resource Governor](https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server-ver17) itself moves into Standard and Standard Developer edition with the same functionality it has in Enterprise, which matters a lot once you get to the TempDB section below. Express edition stays capped at a [50 GB maximum database size](https://learn.microsoft.com/sql/sql-server/editions-and-components-of-sql-server-2025?view=sql-server-ver17), so it is not the landing spot for anything that outgrew Standard. Licensing changes here too, and one of them fixes a real operational failure mode. ### The Standard Developer Edition Closes a Real Testing Gap Every edition of SQL Server before 2025 shipped one free Developer edition, and it had full Enterprise feature parity. That sounds generous until you notice what it enables: a developer builds and load-tests a feature against Enterprise-only behavior (batch mode processing over rowstore, larger buffer pools, Resource Governor), then ships it to a Standard edition production server that silently can’t do any of it. The bug doesn’t show up in dev. It shows up in production, under load. SQL Server 2025 splits Developer edition in two. [Standard Developer edition](https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2025?view=sql-server-ver17) is free and includes all features of Standard edition, so what you certify in a staging environment matches what production runs. The old full-parity Developer edition didn’t disappear; it’s Enterprise Developer edition now, and it’s still free for building against Enterprise features on purpose. Pick the edition that matches your production target instead of defaulting to the one that used to be the only option. ## SQL Server Stops Treating Vectors as Someone Else’s Problem Every example in this article targets SQL Server 2025 (17.x) at database compatibility level 170, the default for new databases on this release. If you’ve built retrieval-augmented generation or semantic search on top of SQL Server before, the architecture was usually the same: keep relational data where it lives and export the text you want to search into a separate vector database like [Pinecone](https://www.pinecone.io/) or [Milvus](https://milvus.io/). You generate embeddings there, then query across two systems with two consistency models. SQL Server 2025 adds a native [`VECTOR` data type](https://learn.microsoft.com/en-us/sql/t-sql/data-types/vector-data-type?view=sql-server-ver17) (one of several new additions to [SQL Server’s data types](https://adamtheautomator.com/sql-server-data-types/)) that stores embeddings directly in a relational table, right next to the row they describe. ```sql CREATE TABLE dbo.DocumentEmbeddings ( DocumentId INT PRIMARY KEY, Content NVARCHAR(MAX), Embedding VECTOR(4) NOT NULL ); INSERT INTO dbo.DocumentEmbeddings (DocumentId, Content, Embedding) VALUES (1, 'Troubleshooting a failed login', '[0.1, 2, 30, -100.2]'); ``` Match the column width to the output dimension of whichever embedding model you use. A real deployment with a 768-dimension model declares `VECTOR(768)`; this example uses 4 dimensions only to keep the sample runnable as printed. ### How the Vector Type Stores and Limits Data The engine stores that vector in an optimized binary format but exposes it as a JSON array, so any client that already speaks JSON can read and write it without a special driver. A `VECTOR` column supports up to 1,998 dimensions, and by default each element is a single-precision `float32` value; half-precision `float16` storage is available if you enable preview features and need to trade a small amount of accuracy for a smaller footprint. What a `VECTOR` column won’t do is act like an ordinary column: it takes no `DEFAULT` or `CHECK` constraint and cannot be a primary or foreign key. It also can’t be an index key: a B-tree index built on the column itself is rejected, though a vector column can still ride along as an `INCLUDE` column. Similarity search runs instead through [`VECTOR_DISTANCE`](https://learn.microsoft.com/en-us/sql/t-sql/functions/vector-distance-transact-sql?view=sql-server-ver17) and approximate nearest-neighbor vector indexes, which are still in preview in SQL Server 2025. `CREATE VECTOR INDEX` needs the `PREVIEW_FEATURES` database scoped configuration turned on, and on SQL Server 2025 the table becomes read-only once the vector index is created. With either approach, a search for “login failure” can surface a row whose text says “authentication error” without either string sharing a keyword. The traditional stack keeps relational data in SQL Server and embeddings in a separate vector database, each with its own consistency model, while SQL Server 2025 puts relational tables and VECTOR columns inside one box, as the diagram below shows. ![Vector stack comparison](https://adamtheautomator.com/wp-content/uploads/publisher/07635164eddd440c8479f193874a0810e76e2b96721313f3e4c8b7f883d136c6.jpg) ### Generating Embeddings From T-SQL T-SQL can also generate those embeddings. [`sp_invoke_external_rest_endpoint`](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint-transact-sql?view=sql-server-ver17) lets T-SQL call an external REST endpoint, including [Azure OpenAI](https://learn.microsoft.com/azure/ai-foundry/openai/overview), directly from a stored procedure once you turn on the `external rest endpoint enabled` server option with `sp_configure` (it is off by default), and [`AI_GENERATE_EMBEDDINGS`](https://learn.microsoft.com/en-us/sql/t-sql/functions/ai-generate-embeddings-transact-sql?view=sql-server-ver17) turns text into a vector array using a model definition you registered ahead of time with [`CREATE EXTERNAL MODEL`](https://learn.microsoft.com/sql/t-sql/statements/create-external-model-transact-sql?view=sql-server-ver17). That model definition points at an embedding endpoint: Azure OpenAI, OpenAI, an Ollama server, or an ONNX Runtime model running on the instance itself (Windows only). The function sends your text to a remote endpoint, so the text leaves the database process unless you use the local ONNX Runtime model. Sentiment analysis and summarization are not built into SQL Server 2025; the `AI_ANALYZE_SENTIMENT` and `AI_SUMMARIZE` functions exist only in Fabric. For anyone still writing raw T-SQL by hand, [GitHub Copilot](https://learn.microsoft.com/ssms/github-copilot/overview) sits inside SQL Server Management Studio (requires SSMS 22) and can turn a plain-English question into a query or, through its `/explain` command, explain a selected query you didn’t write. ## JSON and Pattern Matching Move Into the Engine Itself SQL Server has parsed JSON with functions like `JSON_VALUE` and `OPENJSON` since 2016, but the documents themselves were always stored as plain `NVARCHAR(MAX)` text. SQL Server 2025 promotes JSON to a [native binary data type](https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver17) that holds documents up to 2 GB, and the engine can now build a [`CREATE JSON INDEX`](https://learn.microsoft.com/sql/t-sql/statements/create-json-index-transact-sql?view=sql-server-ver17) directly against paths inside that document. Before this release, indexing a JSON property meant creating a computed column for each property you cared about and indexing the computed column, a workaround that scales badly once a document has more than a handful of properties worth querying. [`JSON_CONTAINS`](https://learn.microsoft.com/sql/t-sql/functions/json-contains-transact-sql?view=sql-server-ver17) then checks whether a value, object, or array exists at a given path and returns `1` if it does and `0` if it doesn’t (or `NULL` if any argument is `NULL` or the path isn’t found), and when that lookup runs against a JSON index instead of a full document scan, the difference shows up immediately in the query plan. ### Regular Expressions Replace the LIKE Workarounds The other long-requested addition is native [regular expression support](https://learn.microsoft.com/en-us/sql/relational-databases/regular-expressions/overview?view=sql-server-ver17) in T-SQL, through functions like `REGEXP_LIKE`, `REGEXP_REPLACE`, and `REGEXP_SUBSTR`. Before this, validating or extracting patterned text (a malformed phone number, or an email address with a typo in the domain) usually meant either chaining multiple `LIKE` and `PATINDEX` calls that couldn’t express real pattern logic, or registering a CLR assembly and accepting the deployment and security overhead that comes with it. A single `REGEXP_LIKE(Column, '^[0-9]{3}-[0-9]{4}$')` now replaces both approaches for anything a standard regex can express. It won’t replace a CLR function for genuinely custom logic, but for the validation and extraction work that’s actually pattern matching, it removes a dependency most teams didn’t want to maintain in the first place. The optimizer changes in this release are less visible than a new function, and they reach queries you already shipped. ## The Optimizer Finally Handles “Maybe This Parameter, Maybe Not” If you’ve ever written a search procedure with a predicate shaped like `WHERE (Column = @Param OR @Param IS NULL)`, you’ve hit the problem [Optional Parameter Plan Optimization](https://learn.microsoft.com/en-us/sql/relational-databases/performance/optional-parameter-optimization?view=sql-server-ver17) (OPPO) fixes. Microsoft Learn describes the failure mode directly: “Without OPPO, the SQL Server Database Engine must compile and cache a single execution plan that’s valid for both cases.” Because a seek plan isn’t valid when the parameter is `NULL`, the optimizer typically settles on a conservative, scan-based plan for every execution, so even the highly selective executions that a seek would have handled in milliseconds pay for a scan. OPPO builds on the same adaptive plan infrastructure introduced for parameter-sensitive plans in SQL Server 2022, but instead of branching on estimated row counts, it branches on whether the parameter is actually `NULL` at runtime. The engine compiles a dispatcher plan plus a separate query variant for each parameter state, and picks the right variant at execution time without you rewriting the query or adding `OPTION (RECOMPILE)`. `OPTIONAL_PARAMETER_OPTIMIZATION` defaults to `ON` at compatibility level 170, and the official OPPO reference shows how to set it explicitly to be sure, because a database-scoped configuration a prior DBA turned off doesn’t turn itself back on just because you raised the compatibility level: ```sql ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 170; ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON; ``` Run both statements even though the second one is redundant on a fresh database: it’s the only way to be certain OPPO is active rather than assumed active. ### Cardinality Estimation Learns From Its Own Mistakes Cardinality estimation gets a related upgrade: the optimizer now applies [feedback to expressions](https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing-ce-feedback-for-expressions?view=sql-server-ver17) as well as whole queries, learning from how a repeating filter or join expression actually behaved last time and adjusting the estimate for next time; expression feedback needs compatibility level 160 or later. Whole-query CE feedback (2022) and expression feedback (2025) are both Enterprise edition only. Combined with degree-of-parallelism feedback, which is also Enterprise edition only and arrived in SQL Server 2022 rather than 2025, the pattern across [intelligent query processing](https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-ver17) in SQL Server 2025 is the same one you’ve seen in every IQP release since 2017: the engine corrects itself at runtime instead of waiting for a DBA to notice a bad plan in production. That self-correction fixes plans the optimizer already chose; it has nothing to say about a single query that blows up the shared workspace every other session on the instance depends on. ## TempDB Stops Being a Silent Blast Radius TempDB is shared by every session on the instance, which means one poorly written query building an enormous worktable can starve every other workload on the server, and there was never a clean way to stop it short of killing the offending session after the damage was already done. [TempDB space resource governance](https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/tempdb-space-resource-governance?view=sql-server-ver17) extends Resource Governor to enforce a hard space limit per workload group, so a runaway reporting query is capped at the TempDB space you’ve allocated to reporting. ```sql CREATE WORKLOAD GROUP AdHocReporting WITH ( GROUP_MAX_TEMPDB_DATA_MB = 20000 ) USING [default]; ``` ### What Happens When a Group Hits Its Limit When a session in that group tries to push TempDB consumption past its limit on an instance where Resource Governor is enabled, Resource Governor aborts the request with error 1138 rather than letting it keep growing. That’s a deliberate, bounded failure for one workload group instead of an unbounded one for the whole instance. * * * _**Warning: Don’t apply a TempDB space limit to the _**`default`**_ workload group unless you’ve built a classifier function to route real workloads elsewhere first. Microsoft’s own guidance warns that a zero or very small limit on the _**`default`**_ group can make routine operations, including opening Object Explorer in SQL Server Management Studio, fail because they can’t allocate TempDB space, and that limiting it without custom workload groups and a classifier function can return error 1138 while TempDB still has unused space.**_ * * * Budget real headroom per workload group before you flip this on, or the query you meant to cap becomes the one paging you instead. Backup compression gets its own upgrade in this release too, unrelated to TempDB governance but worth knowing before your next maintenance window. ## Backups Get Their Own Compression Upgrade SQL Server 2025 adds a new [Zstandard (ZSTD) backup compression algorithm](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server?view=sql-server-ver17) alongside the long-standing `MS_XPRESS` default, and Microsoft describes it as “faster and more effective” for backup compression. If you already run a repeatable [backup script](https://adamtheautomator.com/sql-server-backup-database-script/), request ZSTD the same way, per backup: ```sql BACKUP DATABASE [YourDatabase] TO DISK = N'D:\Backups\YourDatabase.bak' WITH COMPRESSION (ALGORITHM = ZSTD); ``` Or flip the server-wide default so every future compressed backup uses it without changing individual scripts: ```sql EXECUTE sp_configure 'backup compression algorithm', 3; RECONFIGURE; ``` * * * _**Warning: Setting the server-wide _**`backup compression algorithm`**_ option to ZSTD is listed on Microsoft’s _**[_**SQL Server 2025 known issues**_](https://learn.microsoft.com/sql/sql-server/sql-server-2025-known-issues?view=sql-server-ver17)**_ page, which documents the error it returns and the supported workaround. Pin ZSTD per backup with _**`WITH COMPRESSION (ALGORITHM = ZSTD)`**_ rather than flipping the instance default, and read that page before you change any server-level default on a production instance.**_ * * * ## Decide Before the Maintenance Window, Not During It A native vector type, an optimizer that stops caching the wrong plan, and TempDB governance are all worth having, and none of them matter if the SQL Server 2025 upgrade takes down a replication topology nobody remembered still had a self-signed certificate on it. Run the migration assessment first. Provision certificates for every linked server and every publisher-to-remote-distributor connection while the report is still open, and confirm which of MDS, DQS, 32-bit SSIS, or Synapse Link your environment still depends on before Setup removes them. Stage the compatibility level change on its own, with a Query Store baseline captured before you touch it. Read the [SQL Server 2025 release notes](https://learn.microsoft.com/en-us/sql/sql-server/sql-server-2025-release-notes?view=sql-server-ver17) against your own environment before you pick a date for the maintenance window. Share this article [Share on X](https://twitter.com/intent/tweet?url=https%3A%2F%2Fadamtheautomator.com%2Fsql-server-upgrade-breaking-changes%2F&text=SQL%20Server%202025%20Upgrades%3A%20Avoid%20These%20Breaking%20Changes)[Share on Facebook](https://www.facebook.com/sharer/sharer.php?u=https%3A%2F%2Fadamtheautomator.com%2Fsql-server-upgrade-breaking-changes%2F)[Share on LinkedIn](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fadamtheautomator.com%2Fsql-server-upgrade-breaking-changes%2F) ## Related Posts ![](https://adamtheautomator.com/wp-content/uploads/2021/07/Understanding-SQL-Server-Data-Types-And-When-to-Use-Them.jpg) ### [The Amazing SQL Server Data Types (And When to Use Them)](/sql-server-data-types/) If you’re storing SQL data, you must understand SQL Server data types. In this article, you’ll learn about all data types and when to use them. ![](https://adamtheautomator.com/wp-content/uploads/2024/03/mssql-linux.jpg) ### [How to Install Microsoft SQL Server on Linux](/mssql-linux/) Unlock the potential of Microsoft SQL (MSSQL) on Linux systems in solving database management challenges effortlessly! ![](https://adamtheautomator.com/wp-content/uploads/2024/01/mysql-indexes.jpg) ### [How To Use MySQL Indexes Through Examples](/mysql-indexes/) Supercharge your database with MySQL indexes. Optimize queries, boost speed—master the art effortlessly, and elevate performance in this tutorial! ## Categories * [IT Ops](/category/it-ops/) * [Cloud](/category/cloud/) * [DevOps](/category/devops/) * [Home Ops](/category/home-ops/) * [Information Security](/category/infosec/) * [Software Development](/category/software-development/) ## Site * [Home](/) * [Tutorials](/tutorials/) * [Instructors](/author/) * [Advertising](/advertising/) * [Recommended Resources](/resources/) * [About Adam](/about-adam/) Copyright 2026© ATA Learning | [Privacy Policy](/privacy/)