Which Features You Lose When You Drop an Edition

SQL Server edition features affect more than whether a database restores successfully. A move to a lower edition can also change maintenance options, availability design, and the resources your workload can use.

Compare the Exact Releases

An old feature chart can turn a simple review into the wrong argument. Compression and partitioning became more widely available starting with SQL Server 2016 SP1. TDE reached Standard in SQL Server 2019. Don’t carry an Enterprise-only label forward without checking the target release.

SELECT
    SERVERPROPERTY('Edition') AS source_edition,
    SERVERPROPERTY('ProductVersion') AS source_version,
    DB_NAME() AS database_name;

Record the target edition and build beside that output. Then use the edition comparison for the target, not the newest page a search engine happens to show. A change in major version can add another restriction even when the edition name sounds familiar.

Also separate licensing permission from technical compatibility. A database that opens successfully hasn’t proved that the intended deployment is properly licensed. Keep those reviews connected, but don’t expect a database query to answer both.

Start With Persisted Features

SELECT feature_name, feature_id
FROM sys.dm_db_persisted_sku_features;

Run this in each database under review with suitable permissions. It reports persisted features that can matter when moving between editions. Use every returned row as an investigation lead. Read the corresponding documentation for the version you are moving to.

An empty result isn’t a full compatibility certificate. The view doesn’t inventory every server capability, Agent job, or operational choice. It also doesn’t measure how the workload behaves with less memory. It answers a narrower question about persisted database features.

I would save its output with the migration record and repeat the check after any feature changes. That gives you evidence of what was examined. It doesn’t replace a restore rehearsal on the actual destination edition.

Inspect Compression and Partitioning Directly

SELECT
    OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
    OBJECT_NAME(p.object_id) AS object_name,
    p.index_id, p.partition_number,
    p.data_compression_desc
FROM sys.partitions AS p
JOIN sys.tables AS t ON t.object_id = p.object_id
WHERE p.data_compression_desc <> 'NONE'
   OR p.partition_number > 1;

This exposes compressed storage and objects with additional partitions. A partitioned object with only one partition requires further inspection of its partition scheme. The query is a starting inventory, not an exhaustive detector. Check the table and index definitions when partitioning matters to the move.

Support for the stored structure doesn’t guarantee identical maintenance choices. Review the statements your jobs issue, including ONLINE and resumable options. An offline alternative can introduce blocking that the previous maintenance window never experienced.

Check Encryption and Its Keys

SELECT
    DB_NAME(database_id) AS database_name,
    encryption_state, key_algorithm, key_length
FROM sys.dm_database_encryption_keys;

Check whether the destination supports TDE for that release and edition. Also confirm that you can restore the protecting certificate and private key when required. Edition support won’t help when the backup arrives without the material needed to decrypt it.

Don’t disable encryption casually to simplify a migration. That changes the protection of data files and backups and requires a deliberate plan. If encryption must change, involve the person responsible for the data. Keep the original keys and tested recovery instructions safe.

Review Availability Outside the Database

Availability Groups aren’t a single checkbox with identical behavior across editions. Standard supports Basic Availability Groups with restrictions that differ from Enterprise availability groups. Check replica count, database grouping, and secondary capabilities against the exact target design.

Inventory listeners, endpoints, replicas, and operational dependencies separately. A backup doesn’t package the entire availability configuration. Applications can depend on a listener name even when a restored database works through a direct server connection.

Online index operations also depend on edition and operation details. Don’t assume that every index or maintenance statement supports the same option. Run the planned maintenance on a disposable copy and watch both the result and its effect on concurrent requests.

Test the Smaller Operational Envelope

Use a target-edition test instance with representative configuration and data distribution. Restore there, run the application’s important paths, and rehearse maintenance. Include backups and recovery. A clean restore proves that the database opened, not that the operating plan survived.

Compare resource ceilings as well as feature names. Lower memory or compute capacity can change performance without raising a compatibility error. Measure on the target instead of inventing a slowdown percentage. Keep the observations beside the workload used to produce them.

There isn’t a general Setup button that downgrades an installed edition in place. Plan a supported migration to another instance and a cutover you can reverse. The cheapest edition is the one that meets the requirements, including the requirements your nightly jobs forgot to mention.

An edition comparison is not a restore test, it is the start of an operational review.

This post was rewritten from scratch in September 2026. The original, published on 2009-12-27, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.