Does SQL Server Database Use Any Enterprise Edition Features?

One of the most common tasks performed by the DBA is to moving user databases across different editions of SQL Server especially during Disaster Recovery Situations. There are some Enterprise Edition only features which are not available on other editions of SQL Server. Hence it’s better to know which features are supported before one goes ahead and restore the database on new a server.

Using sys.dm_db_persisted_sku_features Dynamic Management View (DMV) you can list the following database-changing features as restricted to the SQL Server Enterprise or Developer editions in SQL Server 2008 and higher versions.

Change Data Capture: Indicates that CDC feature is enabled for a Database. One can remove CDC feature using sys.sp_cdc_disable_db stored procedure. This feature is available in SQL Server 2008 and Higher Versions.

Column Store Index: Indicates that at least one table within the database uses In Memory Optimized Column Store Index. Before restoring the database on to an Enterprise or a Developer Edition one must DROP or ALTER the index. This feature is available in SQL Server 2012 and higher versions.

Data Compression: Indicates that at least one table or an Index within the database Data Compression or the VARDECIMAL storage format. Before restoring the database on to an Enterprise or a Developer Edition one must remove data compression using ALTER TABLE or ALTER INDEX statements and to remove VARDECIMAL storage format one must use SP_TABLEOPTION statement. This feature is available in SQL Server 2008/R2 and Higher Versions.

In Memory OLTP: Indicates that the database uses In-Memory OLTP feature. i.e., the database has a MEMORY_OPTIMIZED_DATA filegroup. This feature is available in SQL Server 2014.

Partitioning: Indicates that the database contains partitioned tables, partitioned indexes, partition schemes, or partition functions. In order to restore or move database to an edition of SQL Server other than Enterprise or Developer, modify the table to be on a single partition, remove the partitioned table. If the table contains data, using SWITCH PARTITION convert each partition into a non-partitioned table. And finally delete the partitioned table, the partition scheme, and the partition function. This feature is available in SQL Server 2005 and higher versions.

Transparent Data Encryption: Indicates that the user database is encrypted using transparent data encryption feature of SQL Server. In order to remove transparent data encryption feature one must use the ALTER DATABASE statement. For more information, see Transparent Data Encryption.

T-SQL Query to Identify Features Used by SQL Server Database

Database Administrators can query sys.dm_db_persisted_sku_features Dynamic Management View (DMV) to identify SQL Server Edition specific features that are used by a user database.

SELECT feature_name FROM sys.dm_db_persisted_sku_features
GO

Demonstration:-

Let us try restoring a database on to a SQL Server 2008 Standard Edition which has a table which uses Page Level Compression feature of SQL Server 2008 Enterprise Edition. Since SQL Server 2008 Standard Edition doesn’t support Data Compression Feature the database restore is going to fail with the below mentioned error.

RESTORE DATABASE is terminating abnormally.
Msg 909, Level 21, State 1, Line 4
Database 'AdventureWorks' cannot be started in this edition of SQL Server because part or all of object 'Person' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Msg 933, Level 21, State 1, Line 4
Database 'AdventureWorks' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

Conclusion

As you could see that SQL Server did the complete restore and then finally it failed because the database uses Data Compression feature which is available in SQL Server 2008 Enterprise Edition. Hence by queries sys.dm_db_persisted_sku_features DMV on the server where the database exist one would come to know whether the backup of the database can be restored on another edition of SQL Server of the same version.

Must Read SQL Server Articles for DBAs and Developers…

• SQL Delete Duplicate Rows from a SQL Table in SQL Server
• COVID-19 Dashboard
• Does SQL Server Database Use Any Enterprise Edition Features?
• Discover SQL Server Components Installed Using SQL Server Discovery Tool
• Different States of SQL Server Database
• How to Get SQL Server Instance Information
• How to Use SQL Server Configuration Manager
• How to Configure a Contained Database Feature in SQL Server
• How to Manage SQL Server Failover Cluster using Command Line