SQL Server 2025 for DBAs: Features That Change Day-to-Day Operations

SQL Server 2025 (17.x) reached general availability on 18 November 2025, and the latest cumulative update as of November 2026 is CU10, build 17.0.5035.5. Launch coverage focused on vectors and AI. For the people who run the instances, the release changes more ordinary things: how long an UPDATE holds its locks, whether one report can fill tempdb, how backups are compressed, and where in an availability group you can take a full backup. It also brings breaking changes that show up on the first morning after an in-place upgrade: remote replication distributors that fail with SSL Provider: The certificate chain was issued by an authority that is not trusted, and full-text queries that fail with Msg 30010. This guide covers each operational change, how to switch it on, and how to roll out compatibility level 170 safely.

Scope and review date: November 2026. This post covers SQL Server 2025 on Windows and Linux for teams moving from SQL Server 2019 or 2022. Features, defaults, and syntax were checked against Microsoft Learn documentation on 9 November 2026. The commands were not run in a lab, so rehearse them on a staging instance first.

Which SQL Server 2025 build and edition should you plan for?

Start with the newest cumulative update. The SQL Server 2025 build versions page lists CU10 (17.0.5035.5) from 22 October 2026 as the latest. Under Microsoft's fixed lifecycle policy, mainstream support ends in January 2031 and extended support in January 2036.

SELECT SERVERPROPERTY('ProductVersion')     AS product_version,
       SERVERPROPERTY('ProductUpdateLevel') AS update_level,
       SERVERPROPERTY('Edition')            AS edition;

Interpretation: a product_version starting with 17.0 is SQL Server 2025. Compare the build with the build versions page before you schedule the upgrade window.

The edition lineup changed as well. From What's new in SQL Server 2025 and the editions page:

What behaves differently from SQL Server 2022?

Some changes take effect as soon as the new binaries start. Most of the operational ones are opt-in per database or per instance, so an upgraded server behaves much like SQL Server 2022 until you change settings.

Before you change anything, record the per-database settings that the new features depend on. Save this as check-2025.sql:

SELECT name,
       compatibility_level                 AS compat,
       is_accelerated_database_recovery_on AS adr,
       is_read_committed_snapshot_on       AS rcsi,
       is_optimized_locking_on             AS opt_lock,
       is_query_store_on                   AS qs
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

Interpretation: each column is a prerequisite for something later in this post. Optimized locking needs adr = 1, its lock-after-qualification part needs rcsi = 1, and the compatibility level rollout needs qs = 1.

How does optimized locking change blocking?

Optimized locking has two parts. With transaction ID (TID) locking, row and page locks are released as soon as each row is modified, and the only lock held to commit is one X lock on the transaction (XACT) resource. With lock after qualification (LAQ), an UPDATE or DELETE checks its predicate against the latest committed row version without taking a U lock, and locks only the rows that qualify. LAQ is in effect only when RCSI is on.

Optimized locking is in Enterprise and Standard editions and is off by default in SQL Server 2025. Each ALTER DATABASE below needs to be the only connection to the database, so run them in a maintenance window.

-- 1. ADR is required before optimized locking can be turned on
ALTER DATABASE [Sales] SET ACCELERATED_DATABASE_RECOVERY = ON WITH ROLLBACK IMMEDIATE;

-- 2. RCSI is needed for lock after qualification (test the application first)
ALTER DATABASE [Sales] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- 3. Optimized locking
ALTER DATABASE [Sales] SET OPTIMIZED_LOCKING = ON WITH ROLLBACK IMMEDIATE;

SELECT DATABASEPROPERTYEX('Sales', 'IsOptimizedLockingOn') AS is_optimized_locking_on;

Interpretation: 1 means enabled, 0 disabled, and NULL means the feature is not available on this instance. WITH ROLLBACK IMMEDIATE rolls back other sessions' open transactions; without a termination clause, the statement waits for them indefinitely. To disable ADR later, turn optimized locking off first.

LAQ is skipped with locking hints such as UPDLOCK or HOLDLOCK, on tables with a columnstore index, and in MERGE statements. Blocking shows up under new wait types:

SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('LCK_M_S_XACT', 'LCK_M_S_XACT_READ', 'LCK_M_S_XACT_MODIFY')
ORDER BY wait_time_ms DESC;

Interpretation: after you enable the feature, row-level blocking that used to show as key or row lock waits shows up as these XACT waits instead. Update alert rules and wait-stats dashboards, or blocking appears to disappear when it has only moved. The locking_stats extended event also reports, per database, why LAQ was not used.

How do you stop one query from filling tempdb?

With tempdb space resource governance, resource governor counts the tempdb data pages each workload group uses, including temporary tables, table variables, spills, and worktables. A request that would push its group over the limit is aborted with error 1138:

Could not allocate a new page for database 'tempdb' because that
would exceed the limit set for workload group 'workload-group-name'.

