2

I have SQL Server 2016 Enterprise edition, I restored the DB's to SQL Server 2019 Enterprise edition. But I want to check if I can restore it in SQL Server 2019 Standard.

if I run the DMV sys.dm_db_persisted_sku_features in SQL Server 2019 Enterprise, the results that returns: Compression, Partitioning, ColumnStoreIndex

which means I can't downgrade to standard edition. but according to Microsoft documentation SQL Server 2019 Standard supports the these features. so why these results appears in sys.dm_db_persisted_sku_features?

1
  • 2
    Prior to SQL Server 2016 SP1 these features were indeed restricted to Enterprise and not available in Standard; the view likely was not changed for compatibility purposes, but at the same time this should not prevent you from restoring the database in 2019 Standard. May 22 at 9:13

1 Answer 1

3

There is a note in the documentation that clearly states:

Starting with SQL Server 2016 (13.x) Service Pack 1, these features, except TransparentDataEncryption are available across multiple SQL Server Editions, and not limited to Enterprise or Developer Editions only.

So the code you want is:

select *
from sys.dm_db_persisted_sku_features
where feature_name = 'TransparentDataEncryption'

I think the docs are actually wrong still, because Change Data Capture is not available in Express Edition or LocalDB. The DMV could certainly be made more useful by adding columns showing which edition supports these features, at least for the current version.

6
  • That documentation section is a bit weird. the DMV is documented under Azure SQL Database, but not under SQL Server. Also, the DMV returns an empty resultset as of SQL 2022 (including SQL Database) (TDE is available in SE as of SQL Server 2022). May 22 at 13:45
  • @TiborKaraszi Do you actually have it enabled on that database? I get Compression Partitioning ColumnStoreIndex on my DB in SQL Server 2016 May 22 at 14:22
  • D'oh! No, I didn't have any "enterprise features" in that database. I have never been a fan of how this DMV works, btw. Anyhow, I'm on 2022, and it still returns for me Compression and Columnstore Index. So not even does it miss that TDE is now available in SE, it also report stuff that has been available in lower editions for 7 years, now. Anyhow, I have passed on that this isn't documented under the "SQL Server" section, we'll see if it is picked up. May 23 at 6:30
  • So basically this DMV is useless? According to the documention these features supports SQL Server 2019 standard, and i won't have a problem to restore it there?
    – Kramerica
    May 23 at 10:05
  • No, it tells you which of those documented features are enabled in the database (without having to query each separately). Whether it actually makes a difference to moving editions is dependent on versions. Certainly it could be made more useful, but it's not useless. TDE and CDC is still not supported on Express or LocalDB for example May 23 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.