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.
very nice post
ReplyDeletedbakings
Would this work with Clustered Server configuration if I do them one at a time? Should I break the cluster first then re-create?
ReplyDeleteI'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.
DeleteIf you try it out one way or another, please let me know as I'd love to know which way works for you.
Thanks!
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! :-)
DeleteVery useful post, just used this process to downgrade a test server from Enterprise to Developer.
ReplyDeleteSlight 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.
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?
ReplyDeleteGood morning Henk,
DeleteThe 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!
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.
ReplyDeleteThanks,
Abdul
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.
ReplyDeleteVery well done sir!