April 3, 2024 at 7:32 am
Hi there,
thanks for listening!
We are preparing to downgrade one or our Instance from Enterprise to Standard. Thankfully, this is not a production server, so some downtime is not an issue. We are planning to make a snapshot of the VM so that we can rollback in case something goes wrong.
I want to "downgrade" using the method described by Garry Bargsley in this MSSQL Tips article:
https://www.mssqltips.com/sqlservertip/6686/edition-upgrade-and-downgrade-in-sql-server/
Basically, backup everything, stop the service, copy the database file for the system databases, make a reinstall, restore backups, replace system database files (just to summarize).
I have some worries about SSIS and SSRS, though. First about SSIS:
SSISDB uses a Database Master Key, that we do not have the password for. Additionally, an third-party application is using this password to open the key to execute the packages. So we cannot backup and restore the key with a new password. As the Master Key should be restored when we restore SSISDB, is it sufficient to backup the Service Master Key from the Enterprise Edition and restore it after the reinstallation? Or am I completely on the wrong track here?
About SSRS:
Do we need to reinstall SSRS as well, or can we "just" stop the SSRS-service and start the service again after reinstall of Standard Edition? If the restore of ReportServer and ReportServerTempDB goes without a hitch, it shouldn't be a problem, should it?
I'm very grateful for any advice or info you can give me about this.
Have a great day!
Sandigmann/Mark
April 3, 2024 at 5:10 pm
Offhand, I've not tried this but my 2 cents are below:
SSIS - if you don't have the database master key, then any and all encrypted data is possibly going to be lost. If you are OK with encrypted data being lost (certificates for example), then you don't need that. I think you are right though that if you restore the service master key BEFORE you restore the database, AND the database master key is allowed to be opened by the service master key, then you will be fine. If not, I'd be trying to find the password to the master key. IF the database master key is currently allowed to be opened by the service master key, you should be able to ADD a password to the database master key. Won't help get the current password, but at least you can update the application to use the new password as time permits AND have the old password work until you can disable that one.
SSRS - I am fairly confident you will need to reinstall SSRS as SSRS enterprise has more features than SSRS Standard and if you connect SSRS Enterprise to an SQL Standard database, it'll give you errors (if I remember correctly). The link you provided says this too, just not explicitly. It states "Click Select All to remove all SQL Server components from the server for this instance and click Next.". This would include uninstalling all SSIS and SSRS as well. The only thing that would remain would be shared components that you can't uninstall (multiple instances on one box can result in shared components that can't be removed).
My approach to all this sort of stuff is to do a second install of all things (SQL Server, SSIS, SSRS if possible, can't have 2 SSRS's on the same server with newer SSRS versions) so I can do side-by-side comparison. I can run and benchmark things 1:1 (same server, same configs, etc) and I can ensure that the change isn't going to hurt performance or if it does, that the impact is minimal. I believe SSRS 2017 only allows 1 instance per machine, but you could install the new instance, do the restores, and then re-point SSRS to the new server and see what happens. It MAY let you, but I suspect it'll complain that it needs or expects Enterprise Edition.
One thing to note - I'd personally NOT refresh the system DB's. The system DB's are going to contain some configs and such from Enterprise Edition and you don't want to have an EE ONLY feature enabled on Standard. My approach here is to script out anything in the system DB's and then test. I mean, I have read about people doing that and having success, but I'm hesitant to restore system databases to different versions/editions. Never know what MAY be in there that COULD cause a conflict. My approach of having the 2 instances up at the same time (different names and ports) and just changing connection strings and a quick backup/restore (or if that is going to take too long, backup JUST in case something goes sideways then detach from old and attach database files to new) at go live time means that I can validate that data looks good between the 2 for system DB's and I have a quick rollback plan (change connection strings back) if something goes sideways. If you have to restore the VM from snapshot, you are REALLY hoping the snapshot restore is quick and accurate. I've had snapshot restores fail before, so I'd be a bit hesitant on having "restore from snapshot" as my rollback plan.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 15, 2024 at 9:54 am
Hey Brian,
thank you very much for this very detailed explanation. It helped me tremendously! I played around with SSIS migration and with the following steps it works:
After that I can successfully start the packages. For SSRS I found a PowerShell-Script that collects every report, datasource and dataset: https://gist.github.com/jzabroski/346ab6bd2dd0988fe5b6e1f660023470
Just in case I have to reinstall SSRS. The downgrade of the first server will probably commence tomorrow.
I'll let you know how it went.
Thanks again and best regards!
Mark
April 26, 2024 at 4:11 pm
Definitely don’t restore SMKs across instances, they’re designed to be unique amongst instances and if you have existing objects protected by the original SMK then you lose those.
To downgrade the instance I would commission a new server with the correct edition of SQL server installed and restore the databases and other objects (logins, jobs, etc) across.
Bear in mind, if any database is using enterprise features you’ll lose these too
Finally, if the 3rd party know the DMK why can’t you get the details from them to add to your password safe after all it is your server, it’s not a situation I would be happy with
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply