2

There is a requirement in our project where they want to evaluate if it is possible to downgrade SQL Server from Enterprise to Standard edition and if a server can be decommissioned.

How can I identify which servers are suitable for downgrade?

  • What versions of MS SQL Server are you running is the first question? How heavily loaded are the servers? Are you dependent on any Enterprise functionality? For checking Enterprise edition of 2014 see: msdn.microsoft.com/en-us/library/cc645993.aspx You can from that page use 'Other Versions" to check back to SQL Server 2008. – RLF Dec 5 '14 at 16:42
  • 1
    If you are running 2008 R2 or higher, run this query first. This will tell you if your server is currently using any Enterprise only features: SELECT * FROM sys.dm_db_persisted_sku_features – Kris Gruttemeyer Dec 5 '14 at 16:43
  • Just for my own curiosity, is this a cost/licensing issue for your higher ups? – Kris Gruttemeyer Dec 5 '14 at 18:16
  • I ran the query on my SQL2K8R2 64 Bit server, which is not that heavily loaded, and got the blank output which i believe states i am not using any feature of that enterprise edition.as column feature name and feature id did not returned anything. – KASQLDBA Dec 6 '14 at 12:22
6

Kendra Little did a great article on this: http://www.brentozar.com/archive/2014/08/sql-server-edition-change-standard-edition-enterprise-evaluation/

Microsoft article on supported upgrade paths: http://msdn.microsoft.com/en-us/library/ms143393.aspx

First, note that not all downgrade scenarios are supported (You didn't provide the exact version you are running).

Second, run this query to check if there are any Enterprise features in use (those features will not work when you downgrade).

SELECT * FROM sys.dm_db_persisted_sku_features

Third, if your downgrade isn't supported (it doesn't seem like many are), then you are essentially going to have to do a fresh install with standard edition, then migrate to the new server.

  • I ran the query on my SQL2K8R2 64 Bit server, which is not that heavily loaded, and got the blank output which i believe states i am not using any feature of that enterprise edition.as column feature name and feature id did not returned anything. – KASQLDBA Dec 6 '14 at 12:22
  • 1
    Thanks Kris@ i ran on my listed servers' DB and for most of them feature_name :Compression and ID : 100 is what i am getting, so i believe, they are using compression feature for backing up. if its okay to require backups without compress i believe i am good to degrade in that case! Kindly correct if i am wrong – KASQLDBA Dec 8 '14 at 14:44
  • Per BOL (technet.microsoft.com/en-us/library/…) Compression. Indicates that at least one table or index uses data compression or the vardecimal storage format. To enable a database to be moved to an edition of SQL Server other than Enterprise or Developer, use the ALTER TABLE or ALTER INDEX statement to remove data compression. To remove vardecimal storage format, use the sp_tableoption statement. For more information, see Storing Decimal Data As Variable Length. – Kris Gruttemeyer Dec 8 '14 at 14:46
  • You will need to address this issue and remove data compression before you can downgrade. – Kris Gruttemeyer Dec 8 '14 at 14:47
1

In order to "downgrade" you'd have to uninstall enterprise and install standard. There is no in-place downgrade in the versions I've installed (2005+). As mentioned, you'd want to look at the sys.dm_db_persisted_sku_features for each database, it is not an instance-level dmv. You might want to look at this a write-up about two methods of downgrading. You'll also probably want to use that dmv in combination with Aaron Bertrand's sp_foreachdb because sp_msforeachdb can miss some databases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.