Database features restricted to a specific edition of SQL Server 2008

Identify database features restricted to a specific edition of SQL Server 2008

By: Ashish Kumar Mehta | Updated: 2010-09-07 | Comments (5) | Related: > Upgrades and Migrations

Problem

One of the Junior SQL Server database administrators (DBA) in my company approached me yesterday with a dilemma. He wanted to know how he can identify whether a database uses any of the features that are restricted to a specific edition of SQL Server 2008. In this tip we will go through the steps a DBA needs to follow to identify a database which uses edition specific features.

Solution

When you implement any of the features such as Data Compression , Partitioning , Transparent Data Encryption or Change Data Capture of SQL Server 2008 Enterprise Edition on a database, these features will internally change the way the database engine stores information within the database files. If a database uses any of the above features which are specifically designed to be used in Enterprise Edition of SQL Server 2008, it cannot be moved to other editions of SQL Server 2008.

DBAs can identify all edition specific features that are enabled within a user database by using the sys.dm_db_persisted_sku_features dynamic management view.

As an example, let's try restoring the AdventureWorks database to a SQL Server 2008 Standard Edition instance. Unfortunately, there is a catch; one of the tables Person.Address of is using Page Level compression in SQL Server 2008 Enterprise Edition. When you try to restore the database to a SQL Server 2008 Standard Edition instance which does not support the Data Compression feature, the restoration of the database will fail with the following error message:

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 'Address' 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.

identify a database which uses edition specific features.

If a DBA is asked to move a database from Enterprise Edition to any other edition of SQL Server 2008 it is a good idea to execute the T-SQL code below to identify whether the current database uses any of the features that are restricted to a specific edition of SQL Server. The sys.dm_db_persisted_sku_features dynamic management view will let you know which edition specific feature is being used by the database. One needs to have VIEW DATABASE STATE permission on the database to view the results.

USE AdventureWorks
GO
SELECT * 
FROM sys.dm_db_persisted_sku_features 
GO

execute the T-SQL code below to identify whether the current database uses any of the features that are restricted to a specific edition of SQL Server

As you can see in the code above snippet, the dynamic management view lets you know that the AdventureWorks database uses Compression feature of SQL Server 2008 Enterprise Edition. The sys.dm_db_persisted_sku_features dynamic management view will not return any rows if no features that are restricted by edition are used by the database. In case, you are using Data Compression , Partitioning , Transparent Data Encryption or Change Data Capture of SQL Server 2008 Enterprise Edition, you need to remove the features before you perform the full backup and restore the database.

Next Steps

As you begin to migrate databases between your environments, keep in mind that each of editions supports a different set of features and plan accordingly.
Review more tips on Compression, Partitioning, Encryption and Change Data Capture.
Read my previous Tips.

About the author

Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2010-09-07

Comments For This Article

Tuesday, July 5, 2022 - 11:09:47 AM - Erik Mus

You can list all databases which have EE features using:
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
SELECT * FROM sys.dm_db_persisted_sku_features;'

In case of 'data compression' you can list all tables from all databases using:
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
select distinct t.name AS CompressedTables
from sys.partitions p
inner join sys.tables t
on p.object_id = t.object_id
where p.data_compression > 0
;'

Whether this suffices (maybe other EE features will have to be adressed as well such as partitioning), the restore will tell!

Furthermore, features are bound to versions.
Data compression was EE only until SQL Server 2016 SP1 if I remember correctly.

Tuesday, April 5, 2016 - 3:36:53 PM - Joe Driscoll

Is using the following,

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; select ''?'' as ''database_name'', feature_name,feature_id from sys.dm_db_persisted_sku_features'

the only way to check if SQL Server has any of these features enabled?

Isn't there a query at the server level possible without touching every database?

thanks,

Joe

Friday, March 27, 2015 - 12:36:59 PM - Dragos Badea

Hello,

In order to identify more faster if there are any Enterprise features enabled on all the databases existing on an instance you can use the below query.

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; select ''?'' as ''database_name'', feature_name,feature_id from sys.dm_db_persisted_sku_features'

This will gave also the name of the involved databases. The query it's compatible as well on MSSQL 2008/2008R2 and 2012 ;

Best regards.

Monday, September 20, 2010 - 11:31:31 AM - Noeldr	Back To Top (10184)
Correction: With change data capture enabled the db CAN be restored on Std Edition even though the feature is lost.

Tuesday, September 7, 2010 - 4:04:38 PM - natarshia	Back To Top (10133)
do have any tips for finding tsql that has been deprecated when you are moving your databases up to different versions? or new tsql for code that may need to be implemented in older versions... ?