question

raadee avatar image
raadee asked

SQL 2008 R2 - Identify used Enterprise Features.

Anybody got a script och easy method to identify Enterprise Features that are used in a instance? Background, customer want's to downgrade from one Enterprise Server to two standard servers.
sql-server-2008-r2enterprise-edition
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
I guess the number of features and their usage makes so many combinations that it would be very hard to cover it fully. Yes, there could be some bunch of scripts which could help you cover some For e.g. If the count of the CPUs used are more than then Enterprise feature used otherwise not used. If the Memory in use is more than 64 GB then Enterprise else not etc. etc. So IMHO, if you can extract all the information against the features available in this BOL link [Features Supported by the Editions of SQL Server 2008 R2][1] and start omitting/marking features against Standard/Enterprise editions, you will be able to cover it comprehensively. Last thing you want is to use some script and find out that an Enterprise feature is require after moving to Standard edition. Moreover, I think if you are going to have two servers, then testing your application/databases could be more easier as you can plan things better. I know it would be a laborious work but sometimes there is no better alternative. Just my two cents. [1]: http://msdn.microsoft.com/en-gb/library/cc645993(v=sql.105).aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I started listing all enterprise features and thinking about creating a script. But since this is really a special one time gig I threw out the question here to maybe save some time. This is a "developer owned" server that I inherited and I will rely on their documentation including some manual double checking. Thanks Usman for the input.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
You are absolutely going to have to dig, and dig hard, to identify every possible use of enterprise features. And no, I don't know of any method that's going to automate that for you. You can get a little help from [sys.dm_db_persisted_sku_features][1] which will show you storage mechanisms that will prevent the shift. But you've still got to go through all the code and structures within the database. [1]: http://msdn.microsoft.com/en-us/library/cc280724.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It's more of a case of helping out, the responsibility is not on me. Thank's for the sys.dm_db_persisted_sku_features tip.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.