September 11, 2018 at 7:47 am
We have a vendor application that needs to be upgraded from 2012 to 2016. The server isn't due for replacement for another 3 years so we normally uninstall and install SQL. The vendor says that will render the application unusable due to CLR assemblies in the databases (234 databases) and it would need to be an upgrade in place. Past history with in place upgrades has not been thrilling to say the least but none of us have done an in place in many years. What issues may/will come up with an in place upgrade and is there another way to go as far as the assemblies are concerned?
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
September 11, 2018 at 7:58 am
Hi,
Sadly I don't have a definitive answer for you. We did an upgrade from 2012 to 2016 (3 times actually, 1 for DEV, 1 for UAT and 1 in PROD) but we had the benefit of doing a side by side upgrades. We did this
1. Got infrastructure to spin up a new box (lets call it SQL2016)
2. I did the SQL 2016 install.
3. We then backed up the database from the old box (call it SQL2012).
4. Restore the databases on to the SQL2016 box
5. Tested.
6. Tested some more.
7. When we were happy we then shutdown the old box and gave it back to Infrastructure so they were happy, a box for a box.
For what its worth we didn't find any issues with CLR's but that was with the above. An in place is a bit riskier as you don't have an easy rollback, you could get someone to snapshot the machine before hand I suppose and when your happy through the snapshot away or if it all goes wrong then roll back to it.
Thanks,
Nic
September 11, 2018 at 2:18 pm
I was pretty hesitant to do in-place upgrades based on my experiences with older versions, but I've done about 8 in-place upgrades from 2012 to 2016 this year and, fortunately, none had issues. Obviously, the biggest concern is the lack of rollback option once everything is underway. Really, all you can do is attempt to prepare for it as best as possible.
Educational prep work
As far as the CLR issue goes... I believe that doing a standard restore or attach of a database to a new server would bring the assemblies and the wrappers with it. If they're dependent on asymmetric key security, you'd have to bring those over from Master or recreate them and you need to make sure the logins get the same server-level permissions. You might have issues with owner SIDs and you'd probably want to install the various .Net frameworks that exist on the current server... but I think it's doable.
September 11, 2018 at 2:36 pm
Thank you both for your replies. We are going to have conference call with the vendor support and try to find out why an attach or restore won't work. If the reason is valid, we'll stand up a VM of 2012 and set up the app then run the in place upgrade and hopefully not have any issues.
dependent on asymmetric key security
- I doubt it. I scripted them and they are all permission_set = safe.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
September 11, 2018 at 2:58 pm
MG-148046 - Tuesday, September 11, 2018 2:36 PMdependent on asymmetric key security
- I doubt it. I scripted them and they are all permission_set = safe.
If the database(s) are set with trustworthy on, then I think you should be fine as long as you restore/attach them with that property set on the new server and you have CLR enabled in the configuration.
If you have the option to stand up a new VM for testing, I would consider doing a clean install of 2016, backup/restore some of the databases over and manually run some of the CLR procs. If they work you can go back to the vendor with that information. If they don't you can come tell me that it didnt 😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply