Tag Archives: enterprise only features

find Enterprise-only features in your database

29 Friday Nov 2013

Posted by in SQL Server

≈ Leave a comment

Tags

, , , , ,

I have SQL Server that was installed as enterprise edition, we have never utilized the enterprise-only features on this particular server and want to downgrade to Standard edition so that we can make use of this enterprise license in some other server.
There’s an easy way to tell whether the database contains enterprise-only features. In SQL Server 2008 onwards a new DMV sys.dm_db_persisted_sku_features has been added that will report you which enterprise only features are present in a database. Let’s check it out.

Method 1:
You need to run below query on every database to check for enterprise-only features.

SELECT * FROM sys.dm_db_persisted_sku_features

Method 2:
The following query will report all enterprise-only features from all databases.

Declare @EntFeatures Table(DbName NVARCHAR(255),[Enterprise feature] NVARCHAR(255))
INSERT INTO @EntFeatures
exec sp_msforeachdb 'select "?" AS DatabaseNames,feature_name from [?].sys.dm_db_persisted_sku_features'
SELECT * FROM @EntFeatures

Method 3:
The following query makes use of cursor to achieve the same result as in method 2.

Declare @dbid Int
Declare @STR Varchar(100)
Declare @EntFeatures Table(DbName NVARCHAR(255),[Enterprise feature] NVARCHAR(255))
Declare rs scroll cursor for
Select database_id From sys.databases
open rs
fetch first from rs into @dbid
while @@fetch_status= 0
Begin
Select @STR = 'use ' + db_name (@dbid) +
CHAR(13) + ' SELECT db_name(),feature_name FROM sys.dm_db_persisted_sku_features'
Insert Into @EntFeatures
EXEC ( @STR )
fetch next from rs into @dbid 
End 
Close rs 
Deallocate rs 
Select * from @EntFeatures

sys.dm_db_persisted_sku_features DMV only reports below four features.

  • Data compression
  • Partitioning
  • Transparent data encryption
  • Change data capture

As per MSDN Library even Database Snapshot & Online Indexing are enterprise-only features , if you have enabled these features in your database it will not be reported by this DMV.
To make sure I created a database snapshot and ran this DMV but it did not report database snapshot as enterprise-only feature same is with online indexing which I enabled for one of my maintenance plans but DMV again missed out this as well.