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?
2 Answers
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.
answered Dec 5, 2014 at 16:48
-
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.– KASQLDBADec 6, 2014 at 12:22
-
1Thanks 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– KASQLDBADec 8, 2014 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. Dec 8, 2014 at 14:46
-
You will need to address this issue and remove data compression before you can downgrade. Dec 8, 2014 at 14:47
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.
answered Dec 5, 2014 at 18:03
SELECT * FROM sys.dm_db_persisted_sku_features