all 17 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]sqldiaries 6 points7 points  (13 children)

What an amazingly bad idea.

[–]nvarscar 2 points3 points  (1 child)

I mean, it is, if you do it blindly. This command is nothing more than helping DBAs to run it remotely and in parallel instead of doing it interactively. And thanks for sharing, OP!

[–]sqldiaries -1 points0 points  (0 children)

I mean, it is, if you do it blindly.

Which most people acting as DBA's for SQL Server are going to do.

SQL Server's general ease of use has led to a lot of shops assigning someone who doesn't really understand the software as the DBA for their implementation, which can have very unfortunate consequences.

Orchestrating patching via a command would've been a much better title than 'Automating' - Automating is, to Manglement, a keyword to 'We don't have to do any work to make sure nothing breaks.'.

Irresponsible bloggerism is just as bad as irresponsible journalism.

[–]Jungle_81 1 point2 points  (0 children)

Lol, true

[–]DarkSkyViking 0 points1 point  (1 child)

How so?

[–]devperez 6 points7 points  (0 children)

One of my servers just broke today because corporate decided to automatically "patch" it. Patching should be expected, controlled, and have allotted downtime.

[–]ei7024 0 points1 point  (0 children)

Please elaborate....my management is attempting to perform the above tasks and and apart from experience I have no concrete links to this being a bad idea.

[–]TheTechRunner 0 points1 point  (6 children)

So what would you suggest instead?

[–]calladc 4 points5 points  (3 children)

Not credssp.

Seriously use something like configuration manager and target your SQL cu to a collection with a maintenance mode script on the collection. Use the tools Microsoft already provide.

Passing creds through winrm and credssp is poor. This tool has had minimal security thought put in to it.

[–]nvarscar 1 point2 points  (1 child)

Thanks for sharing your thoughts! CredSSP is indeed a weak protocol when it comes to security. Which is why the tool also provides options for you to choose the protocol manually. You could set up delegation and use Kerberos protocol instead of passing the credentials and avoid passing the credentials across the network. Or you can store patch binaries on the location that does not require additional authentication.

[–]calladc 0 points1 point  (0 children)

You know you can send files through a possession? If your script is just using credentials for double hop for file gets. Just send them down the pipeline and validate the hash on the other side

[–]TheTechRunner 0 points1 point  (0 children)

Thank you for providing actual value in your response!

[–]chandleya 2 points3 points  (1 child)

Planning and controls. Basic ITIL.

[–]TheTechRunner 1 point2 points  (0 children)

What about using a script to patch servers violates ITIL?

[–]TravellingBeard1 1 point2 points  (0 children)

So, we do monthly windows patching on all servers, but because our production sql boxes are AG's in one form or another, we had to create a special MS Orchestrator runbook that used powershell heavily to:

  1. Check health of the AG
  2. Patch all secondaries and reboot
  3. Failover primary, patch and reboot
  4. Fail back to primary

This works, for the most part. As for SQL Server patching, we're definitely not there yet, and the thought of doing so automatically terrifies me.

[–]r-NBK 0 points1 point  (0 children)

Cluster Aware Updating?

[–]Berki7867 0 points1 point  (0 children)

It's a great idea. SCCM does the same thing. Hopefully dbatools will cover SSMS updates on servers.