Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (4)   |   Related Tips: More > Upgrades and Migrations

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, April 05, 2016 - 3:36:53 PM - Joe Driscoll Back To Top

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 Back To Top

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
Correction: With change data capture enabled the db CAN be restored on Std Edition even though the feature is lost.

 


Tuesday, September 07, 2010 - 4:04:38 PM - natarshia Back To Top
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... ?


Learn more about SQL Server tools