Measure before you limit. The usage columns are populated even when no limit is set:

SELECT name,
       tempdb_data_space_kb / 1024      AS current_mb,
       peak_tempdb_data_space_kb / 1024 AS peak_mb,
       total_tempdb_data_limit_violation_count
FROM sys.dm_resource_governor_workload_groups;

-- Then cap a reporting group (the classifier function must route sessions to it)
ALTER WORKLOAD GROUP [reporting] WITH (GROUP_MAX_TEMPDB_DATA_MB = 51200);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Interpretation: take peak_mb over a full business cycle, then set the cap above it. A rising total_tempdb_data_limit_violation_count means queries are being aborted. Microsoft advises against limiting the default group unless a classifier places workloads in their own groups, and against setting any limit to a small value or zero.

GROUP_MAX_TEMPDB_DATA_PERCENT works only when every tempdb data file either has a fixed MAXSIZE with growth on, or is pregrown with FILEGROWTH = 0. A default installation meets neither condition. In that case ALTER RESOURCE GOVERNOR RECONFIGURE succeeds, but logs warning 10989 and does not enforce the percent limit. The governor covers data files only: the version store and the tempdb log are not limited.

For the log, SQL Server 2025 can run accelerated database recovery in tempdb, which brings instant rollback and aggressive log truncation to temporary table work.

ALTER DATABASE tempdb SET ACCELERATED_DATABASE_RECOVERY = ON;
-- No exclusive lock is needed, but the change applies only after the Database Engine restarts

Interpretation: plan this for the restart that applies the CU. Microsoft notes that you may need extra tempdb data file space for the persistent version store (PVS) once ADR is on. Monitor PVS size there as you would in a user database.

What changed for backups?

Three changes matter: a new compression algorithm, immutable storage for backup to URL, and more backup types on availability group secondaries.

ZSTD compression

The backup compression documentation describes ZSTD as faster and more effective than MS_XPRESS, but publishes no figures. Test it per database. The LEVEL option accepts LOW (the default), MEDIUM, or HIGH.

BACKUP DATABASE [Sales]
TO DISK = N'R:\Backup\Sales_full_zstd.bak'
WITH COMPRESSION (ALGORITHM = ZSTD, LEVEL = MEDIUM), CHECKSUM, STATS = 10;

-- Compare ratio and duration with your current MS_XPRESS backups
SELECT TOP (10) database_name, backup_finish_date,
       DATEDIFF(SECOND, backup_start_date, backup_finish_date) AS seconds,
       CAST(backup_size / NULLIF(compressed_backup_size, 0) AS decimal(6,2)) AS ratio
FROM msdb.dbo.backupset
WHERE database_name = N'Sales' AND type = 'D'
ORDER BY backup_finish_date DESC;

Interpretation: a ratio of 3.00 means the backup is a third of the data size. Watch CPU during the test, because compression adds CPU load. Once you are satisfied, sp_configure 'backup compression algorithm', 3 makes ZSTD the default for every backup that specifies WITH COMPRESSION, including maintenance jobs that do not name an algorithm.

Immutable storage and AG secondaries

Backup to URL now writes to Azure containers with an immutability policy. SQL Server writes the whole backup in one pass to block blobs, instead of first creating a file filled with zeros. Only container-level immutability is supported, not version-level. WITH FORMAT against an existing blob name fails, so every backup needs a unique file name.

On availability groups, SQL Server 2025 allows full and differential backups on any secondary replica, in addition to copy-only and log backups. The secondary must be SYNCHRONIZED or SYNCHRONIZING and able to reach the primary.

-- Run in each backup job before it backs anything up
IF sys.fn_hadr_backup_is_preferred_replica(N'Sales') = 1
    -- Needs a SAS credential for the container
    BACKUP DATABASE [Sales]
    TO URL = N'https://account.blob.core.windows.net/sqlbackup/Sales_full_20261109.bak'
    WITH COMPRESSION (ALGORITHM = ZSTD), CHECKSUM;

Interpretation: the function honors the AG's AUTOMATED_BACKUP_PREFERENCE and BACKUP_PRIORITY settings, so the same job can run on every replica. Before full backups move to a secondary, check that your restore scripts can find a differential base taken on another replica.

What changed in availability groups?

The availability group changes are small, but each affects failover:

• Group commit time. The primary waits up to 10 ms to fill log blocks before sending them. The new advanced server option availability group commit time sets that wait in milliseconds; 0 keeps 10 ms.
• Fast failover. A Windows Server Failover Cluster (WSFC) RestartThreshold of 0 fails over at once on a persistent health issue instead of first restarting on the same node.
• Asynchronous page requests speed up undo-of-redo during failover. On by default; trace flag 12348 turns it off.
• Quorum-loss tolerance. Databases no longer stay in Not Synchronizing after a short WSFC quorum loss.
• Listener and routing. ALTER AVAILABILITY GROUP can remove one listener IP address, and routing URLs can be set back to NONE.

