“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.
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:
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.)
71 Comments. Leave new
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 …
Oh, wow, I didn’t know that about SharePoint! That’s a great tip.
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.
I follow the same process Jeff describes without any issues or queasiness 🙂
Sorry, meant to write “…Matt describes…” . To many connections on my desktop.
Would you still have the same concerns if sp_attach / sp_detach was used, rather then just stopping the services and copying the data files?
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?
Kendra, so I can’t upgrade a 2008r2 Standard that a former DBA installed to 2008r2 Enterprise (which we were licensed for) to use the remainder of the memory on the physical machine in a FCI (minus 10%)? Or is that one of the few scenarios that is supported, the documentation doesn’t say it is unsupported so I should be ok? I’m not adding any features per the KB you posted, just want to use the enterprise engine features like online rebuilds and compression.
Hi Bruce,
That scenario is supported. There’s a little extra info on executing it here in the “SQL Server in a clustered environment box”: https://msdn.microsoft.com/en-us/library/cc707783.aspx
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 ?
Hi Jerry,
According to the table here (http://msdn.microsoft.com/en-us/library/ms143393.aspx), in place upgrade from 2012 Enterprise to 2014 Standard Edition isn’t a supported scenario. That’s just for in-place upgrades.
If you install a new 2014 standard installation, you can restore databases to them that were backed up from enterprise edition, unless they contain enterprise-only features. You can check if you’re using any of those with sys.dm_db_persisted_sku_features. (http://msdn.microsoft.com/en-us/library/cc280724.aspx)
Hope this helps!
Kendra
Kendra,
I have been migrating a large collection of databases off a SQL Server 2008 R2 Enterprise Edition server to new servers as part of a SAN migration project. The previous twenty-some migrations were moved to an identical but clustered SQL Server 2008 R2 instance. But the current batch of databases is different because this particular vendor application doesn’t support clustering and therefore has to move into a standalone and cheaper SQL Server 2012 Standard instance. Unfortunately now I’ve run into a new problem.
After two databases failed to restore to the new instances, I learned that the error message means they are using Enterprise features not supported by Standard edition. The DMV query shows that the compression feature is enabled on the 2008 R2 server for almost every database there, and the new instance error log indicates that is the problem with these two databases. A specific table is named for each database.
How would I disable that compression on this particular set of databases and tables so they can be restored or copied and attached into the downgraded 2012 instance?
The Enterprise backups with this characteristic restore onto the new instance in Suspect mode, which suggests to my novice brain that they need to leave compression behind before they’re moved from the Enterprise instance. Or is there a way to do that in the new instance and redeem them from Suspect status? I haven’t found those answers yet in spite of multiple searches through this site, SQLServerCentral, MSSQLTips, and Google.
I tried tips from various articles and technet. The commands from this one (https://www.mssqltips.com/sqlservertip/1614/data-compression-using-the-sql-server-vardecimal-storage-format/) seemed promising, since the logs told which tables had data compression.
But even after running the command to disable vardecimal storage on the offending table and then at the database level, the DMV still reports that data compression is in use.
I tried the SSMS table storage wizard, changing compression settings (which were “Page”) and generated and ALTER statement using these instructions (https://msdn.microsoft.com/en-us/library/hh710073%28v=sql.110%29.aspx) and the DMV still reports compression in use on the database if not the table. Nothing seems to shut that flag off for the database though.
I’m at my wits end what to try next since I’ve only succeeded so far in trashing the test vendor database. I dare not experiment on the other production ones after this misadventure without more information.
Could you help me understand the roadblock and how to get past this problem?
Thank you so much for any suggestions!
Hi Becky,
You do need to remove the data compression on each index while it’s still on the Enterprise Edition instance– as you’ve learned, the database can’t be restored (and sadly it checks at the end of the restore).
To remove compression you have to rebuild each of the indexes that are compressed individually with data_compression=NONE.
Most people typically script this out with a command on an index-by-index basis so that the work can be done in smaller chunks. (Rebuilding all the indexes on a large table can sometimes fill up transaction logs and be painful.)
Books online has some sample syntax here (bottom of the page): https://msdn.microsoft.com/en-us/library/hh710073.aspx. You could do some scripting to identify all the compressed indexes in a database and generate commands to rebuild them with no compression.
Oh, also, I re-read your comment (sorry, long day), and if you’ve already tried the alter table rebuilds that sounds odd if it’s still reporting feature in use. However, I couldn’t tell you the last time I’ve tested it!
The first thing I would do in that case would be to query sys.partitions for the database and make sure that the data_compression column really does say NONE for each row in the table. If not, you can use that to track down the culprit.
Kendra,
Thank you so much for responding and for that information!
I Googled another hour or so last night after sending that question and discovered that the log error wasn’t listing all the tables with compression, maybe just the first one it ran into. I expect that’s why the database reported the feature in use when I’d only known to modify one table per database.
There are in fact 33 affected tables in the test and training databases, and 1221 in the production database with data compression enabled. So compression appears to be on all the production tables and only a few of the test environment ones. But there’s not much data in test or training, so even the largest tables are pretty insignificant in size.
I’m working on the larger scale T-SQL solution today to alter the index compression so I won’t be rightclicking tables all night in SSMS! 🙂 In the meantime, I have restored an old copy of the test vendor database back to the original SQL Server 2008 R2 instance to clean this up and test my solution.
I see sp_estimate_data_compression (https://msdn.microsoft.com/en-us/library/cc280574%28v=sql.110%29.aspx) can estimate potential space savings based on a given compression scheme. I tested this against a few table names, and the largest production tables look like they might be compressed about 25% now.
Does that stored procedure (especially run through a nice script like this one from SQL Fool–http://sqlfool.com/2011/06/estimate-compression-savings/) give a close enough guesstimate on the uncompressed sizes for me to update the new server’s storage capacity with those results? Or would I be just as far ahead to wait until the databases’ compression has been disabled on the old server and then gather file size statistics?
Thank you again for your help, I really appreciate you taking time to answer!
Becky
I wouldn’t use the estimates, I’d rebuild to move the compression and then look at the space. Even if the estimates were accurate, you’re going to need some empty space to do the rebuilds, so might lead to some miscalculations.
Thank you again for pointing out those resources, Kendra, everything seems to be working well now. Of twelve application databases, it turned out only 3 vendor and one utility database were using data compression.
I modified my test and utility databases and tested that they restore and attach properly to the 2008 R2 Standard instance now from fresh decompressed backups. I have disabled data compression on the last two databases and will test more with the application team before moving those to the new instance.
You’re absolutely right that altering the tables in smaller sets would help the system load. I didn’t have that luxury and saw the production database deadlocking the application server at times, but the process finished in under 10 minutes.
The production log file is huge at the moment (I’ll check it after the full tonight’s backup) but the data file only used an additional 21% of space.
Becky
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
That’s not supported by Microsoft. If you do that and then weird things start happening (stack dumps/bad performance), and you call Microsoft support….
Yeah, I wouldn’t do it.
Thanks Kendra!
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
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?
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
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
No problem! A few things I’d check:
* Storage /network latency from where the backup is and for the drives you’re restoring to
* If instant file initialization is enabled where you’re doing the restore (that can help a lot)
* If you might have a high amount of virtual log files in the source database that could make the restore slower (https://www.brentozar.com/blitz/high-virtual-log-file-vlf-count/)
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
Kendra,
Sorry i found the root cause is PAGEIOLATCH_SH wait, my disk is very poor.
thank a lot, aprreciate your help.
Regards,
Tarek
Hey, congrats on figuring it out with wait stats!
Hello,
I am using mssql 2014 enterprise cal licensing, may I change it to mssql 2014 enterprise core licensing
I have product key of it.. I dont want to lose dbes, jobs, procedures vs
Have a nice day
Check out the chart on the “supported upgrades” page– it mentions that. (Search for “core”): https://msdn.microsoft.com/en-us/library/ms143393(v=sql.120).aspx
For any upgrade, even if it’s supported and you believe it’s going to be simple, I definitely recommend testing it on another instance first. There’s no reason to do it for the first time on your production instance!
Very useful thanks madam
Is there a way to tell if the SQL Server you’re on is a downgraded version of SQL? i.e…the original install was, say, SQL 2014, and it was downgraded to SQL 2012?
There’s no “downgrade” path. It’s possible that both versions may have been installed and one could have been uninstalled, but you can’t take a database that’s on a higher version and attach it to a lower version, so downgrades aren’t very common.
Just curious, why are you worried about it?
Thank you for the prompt reply Kendra!!
Our prod server is SQL Server 2012 (SP1) – 11.0.3431.0. For licensing discussion (auditing I think), my manager stopped by my office and asked if I knew of a way to tell if the server had been downgraded from a newer version. It was built and already in prod before I got here and apparently no one here has any documentation on it.
I’m planning on an in-place upgrade of a 2012 Standard Edition to a 2012 Enterprise Edition. It *is* an FCI, but from what I can tell it should be supported and straightforward…
I also have another INSTANCE on that server, 2012 Standard, and I’m thinking I will simply uninstall that before the upgrade. I do not want it running on that system.
These are on a Host licensed VM environment.
One of the benefits of virtualization is that it’s not that big a deal to create new VMs for situations like this. Why take on an in-place upgrade that you can’t test well before hand when you could just set up new VMs that you could test extensively prior to migration?
Ah! I never replied.
We were out of IPs.
However, it went quite well. Put one in maintenance mode, upgraded and rebooted, etc. I would have been much more concerned had I been doing more than an edition upgrade. I just left the other instances alone, and turned them off.
Thanks for responding.
I did the downgrade to Standard from Enterprise using uninstall and reinstall method. “Select @@Version” indicated it’s “Standard”, however, when using scanning tool against the server, it’s still being picked up as “Enterprise”. Do you know anything about this? Is there a solution to this?
Thanks!
Daisy – unfortunately we can’t really tell what you mean by “scanning tool.”
The “scanning tool” is the Microsoft Assessment and Planning Toolkit.
I am in doubt if i install the standard or web version of sql server 2012, can you help with that?
My scenario is a server with some web applications installed that supports something close to 6 thousand users per day and the database is hosted in another server, an exclusive server.
This server has 4 cores and 32 gb ram with a large amount of disk space.
Danilo – for questions that are unrelated to the blog post, your best bet is to go to http://dba.stackexchange.com.
Hi Kendra,
In my environment, my assignment below:
upgrade database server from sql server 2008 R2 enterprise edition to sql server 2014 standard edition.
My manager wants to know my feedback on it. will here any situation can happen for the enterprise to standard edition. What are those? And please let me know all about the scenario to done that? I will highly appreciate for that. Thanks
Respectfully
Maruf
Maruf – we don’t recommend upgrading a server between versions:
https://www.brentozar.com/archive/2015/03/why-you-shouldnt-upgrade-sql-server/
What about upgrades from MSDN Enterprise Edition to Standard Edition core license for 2014?
I have a query I am installing a sql server 2014 Enterprise core Edition Evaluation version after License came License but in between I am Sql clustering in those server after going to Licence version install in Sql server is there any effect from Cluster & is that Possibel for Cluster Enviourment any effect ??
Arpan – can you simplify and reword your question? I’m not sure what you’re asking.
Hi Kendra,
How to perform upgrade of SQL Server 2008 R2 to 2016 STANDARD edition, can you please guide me on the prerequisites and steps needs to follow on this.
Regards,
Sakthi
Sakthi – for questions, head on over to https://dba.stackexchange.com.
How do I evaluate the Web Edition? I can only seem to find Express, or Evaluation, and Evaluation only seems to come in standard mode….
Web Edition is only available to cloud hosting providers.
Head over here, then ctrl+f for ‘web’.
Is that true for older versions as well? I was trying to find SQL Server 2012 Web Edition. Our primary application still runs on SQL 2008 SP2 Standard, so I’d even be happy with a 2008 Web Edition. I can’t believe they were cloud-only back in 2008. The purpose of this exercise is just to see if our application can run 100% without problems on the web edition, so we can lower our total cost of ownership for it.
You’d have to contact MS licensing about that. I can’t offer any guidance on those issues.
Hi Kendra,
A customer asked me to change an SQL2016 Standard edition to SQL2016 Developer. Is this supported?
Richard – go ahead and read the post carefully. The answer’s in there.
Hi Brent/Sandra, we’ve made a downgrade from Enterprise Evaluation Edition to Standard and we saw the over night jobs all running much longer these are ETL packages and SQL Stored Procedures to load data from application server to DW server(which was downgraded from Enterprise to Standard). All other haven’t changed including configurations, network etc. It seems the performance took hard hit but we don’t know where to start with? If you could help with some thoughts and advice that would be much appreciated.
Yang – for performance questions, you can either get free answers over at http://dba.stackexchange.com, or click Consulting at the top of the site. Unfortunately this isn’t something we can fix in a blog post comment, sorry!
can i change my sql server 2016 from web to standard
Sumit – for Q&A, head over to https://dba.stackexchange.com.
Can we go with SQL server 2016 Enterprise Edition from SQL server 2016 Standard Version for Failover Cluster envionment ? Please suggest on this as its very urgent
Rama – for urgent problems, feel free to hit the Consulting link at the top of the site.
Thanks!
Hello Kendra/Brent,
I currently have SQL 2014 SP2 Developer Edition installed on a 2 node SQL cluster. The server needed to have Enterprise Core-based Licensing installed. Any chance this is supported by MS? Are there any good options to upgrade this?
Thanks,
Norm – for questions, you’ll want to head to a Q&A site like https://dba.stackexchange.com.
Hi All,
First Congrats for the amazing working you are been doing here.
I not a DBA so far more database developer and etc, but I am studying for a a while databases so I need a opinion from Experts to know when a organization need to upgrade from Standard edition to the Enterprise.
It is the Size of the database, the number of instances and each applications databases installed, so I really need some help and Tips to convince the organization and DBA to upgrade the sql.
We are facing some issues as death locks and in our cluster we have more memory that the one the standard can use. So anyone can give me a help what will be the gain to upgrade and what are the signs.
Thank you
Daniel – sure, that’s exactly the kind of work I do in my consulting. Click Consulting at the top of the page to learn more.
Can we move from Existing SQL 2014 Enterprise Edition to SQL 2019 Standard edition?