Blog

“Enterprise Edition was installed for SQL Server, but it turns out that we only have a license for Standard Edition. Is that an easy change?”

I see this question a lot. The answer is well documented by Microsoft, but it seems to be really hard for folks to find! If you’d like to go straight to the source, everything I’m going to highlight here comes from the MSDN page Supported Version and Edition Upgrades.

Sometimes Edition Upgrades (SKUUPGRADES) are simple

If you want to make a supported edition change, it takes a little downtime but isn’t all that tricky. You run SQL Server Setup and just follow the steps in the Procedure section here.

Edition Upgrade SQL Server 2012

“How complicated can this be?” … Famous Last Words

Protip: The Edition Upgrade GUI lets you see and copy the current license key for that instance of SQL Server. (No, I’m not showing a screenshot with my key in it!)

You can also do this from the command line using the SKUUPGRADE parameter (and back in SQL Server 2005 and prior, that was your only option).

Changing the edition causes some downtime, but it’s a simple procedure. The fact that it’s relatively simple isn’t an excuse to skip testing: always run through this outside of production first so you know exactly what to expect. And always, always, always take your backups and make sure they’re on separate storage before you start. Document everything you need to know about your configuration just in case something goes wrong and you’ve got to reinstall.

It’s pretty simple. Except when it’s not supported.

What Goes Up Does Not Necessarily Come Down

The way I usually remember the rules is that you can typically change from a cheaper version to a more expensive version. But you can’t necessarily go from a more expensive version to a cheaper version.

So if you have SQL Server Enterprise Edition and you want to change to Standard Edition, a simple SKUUPGRADE isn’t going to work for you. (If you have the “Evaluation” Enterprise Edition, you’re probably OK though!) Check the chart for what you want to do to make sure.

Clusters are Special. (Not in a good way in this case.)

A lot of the confusion is around SQL Servers installed on failover clusters. You have to scroll waaaaay down on that page to see this:

The fine print on upgrading failover clusters

The fine print on upgrading failover clusters

Ouch! Changing the edition of a clustered SQL Server is not a simple thing.

While I’ve made you uncomfortable, check out KB 2547273, “You cannot add or remove features to a SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012 failover cluster”.

What if I Don’t Know What Edition I Need?

Typically the answer here is to use Evaluation Edition. But if you’re running a failover cluster, be careful– as you can see above, you can’t easily change from Enterprise Evaluation to Standard Edition.

Will CHANGING THE EDITION Reset My Service Packs?

I believe this used to be true on SQL Server 2005– if you changed editions, you’d have to reapply service packs and cumulative updates afterward.

I just ran a test on SQL Server 2012 and upgraded from Developer Edition to Enterprise Edition on a test instance, and I still had version 11.0.3431 (Service Pack 1, Cumulative Update 10) after the upgrade.

But like I said, test this out with your version, even if it’s using a quick virtual environment that you don’t keep after the change has been completed successfully. There’s other real perks to doing this as well, such as making sure that your license keys really work and are the edition you think they are!

What If My Change Isn’t Supported By the GUI / Upgrade Installer?

In this case, you need to uninstall and reinstall SQL Server. It’s going to take longer and cause more downtime. You’ll have to reconfigure everything and reinstall service packs. (It’s not actually that much extra work, because you were going to take those backups and document all the special configuration just in case, right?)

What if I Can’t Take Much Downtime?

If downtime is a real issue, don’t make this change in place. Set up a new SQL instance, test the heck out of it, get it into monitoring and plan a way to migrate to it with limited downtime using something like Database Mirroring. (If you’re considering this, read that link– it mentions that mixing editions between database mirroring partners isn’t supported by Microsoft. You can’t set it up through the GUI, you have to use TSQL. If that makes you paranoid, you could do the migration with log shipping.)

