Please advise - Patching questions
-
Hi All,
I am learning and honestly think this is a great platform and have some basic patching questions.
1. Can I patch from sp1 to sp3 or it has to be in order?
2. What can be the best strategy if application does not work after patching?
3. What are common do not's for patching
4. do DBA's also do windows patching? or only sql server?
5. If we are optimizing the server, will that setting be there after patching?
6. Does RESTART or server or only sqlserver required?My Plan.
1. Take full backup of user, system, report & resources db's
2. We do not have test so need to do on prod( scared 🙂
3. Inform 30 minutes downtime ( to other teams)
4. Check crucial stats ( please share if there is any script)Thank you all for your time.
Appreciate it. -
Quite dangerous without a test environment.
1. Can I patch from sp1 to sp3 or it has to be in order? -> usually you can patch straight to sp3 as the updates include all previous fixes.
2. What can be the best strategy if application does not work after patching? -> consider a rollback. Check your recovery/failure plan. Check the application manual / error logs sql,os
3. What are common do not's for patching -> not testing your rollback plan. Have a power failure ...
4. do DBA's also do windows patching? or only sql server? : can be both
5. If we are optimizing the server, will that setting be there after patching? : usually they are kept, however ssrs can reset its config
6. Does RESTART or server or only sqlserver required? depends on the patchMy Plan.
1. Take full backup of user, system, report & resources db's. -> Maybe one of the os also when DB is down.
2. We do not have test so need to do on prod( scared
-> :crying:
3. Inform 30 minutes downtime ( to other teams). -> How did you decide on 30 minutes without test?
4. Check crucial stats ( please share if there is any script) -> depends on the applications. You might want to check memory, cpu, disk use, transactionlog growth, failures... -
Thanks you for all the answers!
I might be wrong with 30 mins for downtime, and not sure what is the good amount of time for downtime? How do we calculate that?
The way system is setup is sql server is running on Host - HyperV with 7-8 user db's and it is 2012.
I am going to discuss with architect soon and let me know if I have to ask anything which might help....in addition to above what we discussed.Big thank you!
-
I'm with Jo on everything, with a few comments of my own here on your plan...
The two items that jump out at me the most are, you're only planning on 30 minutes of downtime and you're doing this in a production environment with no test environment first....
The first of those, the time estimate, I think is wildly optimistic. A service pack install, that I've done, can take anywhere from an hour to longer. Frankly, I'd tell the other teams to plan on the server not being available for at least 2 hours, maybe 3. Keep in mind also, that's presuming nothing goes wrong, which would just end up making it take even longer.
Second, not having anywhere to test first? Yeah, I'd find that terrifying. I'd either push to get a temporary test environment stood up that's at least close-ish to production, install the SP, then have the other teams ensure nothing broke as well as to sort out what after-update tasks you'll need to do to get everything going, or else get it in writing from your superiors that you'll be doing this in production and if it goes sideways production will be down until the problem can be resolved.And believe me, you can have SP installs go wrong for the weirdest things. At my workplace, if I don't get the sysadmin team to shut down and disable our anti-virus, it prevents some of the SP / CU upgrade scripts from running, leaving me with a very, very, very broken SQL install. Services won't start and I can't uninstall it (thankfully I'm on virtual machines and snapshot before any patching, plus I have a QA environment to do it in first, so I have an "easy" roll back, but if it were physical servers, I'd be reloading...)
-
Great Jason! thanks for sharing your thoughts and guidance.
one of the things I will be doing now is plan on 2-3 hrs of downtime notice..
If anything goes wrong then I think my best bet is the VM snapshot which was on my mind.
Have a great week! -
You're quite welcome, but I'd still push to get a test server stood up. The advantage you've got is, because you're on a VM you should be able to stand up a second VM to test on first. It doesn't even need the same CPU / RAM or even storage as the production VM, just enough to get things loaded for testing. Heck, you could even use a trial version of Windows and SQL Server for this, as you'd just be tearing it down after a couple days or so (I'm not a licensing expert, nor did I stay in a Holiday Inn Express last night. Contact your licensing specialist to make sure this suggestion won't put you / your employer up the solid waste creek without a paddle with Microsoft.)
If you can't do that, then yes, a VM snapshot will give you a way to back out fairly easily. Out of paranoia you may even want to go as far as stopping the SQL Server service before you snapshot, then restart it before you patch, to ensure the databases are quiet.
-
I absolutley agree with your advice about the test server. I will push for it and see it that is a possibility or create awareness of the advantage of having one or maybe build one. I have noted the above suggestions and please share if there is any article with step by step process..I think I am asking to much and respect your time and efforts. Thank you!
-
This cannot be SQL 2017, SP has not been released for 2017.
It appears to be SQL 2012. Here are a few issues you may encounter, if it is SQL 2012...
Do you have an SSISDB database? IF so, there was a glitch in the patch. Detach the SSISDB first, and then patch it. Re-attach it after the patching is successful.
I HIGHLY recommend taking a snapshot of this server, spinning it up and running the upgrade. Only then will you have a proper baseline to determine how much downtime you require.Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Thanks Michael..yes it is 2012 version. Appreciate your sharing information.. surely helps. Thank you!
-> Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply