Introduction

I have SQL Server 2008 that was installed as an Enterprise edition, now we want to upgrade this server to SQL 2012, starting from SQL 2012 licensing terms have changed and if we go for upgrade then we need to buy additional core licenses as our existing license is based on processors. Now the question from my Reporting Manager: Do we really need Enterprise Edition on this server, Can we downgrade it to SQL Standard 2012?  I have been assigned with this task and I needed to submit feasibility report. 



Performing the test

There’s an easy way to tell whether the database contains enterprise-only features. In SQL Server 2008 onwards a new DMV sys.dm_db_persisted_sku_features has been added that reports which enterprise only features are present in a database. 



The above DMV may return one or more of the below four features:

  • Data Compression,
  • Partitioning,
  • Transparent Data Encryption
  • Change data capture

If you want to list enterprise-only features used in all the databases, then you can use below script.



Does DMV list all Enterprise-only features?

Now, the question is: does this DMV list all the enterprise features being used in database, MSDN says Database Snapshot and online Index rebuild are also enterprise-only features, but above DMV does not report those features even though I have used both these features.

In the above query, SNAPSHOT_TEST is a Snapshot of database id 30.

Also, I have maintenance plan which rebuilds index online, this is also not reported by this DMV.



Let me now try both these features on my SQL Server 2008 Standard edition.


As you can see in the error message, Database snapshot is not supported in the Standard Edition.

Now, let me try to rebuild index online on the same server.



Again, as you can see in the error message, this feature is not supported in Standard Edition.

  Note
You require VIEW DATABASE STATE permission on the database to run the above DMV

sys.dm_db_persisted_sku_features

may list the following database-changing features as restricted to specific SQL Server editions:

  • ChangeCapture
  • compression
  • MultipleFSContainers
  • ColumnStoreIndex:SQL Server ( SQL Server 2012 through SQL Server 2017). 
  • InMemoryOLTP: SQL Server ( SQL Server 2014 through SQL Server 2017). 
  • Partitioning
  • TransparentDataEncryption

Conclusion


If you are planning to downgrade from Enterprise edition to lower editions, then you make sure none of the above mentioned features are in use, in SQL Server 2008 onwards  new DMV
sys.dm_db_persisted_sku_features has been added that will report you which enterprise only features are present in a database but as I demonstrated this DMV will not report enterprise features like Database Snapshot and Online Index rebuild.

References