Do you allow your SQL Servers to have Windows updates installed by an automated process such as SCCM or Ivanti?
I would like to hear the thoughts of other DBAs. Thanks
A few jobs ago, yes, via SCOM. I miss it. In the intervening years I've had to patch things manually, and it can be a real slog.
I would allow it to do Windows updates, but not SQL cumulative updates. I'd also not let it install driver updates. Microsoft is making the patching a little too automated, it's either HERE'S ALL THE NEW PATCHES RIGHT NOW LET'S REBOOT IMMEDIATELY or completely turned off. It's a little nicer in the cloud, or was in Azure a few years ago.
If you have high availability concerns, you'd need to get your SCOM admins on board, they need to be aware of cluster nodes, log shipping pairs, etc., things that you don't want patched at the same time. They typically think in terms of OUs and other Active Directory groupings, so you need to plan something out ahead of time to keep things online when they need to be. There is such a thing as cluster-aware updating, I've never used it and am not sure how well it works.
There is a nice theory, can't really vouch for it in practice, is to patch your DR site first, make sure it passes some basic checks, then fail over all your stuff to the DR site. This lets you keep a working system fairly available (if something goes wrong, just fail back) and you actively test, and use, your DR configuration. This would be ideal, and to get it working you have to bite the bullet(s) and shake out all of the problems. The greatest benefit is knowing that DR will work when you actually need it to, and everyone is familiar and comfortable with it
If you're feeling really brave, you should also do password rotation at the same time, all your service accounts, and other passwords that need rotating. Automating it would be challenging, but it's a good challenge and a very useful skill. Because in a real disaster, for instance ransomware, you're going to have to do all of that anyway, and figuring it out for the first time in prod is no fun.
And while containers would not be a good way to approach automated patching, the mindset of containers is valuable. You have a machine/environment config that you want to be consistent, so you define that configuration programmatically and can create and re-create it at will. You can automate the deployment of that state anywhere, anytime. This is in essence what SCOM would do under the covers.
That's really good info, Rob. Thank you.
I was reluctant to do so but we recently went thru our first cycle of automated patching of 74 SQL Servers (Windows updates only) and it went surprisingly well.
Log-shipping got out of sync on one server and I had to force it to resync. The job agent did not auto start on another. Easily resolvable in both cases.
I agree on SQL updates - I'll be doing those manually.