My Very First Guest Post

I met someone at the PASS Summit this year who really pushed past her fears to meet a lot of people and make an impact.  Andrea Allred.  As a tribute to her bravery, I have submitted a guest post over on her blog specifically about dealing with your fears and not letting them stand in your way.  Swing over to her blog (www.royalsql.com) and check out her awesome posts.  

My guest post can be found here: http://www.royalsql.com/2012/12/10/fear-is-a-part-of-life-by-matt-slocum/

SQLFamily is awesome!

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.