SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

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.

Find SQL Server Enterprise Edition Features in Use?

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.

Database Backup Encryption in SQL Server 2014 a Step by Step Implementation Guide

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


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.


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…

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals

Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.