Downgrading the SQL Server Edition of a Dev Environment

I got a great question about Edition downgrades recently.

I have discovered a tranche of potential money saving by replacing SQL Server Enterprise Edition with SQL Server Developer Edition where appropriate. All of the checks have been made - i.e. the Servers are truly Development servers, with nothing Production going anywhere near (either data or people).

What’s the simplest way of changing a SQL Server in this way? It looks like an uninstall / re-install but there’s an awful lot of objects on that Server - Databases, Security, Linked Servers, SSIS, Triggers, so a lot of work. Some people on the Internet suggest copying the system databases before re-installing to the same patch level then copying the previous system databases back in.

Hoping to pull off the Tablecloth trick

Whee! It’s always so much fun when you can save a lot of licensing costs.

When an environment is being used for development, it can be licensed with free Developer Edition (even if you do user acceptance testing in it).

And Developer Edition has all the features of Enterprise Edition. Pretty great deal, eh?

Why not just use SKUUPGRADE?

There’s now a way to upgrade editions in the SQL Server installation interface, but it used to need to be done with the command line using a /SKUUPGRADE parameter, so a lot of people still use that term online.

If you’re using Evaluation Edition, you can often change to other editions quite easily.

But you can’t easily downgrade your editions, and there’s also limitations on changing editions when you’re using a cluster. Microsoft has a list of Supported Version and Edition Upgrades here.

Basically there’s no supported path to use the built-in tools to quickly change from Enterprise to Developer Edition.

I’m careful about doing things that are unsupported

“Unsupported” isn’t always terrible. It doesn’t mean “illegal”. It just means that Microsoft isn’t going to help you if something goes wrong because of what you did.

Technically, running the “DBCC PAGE” command is unsupported, and running it can even sometimes cause stack dumps. I still blog about it and run it sometimes, I’m just careful.

In this case, doing an unsupported downgrade might be just fine. I wouldn’t like managing the environment afterward because it’s such a significant unsupported action – if I choose to take a system-database copying shortcut, am I going to be haunted by that for a long while afterward when anything goes wrong in the environment?

I’ve been burned by things like that over the years, so I’m a bit careful.

If it was my environment, I would want to do fresh installs

Development environments have a way of becoming pretty messy. That’s usually OK, and it’s part of the nature of the beast, but when a Dev environment goes along for many years just being upgraded and patched and modified by many developers, eventually nobody knows what it’s set up like anymore.

And then  you start hitting issues where you deploy to production, and it doesn’t work there, but it worked fine in Dev. Because the dev environment doesn’t resemble production much anymore.

For this reason, I’m a big fan of being able to rebuild dev from scratch periodically:

• I want the dev environments to be installed as much like production as possible
• I want to have dev environments documented and configured as much as possible, so that if we had to re-install fresh after a disaster, we’d be OK – and everything about the environment configuration is “known”
• Dev environment OS’s tend to lag behind production, and doing a fresh install is a good chance to test “ahead” and get to a newer version in dev before rolling it out to production

But I wouldn’t want to do the uninstall!

It’s a huge pain to make a big modification in-place on an environment. Even dev environments, because things don’t always work in dev and sometimes it’s fine: so if you change the environment in place, afterward you have a hard time figuring out if your change broke it, or if it was just already broken.

It’s so much easier when you have the “before” around to compare to the “after”, and you’re doing a switcharoo rather than changing what’s in place.

This is one of the things that’s fantastic about virtualization - being able to set up a new environment and keep the old around on slow hardware for a while. When things don’t work, you go look at the old one to see what you missed.

So I would want to:

1. Spin up a new virtualized environment
2. Choose OS version and cluster configuration carefully if I use them before deploying
3. Automate SQL Server installs and use slipstreaming for SPs/Cumulative updates
4. Look at PowerShell tools to capture and deploy current environment configuration - things like: https://dbatools.io/functions/
5. Check in all my scripts and documentation to source code that’s backed up offsite for future use

Would you go the long route?

My way of approaching this is definitely not the simplest or the fastest. It’s just the one that results in an environment I’m happier living with.

What about you, Internet Reader? Have you used shortcuts for downgrading editions and had good experiences? Bad experiences?