Recently, one of my customers decided to downgrade the SQL Server edition. It was an Enterprise to Standard change, due to licensing costs, but it could be relevant in a few more scenarios.
The customer backs up by taking machine snapshots and has a long downtime possibility. So, in this particular case, he asked to make it an in-place downgrade and not side-by-side.
Well, the Microsoft documentation says "an in-place downgrade of SQL Server is not supported. To downgrade the version of SQL Server, you have to completely uninstall SQL Server, and reinstall it again by using the desired version.". Damm!
First of all, let's face it: this kind of change is migration. Actually, it doesn't really matter if it is an in-place or side-by-side. It's migration! Which includes all the risks involved and requires proper and careful preparation.
If you decide to do it, please behave responsibly! Take all kinds of backups before you start, and be aware of the full change rollback possibility. Create a step-by-step work plan. Test each step as much as possible in the test or lower environment before you start.
Here is how we did an in-place downgrade:
Take and verify a full machine backup (snapshots).
Move system databases from the default location to another, which will not be changed.
Check that the SQL Service account has Full Control over the folders and files.
Take and verify backups of your databases. Do it for both kinds: the system and the user databases as well.
Backup all the necessary server-level objects. Such as server triggers, roles, logins, linked servers, jobs, credentials, and certificates.
Back up all paths used by the SQL server. Mostly, default backups, logs, and all file locations.
Identify the current SQL Server build, by using SELECT @@VERSION and save it in a safe place.
Shut down this specific SQL Server instance, by using the T-SQL statement SHUTDOWN. Do not use "WITH NOWAIT" to perform an immediate shutdown without checkpointing the databases. Additionally, it can be done by stopping the service (SQL Server Service Manager, Windows Services, The Net Stop Command, WMI, and so on).
Copy system database files (both mdf and ldf), except tempdb files. Store these files in a safe place.
Uninstall the required SQL Server instance from the system.
Perform a system reboot.
Run the SQL Server installation and select the appropriate edition.
During the installation, set all system location paths to the same as you chose in steps 2 and 5!!
If necessary, apply all cumulative updates and service packs. That is to bring the instance up to your build number, which you checked in paragraph 6.
Shutdown SQL Server instance (same as paragraph 7).
Replace all system database files (both mdf and ldf) with a copy that is saved in paragraph 8.
Repeat paragraph 3. If during the installation you choose the same (old) account - check that nothing changed. Otherwise, change the file access-control list (ACL) so that the SQL Service account has Full Control over the files.
Start SQL Service.
Some IF's:
If you decided to make such a migration, please read before that about SQL Server Editions & Features
If each step was done correctly it will start up and be exactly where you were before you made any changes. That includes all of your user databases online and you should be ready to let applications connect and resume operations.
If the server is up and you can find all of your databases, but for some reason, any of the server-level objects are missing. You can restore it from the backups that you made in paragraph 5.
If there are any problems with the backups that you made in paragraph 5. You can restore the last good system database backups as a user database (with a different name). After that detach it, shut down the service again, and replace an exists the system databases with these new files. Read an attached link about the migration of system databases.
As I said at the beginning, each downgrade is actually a migration. So, there are a few additional options to migrate: install a new instance, generate scripts, BCP, SqlPackage, and so on.
It does not really matter which option you choose, remember, do it in all seriousness and with the utmost caution!
Links and additional reading:
コメント