Wednesday, December 5, 2012

How to Downgrade SQL Server Editions


I recently ran into an issue where the production VM template (containing Enterprise Edition of SQL Server 2008 R2) was used to deploy new SQL Server VMs for Engineering development (and thus should be the Developer Edition of SQL Server 2008 R2).  This is not a supported path by Microsoft, thus a more creative approach needs to be taken in order to accomplish this task. 

In order to complete this task, uninstalling the higher edition of SQL and reinstalling the correct edition is required.  I was able to work out the following process that helped to ease the pain of the downgrade.  I have used this process to downgrade Enterprise Edition to both Developer Edition and Standard Edition, but it should also work for any other lower Edition of SQL Server (Web, Workgroup, etc…)

I started by verifying the integrity (DBCC CHECKDB) and creating fresh backups of all DBs (including system DBs).

I then created scripts to recreate all of the following (in case the process failed at any point):
     ·         Logins (including roles) – contained in Master database
o   I used sp_help_revlogin, which works great for recreating the logins with the existing password, but unfortunately it does not script out the role membership (I scripted that manually).  For those that are curious, the password is hashed in the script, so you can’t use sp_help_revlogin to look up/hack a login password.
      ·         Jobs – contained in MSDB database
o   I had job creation scripts for setting up the server in the first place, but you can select the Jobs folder in the Object Explorer pane in SSMS and then right-click each job.  Just select Script Job as… > CREATE To > New Query Editor Window
     ·         SSIS Packages  – contained in MSDB database
o   Restoring the MSDB database should recover your SSIS Packages, but you may want to export them through Integration Services, just to be certain to preserve your hard work.

Note: The build number must match exactly for this process to succeed.  I was able to apply SP2 for SQL 2008 R2 (SP2 for SQL 2008 R2 results in build 4000 - 10.50.4000) before starting the process to make things easier after the install (then I don't have to track down what Cumulative Updates and/or Security Updates are applied to the current instance of SQL).

Next, I stopped all SQL Server services and created a folder on the Desktop (called System DBs).  I copied in the MDF and LDF files for the Master, MSDB, and Model (as we had customized Model) databases for the Enterprise Edition installation of SQL Server. 

With all the ground work in place, I then uninstalled all MSSQLSERVER features to remove Enterprise Edition (I grabbed a screen shot of the Select Features screen while I was uninstalling so that I could be certain to install the correct features when reinstalling).  Uninstalling the Shared Features is not necessary, and will only make the process take longer. 

I then installed Standard Edition to the same folder as the previous (Enterprise) edition.  I also applied the correct service pack to SQL Server (to bring the build up to the same as the previous Edition).  Installing SQL Server to the same folder will overwrite all existing System DBs, but your User DBs will remain unscathed.

At this point in the process is where I tried to add some awesome sauce. 

Instead of attempting to restore master (which can be a royal pain), model, and msdb; I merely stopped all SQL services.  I then created a new folder on the Desktop (called New System DBs) and moved out the current MDF and LDF files for the Master, Model and MSDB databases.  Then I copied in the MDF and LDF files for the previous installation of the Master, Model and MSDB databases and started the SQL services again. 

This worked perfectly!  I was presented with all my databases, logins (with the appropriate roles), jobs, SSIS packages, etc...

Summary:
1. Verify integrity of all DBs (DBCC CHECKDB)
2. Make backups of all DBs (including system DBs)
3. Make scripts to be able to attach DB and recreate logins (including roles), jobs, SSIS packages, etc... (just in case the overall process breaks)
4. Make note of the current Service Pack for SQL Server and the path(s) that SQL Server is installed to.
5. Stop all SQL Services
6. Make copies of the MDF and LDF files for Master and MSDB (Model too if you've customized it like I have) to System DBs folder
7. Uninstall SQL Server Enterprise Edition (all Features under MSSQLSERVER need to be removed and be sure to make note of the Features you're uninstalling)
8. Install SQL Server Standard Edition (or the Edition you're trying to downgrade to - i.e. Web, Developer, etc...) making sure to install the same features for SQL Server
9. Install the same Service Pack as was previously installed
10. Stop all SQL Services
11. Move current MDF and LDF files for Master and MSDB (Model too if customized) out to New System DBs folder
12. Copy in MDF and LDF files for Master and MSDB (and Model if customized) from previous installation
13. Start SQL Server services

I hope this process helps you if you find yourself in the same situation.

9 comments:

  1. Would this work with Clustered Server configuration if I do them one at a time? Should I break the cluster first then re-create?

    ReplyDelete
    Replies
    1. I'll have to admit that I do not have enough expertise with clusters to be able to definitively answer your question. Obviously, Developer would be your downgrade Edition of choice, and since Developer and Enterprise have the same feature set, in theory it should work. However, since this is not exactly a supported process, I would recommend breaking the cluster first as that should be a safer process.
      If you try it out one way or another, please let me know as I'd love to know which way works for you.
      Thanks!

      Delete
    2. I successfully downgraded SQL Enterprise to Standard in a Failover Cluster! I used some of the info you provided here, so thank you. I put together a document for the procedure. Only 60 easy step by step tasks! :-)

      Delete
  2. Very useful post, just used this process to downgrade a test server from Enterprise to Developer.

    Slight change on my side was that the Developer edition was installed to a different location, so I had the additional pain of updating the DLL paths in syssubsystem. Other than that it was a faultless process.

    Thanks for sharing the details.

    ReplyDelete
  3. Indeed a nice overview. We're busy planning a number of downgrades. Once accustomed to the steps to take, how long will an average downgrade take? 4 hours? 8 hours?

    ReplyDelete
    Replies
    1. Good morning Henk,
      The preparation for the downgrade will take the most amount of time (and varies based on the size and number of databases).
      I planned on about an hour of downtime for the actual downgrade process (some of my fastest servers I was able to complete in a little less than 30 minutes).
      I would suggest first trying it out on a server where a large/flexible maintenance window is available to get used to the process. My first test was on a server that was a VM. I took a snapshot before attempting the process so that if I completely fouled it up, I could just roll back to the snapshot.
      Best of luck with your downgrade process!

      Delete
  4. Very use full article! I m SQL newbie, how do i ensure SSRS databases, configurations and settings are not lost when i uninstall SSRS along with the steps above.

    Thanks,

    Abdul

    ReplyDelete
  5. Worked near flawless on cluster downgrade from eval to standard. The only problem i had was with fake cluster verification failures that i had to bypass. A reboot would probably have cleared those but the procedure was painless.

    Very well done sir!

    ReplyDelete