We are getting approximately 50-100 lower environments SQL Servers newly build.

We are assigned some basic task of SQL DBA which includes one as patching SQL Servers.

How can this be achieved, specially when we have servers, where only Windows authentication can be used whose password needs to be generated every 24 hours. That makes this look complex or may be something I am expecting too.

Please share your inputs how can we achieve this either via powershell, cmd or any best known approach?

Edit: These boxes are all lower env like DEV/SAT/QA. Some of them have log shipping configured but that too for side reporting , even though not that critical. These are not 365*7 and are generally used during the business EST hours.

Also, we have multiple logins each assigned to a DBA who has sys admin rights on these boxes but that login needs to have a password generated which expires every 24 hours.

  • So you, as a user, are only allowed to log in using accounts with rotating passwords? Does this also apply to accounts in the sysadmin role? Also, can you expand on what type of setup you are currently running, such as the usage of Availability Groups, Failover Cluster Instances, or other HA features? Is this environment 24/7/365 or are there some maintenance windows you can take advantage of? Lots of factors here that could steer an answer in a particular direction. Aug 1 '17 at 12:48
  • @JohnEisbrener, thanks for inputs. I just edited the question Aug 1 '17 at 12:55

While the rotating passwords are a hindrance, they don't negate any approaches available. Kendra Little does a great job of explaining a general approach to patching servers in an automated fashion in this blog post, as well as some of the risks about when server patching goes bad.

Because your servers are not production, these would fall under her "cattle" classification which means that if you can tap into an existing automated toolset such as SCCM/WSUS (as denoted by Alen prior), you should do so. Patches that don't go through may be annoying, but they hopefully won't cause outages that wake you in the night. The rotating password complexity may require you customize this to a degree, and really the best approach there is to automate the process as best you can using PowerShell scripts.

Adam Bertram probably has the most robust toolset out there now that I'm aware of in this domain. I would suggest you check it out and see if you can augment it to your needs. Here's a direct link to his GitHub repository for the same toolset.

  • I have looked at Adam Betram's post and downloaded his powershell script. Can anyone please tell me how to execute it? I get the following RED error: Update-DbaInstance : The term 'Update-DbaInstance' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. At line:1 char:1 + Update-DbaInstance -ComputerName ***-****** -Path \\xxxxxx\soft ... + ~~~~~~~~~~~~~~~~~~ May 24 '19 at 14:03

Have you looked at WSUS for windows updates?

For SQL service packs we usually run them manually after scheduling downtime

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.