↑ Back to top
  1. Kendra,

    using an Enterprise Evaluation together with Sharepoint may result in activated EE features. After downgrading to Standard Edition the database will be in suspect state …

  2. I have successfully downgraded multiple SQL 2008 R2 and SQL 2012 Enterprise Edition servers by following the uninstall and reinstall process. Before I uninstalled though, I made a note of the SP and CUs installed. I also stopped the SQL services and saved the system DB files (MDFs and LDFs for Master and MSDB) to a separate location. Once I reinstalled the correct Edition (Developer or Standard Edition depending on the server’s role), I then updated it to match SP/CU level that previously existed. Stopping the service, swapping in the MDFs and LDFs from the previous installation, and then restarting the service restored all my databases, logins, jobs, etc… to the now correct edition instance.
    One caveat that I want to mention, I did this on VMs and I took a snapshot before I started so if things really went south, I could just recover to that point in time.

    • The idea of swapping in the MDFs and LDFs makes me feel queasy. Like, literally, I actually feel queasy just thinking about it.

      The reason it makes me uncomfortable is that I picture something really weird happening three months later, possibly totally unrelated, and being on a call with Microsoft support and having to explain what I did. If I was them, I’d make me do a totally fresh install and start over before I passed go, unless it was something REALLY obvious. (And even then, I might make me do it for fun. OK, not really. But I’d be tempted!)

      I know people also talk about taking copies of system databases for emergency purposes, but I just can’t get on board with it. It’s like playing Dr Frankenstein to me, I guess.

      • I went through the hell of doing that same technique of mdf/ldf swapping after an uninstall/reinstall on hundreds of servers, and I still remember that queasy feeling! It worked though, even if it took years off my life.

  3. Hi Kendra,

    What about the steps for FCI? i.e. Passive first or active? I would assume to do the passive first, and then do the active. Thoughts? If I have time today, I will test.

    Thanks!

    • With a failover cluster instance, usually you cannot change the edition at all. Are you doing something that isn’t in that list of unsupported changes?

  4. Hi Kendra,

    Have you tried upgrading the Version but lower in Edition ? we have a 2012 Enterprise Ed and we need to upgrade it to 2014 but its Standard Ed only..Will the simple backup/restore will work ?

  5. Hey Kendra,

    I have a task to move 12 separate servers from Enterprise to Standard in the next few months, and in my research I’ve come across a method that looks too good to be true. Essentially, the advice is to simply edit the registry directly rather than uninstalling/re-installing SQL Server on the correct edition:

    http://sqlserverstore.blogspot.co.uk/2013/12/how-to-downgrade-sql-server-2008-r2.html

    I want to believe that it’s possible, I really do. However, it goes against pretty much all the other advice that I’ve read.

    Is this a risk worth taking??

    Thanks!
    Christina

  6. Hi Kendra,

    I’m trying to implemented AlwaysON 2012 for our biggest database.

    Right now we use SQL 2005 Enterprise and to test it i installed SQL 2012 enterprise but it’s an evaluation version. To upgrade my db i perform a backup restore.

    The problem i encountered is that my restore is suspended when SQL Server perform the upgrade.

    Do you know if it’s supported to upgrade a db with backup/restore from sql 2005 enterprise to sql 2012 enterprise evaluation?

    Regards,
    Tarek

    • Hi Tarek,

      Just to make sure– you’re testing everything out and doing that restore outside of production, right? Totally different servers, etc?

      Restoring a SQL Server 2005 database to a SQL Server 2014 instance is supported, and it does specify that in Books Online here: https://msdn.microsoft.com/en-us/library/ms143393.aspx. It will change the compatibility level as part of the process. What do you mean when you say the restore is “suspended”? Are you seeing any errors, or is it just slow?

      Kendra

  7. Kendra,

    excuse my english i’m french :)
    the backup is from an SQL 2005 enterprise to sql 2012 enterprise EVALUATION.
    And when i perform the restore, it write datafile then it’s suspended
    On message we don’t have :
    Converting database ‘db’ from version 661 to the current version 706.

    it’s suspended during more than 2 days

    It’s why i think that the upgrade can’t perform with evaluation version

    • Ah. If it wasn’t supported, I wouldn’t expect it to be suspended, I would expect it to fail. How large is the database? Have you looked at wait stats?

  8. Yes the db is very large 800Gb and my virtual OS very slow so let me try with a small db and feelback to you.

    However i didn’t looked at wait stats but even when i try to cancel the restore it’s not responding. So i destroy all :)

    so right now i install a new instance and try with a small db

    Thanks a lot Kendra

  9. Kendra,

    I try with a small db and it worked fine so i conclude that is beacause of the size of db and my poor infrastructure.

    I launch again the restore, let me check if it will be suspended again and look at wait stats

    thanks a lot

    regards

  10. Hi Kendra,

    I have launched my restore since wednesday afternoon and it suspended again

    sp_who2
    ========
    CPU Time: 1573190; DiskIO: 14880798; LasteBatch: 03/25 18:04:52

    TOP 10 of waits:

    wait_type wait_time_ms
    =======================================================
    DIRTY_PAGE_POLL 167314770
    LOGMGR_QUEUE 167314668
    HADR_FILESTREAM_IOMGR_IOCOMPLETION 167311841
    SQLTRACE_INCREMENTAL_FLUSH_SLEEP 167310204
    REQUEST_FOR_DEADLOCK_SEARCH 167310071
    XE_TIMER_EVENT 167303497
    CHECKPOINT_QUEUE 167302456
    LAZYWRITER_SLEEP 167294712
    XE_DISPATCHER_WAIT 167247072
    SP_SERVER_DIAGNOSTICS_SLEEP 167100121

    I enabled instant file initialization.

    Regards,
    Tarek

  11. Kendra,

    Sorry i found the root cause is PAGEIOLATCH_SH wait, my disk is very poor.

    thank a lot, aprreciate your help.

    Regards,
    Tarek

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php