How to easily downgrade SQL Server from Enterprise to Developer/Standard Edition

How to easily downgrade SQL Server from Enterprise to Developer/Standard Edition

How to easily downgrade SQL Server from Enterprise to Developer/Standard Edition

Problem

This article shows how to easily downgrade SQL Server from Enterprise to Developer/Standard Edition. So recently, I got a request from my manager. In a few of our database development environments, we have Enterprise edition of SQL Server installed, so the request from him was for me to downgrade these environment editions from Enterprise to Developer Edition. Below is a short overview that was taken directly from Microsoft’s website of the difference between SQL Server Enterprise Edition and Developer Edition.

Enterprise

The premium offering, SQL Server Enterprise edition delivers comprehensive high-end datacenter capabilities with blazing-fast performance, unlimited virtualization1, and end-to-end business intelligence – enabling high service levels for mission-critical workloads and end-user access to data insights.

Developer

SQL Server Developer edition lets developers build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition but is licensed for use as a development and test system, not as a production server. SQL Server Developer is an ideal choice for people who build and test applications.

Standard

SQL Server Standard edition delivers basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premises and cloud-enabling effective database management with minimal IT resources.

So let’s get into it. Before we start, it’s always good to take a backup of the environment being worked on. In my case, I had a snapshot taken of the environment by a sysadmin. to quickly backup your databases check out this article. If you can’t do a backup of the databases in the environment be sure to back up the database system databases (master, temdb, msdb ,modeldb).

Solution

  1. In Management Studio execute the query below on each database to know whether or not the database is utilizing any feature that belongs to enterprise edition only. In my case, I was downgrading to developer edition so the features in enterprise edition are the same. If this is not the case for you, you should remove these features.
    SELECT * FROM sys.dm_db_persisted_sku_features
  2. Get the version and build number of the database, so when we are downgrading later we have the correct version and build.
  3. Stop the database services by going to your database configuration manager.
  4. Browse to the location where your system databases (master, temdb, msdb ,modeldb) are kept and copy them to a new location ( we will get back to them later on). There is no need to copy the other database files as they won’t be affected during the downgrade process.
  5.  Uninstall SQL Server (a reboot of the server is required).
  6. Install the other edition of SQL Server (in our case developer edition) with the same instance name and path as the previous installation.
  7.  After the new installation, stop the SQL Server instance and copy the previously copied system databases to replace the new system databases.
  8. Start the SQL Server services and log into your database.
    Check the database edition by running the script below. Also because we copied the system databases to replace the newly installed one, we should see all the users and jobs from the previous installation.

    Select @@Version
    
    

Conclusion

Using SQL Server developer edition in a production environment isn’t permitted by Microsoft, likewise using standard edition or enterprise edition in a dev space would be costly. Before starting a downgrade, one should ensure that the environment being downgraded is backed up properly before proceeding. Check out our other blog posts, 5 free tips on how to keep SQL Server Database performing. and learn how to keep your database in good shape.