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
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" 😉
May 2, 2024 at 9:09 am
Hi guys,
so, I made the downgrade on two servers about two weeks ago.
It went without any major problems. I followed these steps:
After that I made some adjustments to get SSIS to work (s. my last post).
Only thing that did not work was the restore of the cubes. Those are created by the application that uses the cubes and it seems that this application checks the edition of the instance and uses Enterprise Features if available. This was not an issue, we could just use the application to create the cube and all necessary other objects.
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.
I don't think this will be an issue. It's a clean reinstall so there should be not existing objects, that are protected by the old SMK.
Best regards,
Mark
May 2, 2024 at 10:01 am
Which system databases did you copy?
all I can do is point out that SMKs and DMKs should not be restored across instances and if done correctly there’s actually no need to
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 6, 2024 at 4:01 pm
Hey Perry,
I copied all except tempdb.
About the restore of the SMK: It was necessary to do this, because of the SSIS-Catalogue. This was initially created by an unreliable third-party through an employee that doesn't work there anymore. It was very short notice (Licensing costs!), so I had to find a quick way to do this somewhat reliably in an afternoon. If it hadn't worked, we would have rolled back using a server snapshot and learned a few lessons along the way.
Thankfully it seems to have worked, but you never know what lurks behind the next corner 😉
Anyways, thank you very much for your help and insights!
Best regards,
Mark
May 6, 2024 at 9:04 pm
did you patch the new instance to the same version as the source?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 10, 2024 at 1:14 pm
Hi Perry,
yes and no. I installed the same version, neither the new nor the old systems were patched.
Best regards,
Mark
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply