SQL Server 2025 Configuration Options: New, Changed and Obsolete

The Diff at a Glance

Two things stand out. First, Microsoft removed nothing at server level. Options for features that were discontinued years ago are still there and still accept values. Second, the one removal at database level, DW_COMPATIBILITY_LEVEL, is a hard syntax error on 2025. More on both below.

The 10 New Server Options

All defaults below are the values a fresh 2025 CU7 instance reports. "Static" means the change only becomes active after a service restart (is_dynamic = 0), so value and value_in_use differ until then.

Which of These Matter on Day One

• The three external/AI switches are the security-relevant ones. All three are off by default, which is correct. Treat them like xp_cmdshell: switching them on lets code inside the database reach outside the server. Add them to your hardening baseline and audit policy now, before a developer asks for them.
• availability group commit time (ms) is the only new option that is a genuine tuning knob for OLTP on synchronous AGs. Change it only with commit latency measurements before and after; on a busy system the 10 ms batching is what keeps the network and the redo thread efficient.
• Everything else (boxcars, ADR cleaner, sLog, lock manager cache, tiered memory) is an internal engine limit. Leave the defaults unless Microsoft Support asks you to change them. Three of these are static, so a careless change sits as a pending value until the next restart, which is the worst moment to discover it.

One Changed Option: backup compression algorithm

The maximum of backup compression algorithm went from 2 to 3. Value 3 is ZSTD, the new compression algorithm in SQL Server 2025. Both the server default and the explicit syntax work on 2025:

EXEC sp_configure 'backup compression algorithm', 3;  -- 3 = ZSTD
RECONFIGURE;

BACKUP DATABASE MyDb TO DISK = N'...\MyDb.bak'
WITH INIT, COMPRESSION (ALGORITHM = ZSTD);
-- msdb.dbo.backupset.compression_algorithm = 'ZSTD'

The same BACKUP statement on 2022 fails with Incorrect syntax near 'ZSTD'. If you share one backup script between both versions, drive the algorithm through the server default instead of hard-coding it in the statement.

Database Scoped Configurations

Six New Options

OPPO and optimized sp_executesql are measured in detail in Optimized Locking, Optimized sp_executesql and OPPO, Tested.

One Changed Default: DOP_FEEDBACK

DOP_FEEDBACK is OFF by default on 2022 and ON by default on 2025. Degree-of-parallelism feedback lets the engine lower the DOP of a repeatedly executed query if parallelism does not pay off. It requires Query Store and compatibility level 160 or higher, so after an in-place upgrade it is active in every database that already runs at level 160. If you see plans running with a lower DOP than MAXDOP allows, this is the first thing to check.

One Removal That Breaks Scripts: DW_COMPATIBILITY_LEVEL

ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 0;
-- SQL Server 2022: succeeds
-- SQL Server 2025: Msg 102, Incorrect syntax near 'DW_COMPATIBILITY_LEVEL'.

The option was meant for Azure Synapse and never did anything on a boxed SQL Server, but deployment scripts that script out every scoped configuration may contain it. On 2025 that statement is a syntax error and the deployment stops. Search your pipelines and scripts for it before the first deployment against a 2025 target.

Also worth knowing: on 2025, tempdb and every newly created database run at compatibility level 170, so new databases get OPPO and the other level-170 behavior without anyone deciding it.

Zombie Options: Dead Features, Live Settings

This is the part that surprised me most. Nothing was removed from sys.configurations, including options for features Microsoft discontinued years ago. I set each of them on 2025 CU7 and looked at value_in_use afterwards:

Why this matters: configuration baselines and compliance scans compare values. An instance can carry remote data archive = 1 from a 2016 build script, migrate through three versions, and still show up as "configured" in every inventory. None of these is dangerous by itself, but they are noise in audits, and they hide the settings that do matter. Clean them up before the migration so the 2025 baseline starts clean.

The RECONFIGURE Trap: allow updates

allow updates has had no function since SQL Server 2005. It is still there, and it is the one zombie that actively causes damage:

EXEC sp_configure 'allow updates', 1;
RECONFIGURE;
-- Msg 5808: Ad hoc update to system catalogs is not supported.

-- later, someone runs a completely unrelated change:
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;
-- Msg 5808: Ad hoc update to system catalogs is not supported.

RECONFIGURE WITH OVERRIDE pushes everything through, including allow updates = 1, which still has no effect. The clean fix is to put the value back:

EXEC sp_configure 'allow updates', 0;
RECONFIGURE;

This is not new in 2025, but 2025 did not fix it either, and migration projects are exactly where old build scripts get replayed.

