How to downgrade SQL Server 2008 R2 from Enterprise Edition to Developer Edition?

We had few Non-Production servers which were running on Enterprise editions of SQL Server 2008/SQL Server 2008 R2. For obvious reasons we had to downgrade these editions to developer editions of respective versions. Unlike adding new features to existing instance or upgrading existing instance from 2008 to R2 or upgrading from Standard to Enterprise edition there is no direct way(atleast I have not found) to perform a downgrade. Yes that’s true there is no direct way of doing it.

Though I have learnt 2 ways of doing edition downgrade: one is the mostly followed and lengthy processes and second one is less-time and less-known method.

First Method: You can try this for your first time:

This is a complete removal of existing instance and re-installing the instance of required edition. Most of the blogs\articles\forums suggest\prefer this.

How to do it?

First of all, as a first responsibility of every DBA what you do? Yes, take backup of all databases(User DB’s and System DB’s).

1.    Make copy of current mdf\ldf files of all system databases to other location.

2.    Make note of current version of SQL server instance like Service Packs, Hot Fixes, CU# installed.

3.    Script out all the server level permissions of logins.

4.    Script out all database level permissions of users.

5.    Make note of all SQL users credentials like username and passwords.

6.    Make note of Network configuration details like TCP\IP, VIA so on and port number of the SQL instance. You can get it from SQL Configuration manager.

7.    Account details on which SQL Services are running.

8.    In addition you can make a note of mdf\ldf file locations of all databases(system and user). This is required if your databases are spread across different drives.

9.    Also make note of enterprise features that are being used. (Anyway you can’t use them anymore J)

Now go to control panel and un-install the instance let’s say I want to downgrade my ‘Tester’ instance from Enterprise to Developer. So un-install Tester instance completely from the machine using Add\Remove programs of Control panel. Once un-installation completes reboot the server, once server is back verify and make sure instance got removed successfully.

Now place the CD of Developer edition and start installing. Now give the same instance name and if you have DB’s spread across different drives you can specify those file locations while installation, use the details you took in Point 8. Also configure the account for the services during installation itself with the details you have in Point 7. Also add yourself as admin while installation. Then proceed clicking Next and also mainly make sure you are installing only the features which were being used\installed earlier. Once installation completes, install all the required Service packs, CU# and hot fixes required to bring the edition to same version number as earlier(we took in Point 2) .

After bringing the newly installed Tester instance to same version number as point 2, stop the SQL services and replace the mdf\ldf files of master and msdb databases of the new instance with the mdf\ldf files of old instance(we took copy in Point 1). Once after replacing the master and msdb mdf\ldf files, start the sql services.

Now verify the instance and you should be able to see all the jobs and logins back. Now you have to restore all the user databases to the new instance in fact you can attach them back if you have taken mdf\ldf file copies of user databases already. Configure all the logins\users and their permissions(Step 3 and 4). Then configure configuration manager same as previous instance(Step 6 and 7).

Now re-start the SQL services and verify if everything is working as expected. Also confirm now you have same instance running on Developer edition with same version number as earlier.

Note:

                                                                                                                                                                                          

·         You need to replace mdf\ldf files of ‘model’ DB as well if your model DB is customized.

·         After re-installation VERSION number should be same as previous.

Second Method:

Why I mentioned this method as less-known because I have not found any blog mentioning they tried this method. Only place I found is in the comments of this link.

The method says making the registry changes will downgrade the edition without need to re-installation. I have to downgrade anyways in non-production environment so thought of trying this and IT WORKED!

Even though it worked for me, every time I try this I make sure I have the backups of all DB’s and above mentioned backups from Step 1 to 9.

Once you have backups, go to registry.

1.    Type regedit.msc

2.    Got to SQL installation section in registry by default it will be here:
HKLM(HKEY_LOCAL_MACHINE) -> SOFTWARE -> Microsoft -> Microsoft SQL Server ->

3.    Here you have to select the instance you want to downgrade if you have multiple instances.

Expand required instance folder.

Eg: In my case it is ‘Tester’ instance so

MSSQL10_50.Tester -> Setup

4.    Click on ‘Setup’

5.    Here You need to change one by one all below entries.

AGTGroup

DigitalProductID

Edition

EditionType

ProductCode

ProductID

SQLGroup

FDHostGroup

Checksum

Take these values from any already installed Developer edition instance and replace here. Make sure from where you are taking these values have the same features installed and same version and all.

6.    Now click on ‘SQL_Engine_Core_Inst’ and change the entries here as well.

·         ProductCode

7.    Now re-start the SQL Services and verify everything is in place and working as normal.

That’s it you are done.

Note:

·         Second method requires registry changes so please take backup of registry before making any changes.

·         Most important do not try this in your sensitive servers unless you tried it once in your less critical environments and it worked.

·         Make sure you have backup of everything.

     DO NOT MAKE ANY CHANGES TO THE REGISTRY WITHOUT BEING SURE WHAT YOU ARE DOING!!

Thanks!!