Wednesday, 26 June 2013

SQL Server: Identifying Edition Specifc features Used in a Database

I had a situation which forced me to do a feasibility study to move SQL server 2008 enterprise edition environment to standard edition. It is hard to check is there any edition specific features are implemented in any of the databases. It will be more complex process if the environment contain many databases.

There are features like data compression,partitioning,Transparent Data Encryption(TDE) and CDC which are available only on specific edition. The databases, that use these edition specific features can not be moved  to an edition that does not support this feature.

Microsoft has provided a DMV, sys.dm_db_persisted_sku_features to list all the edition specific featured implemented in a database. This can be used as given below:

SELECT * FROM sys.dm_db_persisted_sku_features

For me, it given the below result.

If the query does not return any result , the specific database does not used any edition specific features and can be moved to any edition of SQL server.

If you liked this post, do like my page on FaceBook