Deprecation Counters: Two New Entries

The Deprecated Features performance counter object grew from 255 to 257 instances. The two new ones:

• Purview DevOps and other access policies in SQL Server 2025: Purview access policies are discontinued in SQL Server 2025. Microsoft points to the fixed server roles such as ##MS_ServerPerformanceStateReader## and ##MS_ServerSecurityStateReader## as the replacement.
• Database compatibility level 160: every older compatibility level gets a counter once a newer one exists, so this simply tracks databases still at the 2022 level.

Check which deprecated features your workload actually uses, on the old server, before the upgrade:

SELECT RTRIM(instance_name) AS feature, cntr_value AS uses_since_startup
FROM   sys.dm_os_performance_counters
WHERE  object_name LIKE '%Deprecated Features%'
  AND  cntr_value > 0
ORDER BY cntr_value DESC;

Pre-Migration Check Script

Run this on the source instance. It returns pending values (changed but not active), zombie options that are not at their default, and the scoped configuration that will break on 2025:

-- 1. Pending: value differs from value_in_use (restart needed or failed RECONFIGURE)
SELECT name, value, value_in_use, is_dynamic
FROM   sys.configurations
WHERE  value <> value_in_use;

-- 2. Zombie options that are not at their default
SELECT name, value, value_in_use
FROM   sys.configurations
WHERE  (name = 'allow updates'        AND value <> 0)
   OR  (name = 'remote data archive'  AND value <> 0)
   OR  (name = 'precompute rank'      AND value <> 0)
   OR  (name = 'hadoop connectivity'  AND value <> 0)
   OR  (name = 'open objects'         AND value <> 0)
   OR  (name = 'lightweight pooling'  AND value <> 0)
   OR  (name = 'priority boost'       AND value <> 0)
   OR  (name = 'set working set size' AND value <> 0)
   OR  (name = 'c2 audit mode'        AND value <> 0)
   OR  (name = 'remote proc trans'    AND value <> 0)
   OR  (name = 'SMO and DMO XPs'      AND value <> 1);

-- 3. Scoped configuration that is a syntax error on 2025 (run per database)
SELECT DB_NAME() AS db, name, value
FROM   sys.database_scoped_configurations
WHERE  name = 'DW_COMPATIBILITY_LEVEL' AND is_value_default = 0;

A min server memory (MB) row in query 1 is normal: SQL Server reports a small internal value_in_use (16 on the test instance) while the configured value is 0.

For a full side-by-side between the old and the new instance, dbatools does it in a few lines. Compare-sqmServerConfiguration from sqmSQLTool returns only the differences, and Export-sqmServerConfiguration keeps a JSON snapshot of the old instance for the migration record.

$old = Get-DbaSpConfigure -SqlInstance SQL2022 | Select-Object Name, ConfiguredValue
$new = Get-DbaSpConfigure -SqlInstance SQL2025 | Select-Object Name, ConfiguredValue
Compare-Object $old $new -Property Name, ConfiguredValue |
    Sort-Object Name, SideIndicator

New options appear only with the => side indicator; values that differ between the two instances appear on both sides.

Checklist for the 2025 Baseline

1. Reset the zombies on the source before migration (query 2). Especially allow updates.
2. Remove DW_COMPATIBILITY_LEVEL from deployment scripts.
3. Add the three new external/AI switches to the hardening baseline with value 0 and to your audit or policy checks.
4. Decide on DOP_FEEDBACK deliberately: it is now on by default for databases at level 160 and above.
5. Leave the new engine limits alone (boxcars, ADR, sLog, lock manager cache, tiered memory) unless you have a measured reason.
6. Consider ZSTD as backup compression default, after comparing backup time, size and CPU against your current algorithm on real data.
7. Run query 1 after every change window: a pending value is either a restart you forgot or a RECONFIGURE that failed.

For the migration itself, see Migration to SQL Server 2025: Risks, Issues, and Safe Approach, and for the overall feature picture SQL Server 2025: What's New for Developers and DBAs.

The Bottom Line

SQL Server 2025 adds ten server options, and only four of them deserve attention on day one: the three switches that open the database to external endpoints and AI runtimes, and the AG commit time. The rest are engine limits for Microsoft Support. The more useful work is on the other side: nothing old was removed, so dead options for Stretch, Hadoop and full-text ranking keep living in your configuration, and allow updates can still quietly block every RECONFIGURE on the instance. A migration is the one time someone looks at all of these settings anyway. Use it to start the 2025 instance with a baseline that only contains settings that do something.