# Fail over immediately on a persistent health issue (default is 1)
(Get-ClusterResource -Name "ag1").RestartThreshold = 0
Get-ClusterResource -Name "ag1" | Format-List Name, RestartThreshold

Interpretation: per Microsoft's failover documentation, 0 shortens downtime for persistent failures but may fail over on transient ones that a restart would have fixed. Test it with a simulated health failure before production.

What does compatibility level 170 change, and how do you roll it out?

New databases on SQL Server 2025 default to compatibility level 170. Upgraded and restored databases keep their existing level, so nothing in the optimizer changes until you raise it. The compatibility level reference lists the 170 changes:

• Optional parameter plan optimization (OPPO). Queries with WHERE (col = @p OR @p IS NULL) can get separate plans for NULL and non-NULL values. It is on by default at level 170.
• Parameter sensitive plan (PSP) optimization extends to DML statements and tempdb.
• Key encryption for symmetric keys, the database master key, and the service master key uses OAEP-256 padding instead of PKCS#1 v1.5.

Two instance-level changes sit outside the compatibility level. What's new lists degree of parallelism (DOP) feedback as on by default; it needs level 160 or higher, Query Store in READ_WRITE mode, and Enterprise edition. Optimized sp_executesql serializes compilation of identical batches to reduce compile storms, but it stays off until you turn on OPTIMIZED_SP_EXECUTESQL.

SELECT name, value, is_value_default
FROM sys.database_scoped_configurations
WHERE name IN ('OPTIONAL_PARAMETER_OPTIMIZATION', 'DOP_FEEDBACK',
               'OPTIMIZED_SP_EXECUTESQL', 'PREVIEW_FEATURES');

Interpretation: run this in each user database. If one query regresses under OPPO, apply the DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION hint through Query Store hints instead of turning the feature off database-wide.

What breaks on upgrade, and what was removed?

The breaking changes list is short, and most of it comes from one change: SQL Server 2025 uses Microsoft OLE DB Driver 19, which requires certificate validation (TrustServerCertificate=False) by default.

• Linked servers to SQL Server 2025 must set the Encrypt parameter. Existing definitions can fail after the upgrade unless the target presents a valid certificate.
• Replication with a remote distributor and no trusted certificate. Changes to publications and Replication Monitor fail, and sp_adddistributor returns the untrusted-chain error. Microsoft recommends installing a trusted certificate. Setting trust_distributor_certificate = yes also works but is less secure.
• Log shipping monitoring can break when the monitor is a SQL Server 2025 instance and other servers run older versions.
• Full-text search. The legacy word breakers and filters were removed. Version 1 indexes fail with Msg 30010 until they are rebuilt as version 2.

-- Before the upgrade: what will need attention
SELECT name, provider, data_source
FROM sys.servers WHERE is_linked = 1;

-- After the upgrade, in each database with full-text indexes
SELECT OBJECT_NAME(object_id) AS table_name, index_version
FROM sys.fulltext_indexes;

Interpretation: each linked server row needs a connection test against a certificate the target trusts. Any full-text index with index_version = 1 needs a rebuild before its queries work again.

Data Quality Services, Master Data Services, Synapse Link, and Purview access policies are discontinued, and an upgrade fails if Data Quality Services is installed. Reporting Services is consolidated into Power BI Report Server. Hot add CPU and lightweight pooling are deprecated.

What do the vector and AI features mean for operations?

The vector data type and the vector functions such as VECTOR_DISTANCE are generally available. Vector indexes (CREATE VECTOR INDEX), VECTOR_SEARCH, half-precision vectors, and change event streaming need the PREVIEW_FEATURES database scoped configuration. The release notes state that preview features are not recommended for production.

Operationally: audit who sets PREVIEW_FEATURES with the scoped-configuration query above. CREATE EXTERNAL MODEL, AI_GENERATE_EMBEDDINGS, and sp_invoke_external_rest_endpoint make the Database Engine call external REST endpoints, which means outbound firewall rules, stored credentials, and a dependency on the model service.

Frequently asked questions

Key takeaways

Plan your SQL Server 2025 upgrade with JusDB

JusDB's SQL Server consulting team can review your instances against the SQL Server 2025 breaking changes, plan which databases should adopt optimized locking, tempdb governance, and ZSTD backups, and write a compatibility level 170 rollout with a Query Store baseline and a rollback step.

Bring your current versions and editions, availability group and replication topology, linked server list, and maintenance windows. Your team approves every production change.

Related reading

• SQL Server Query Store: identifying and fixing plan regressions
• SQL Server Always On availability groups: setup and tuning
• Ola Hallengren's SQL Server maintenance solution: production setup guide
• Microsoft Learn: What's new in SQL Server 2025