Hello SSC,
My company has SQL versions that range between 2008 R2 to SQL 2017. I have been tasked with upgrading all service packs on these boxes.
What tests should I run after the SP upgrade? Is it like a DB restore where you would have to re sync logins and check users on DB's? Or is it something more complex? I can run standard checks and have the departments sign off on connectivity, but is there a checklist or something? Should I treat this as a migration?
I am not sure, this is the first time I have upgraded SP's on a live environment and not on my home machine.
Thanks SSC, and as always, thank you in advance for your time.
Everyone has a plan until they get punched in the mouth. --Mike Tyson
My advice - test everything that you want to be certain works after the upgrade. I would test a backup, a restore, dbcc checkdb, update statistics, reorganize indexes, all features of all applications that touch the database, reports, views, stored procedures, functions, triggers... everything. Also check database compatibility levels. Not much point in upgrading and running it in compat mode.
Hypothetically, you do the upgrade and you have a stored procedure that has a deprecated feature or that had a behavior change from 2008 R2 to 2017 or has a performance hit from 2 seconds to 62 seconds. I want to know about that BEFORE production hits the machine.
I would do this upgrade on a TEST server and with 1 SQL instance at a time (one per outage window) so you can test on test, test on test again, test on live, and have a good backout plan in the event there is a performance or result set problem.
In theory, the upgrade will go perfectly and nobody will notice any difference. In practice, you never know what may happen. I'd much rather know as much about the impact prior to upgrading as is realistic rather than have some surprises post upgrade.
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.
January 21, 2020 at 7:20 pm
Thanks very much for the baseline!
I see your point, anything is possible during an SP upgrade, but this gives me a great starting point.
Everyone has a plan until they get punched in the mouth. --Mike Tyson
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply