Patching Multiple SQL Servers With Powershell and dbatools

This image has an empty alt attribute; its file name is dbatools-logo-1.pngPatching SQL Server can sometimes be a time consuming process, especially when you have multiple servers that need to be patched. Remoting in to each box to run through the update wizard is tedious, and if you have multiple patches to apply you’re going to be spending a considerable chunk of time on it.

Thankfully, the dbatool Powershell Module makes this process much easier! In fact, once you get it figured out, using Update-DbaInstance to patch your SQL Servers becomes very simple. But, getting it figured out can be a little confusing, so I’ve outlined the steps below that I use. The biggest hangups I had were setting up a central patching location and using a credential to access it.

Set Up A Central Patching Location

First, you need a network share to put your SQL Server patches on. This share needs to be accessible by all of the SQL Servers you are wanting to install updates on. What works for me is to have a directory structure of ‘SQLSERVER/VERSION/SP/CU’, which contains each executable file in the appropriate directory. So, for patching SQL Server 2016 I would have a path of ‘SQLSERVER/2016/SP2/’ which will look something like this:

Note that the SP2 directory contains the executable for SP2, as well as the directories for the other various CU updates that apply to SP2. Now all we have to do is specify the network path in our Update-DbaInstance call and it can find the patches that it needs. In my case I typically specify the root of the SP directory (SP2 in this case), and dbatools will figure out the CU level on its own from there. In theory, I could just point it to the root SQLSERVER/2016 directory (not shown) and it’s supposed to figure out what patches to apply. However I did not try from there, so I cannot say if it works or not.

Using A Credential With dbatools

This one stumped me for a bit. Once you figure it out it is super simple, but it took me a little while to find examples and get it working. For this you have to use the Get-Credential cmdlet, and then pass the credential in to the Update-DbaInstance call. Get-Credential isn’t part of the dbatools library, but rather a native powershell command.

Running the following command will create a credential for your Active Directory account and save it in a variable named “cred”. When you execute this command, it will prompt you for your AD password.

Specifying A Patch Level

One of the really nice things that Update-DbaInstance allows you to do is specify the patch level that you want to update your SQL Servers to. So, even though I have CU7 listed in my shared directory, I can tell Update-DbaInstance to only patch up to CU5 if I want. To do this, just specify a “version” in your command.

This took a little big of digging, and trial and error, for me, as I couldn’t find an details around how to format the version. But, basically you would specify the version in the format of “2016SP2CU5” for patching SQL 2016 to SP2 CU5.

Here’s The Full Script

Ok, enough talking (typing?), I know you only came to get the full script. One of the things I always do is run Get-DbaBuildReference to ensure that dbatools knows what the latest updates are. Here it is, with comments:


And that’s it! I have been testing and using this script a little bit for the last several months to do my SQL Server patching, and have decided that this is going to be my default patching method going forward.

(Visited 24,470 times, 1 visits today)

29 thoughts on “Patching Multiple SQL Servers With Powershell and dbatools

  1. Eric Cobb Post author

    You would just specify that in the version variable:

    $version = ‘2017CU5’

  2. Nama

    What if i have to run this powershell script in a sql job scheduled. I dont want it to prompt for password. Is there a way ?

  3. Jack

    How to patch multiple versions? 2012, 2014, 2016, in the same script?

  4. khaled

    Hi , does this tool support FCI cluster
    error got ARNING: [10:42:13][Update-DbaInstance] No SQL Server installations found on node1
    thanks

  5. vipul

    Hi,
    I have SQL Server 2017 for update CU
    i have run script with necessary changes but below error occurs

    Get-DbaBuildReference : A parameter cannot be found that matches parameter name ‘MajorVersion’.
    At line:4 char:23
    + Get-DbaBuildReference -MajorVersion 2017 -ServicePack 2 -Update
    + ~~~~~~~~~~~~~
    + CategoryInfo : InvalidArgument: (:) [Get-DbaBuildReference], ParameterBindingException
    + FullyQualifiedErrorId : NamedParameterNotFound,Get-DbaBuildReference

    WARNING: [08:12:20][Update-DbaInstance] Error while looking for SQL Server installations on SQL152008 | Connecting to remote server SQL152008 failed with the following error message : The WinRM client sent a reques
    t to an HTTP server and got a response saying the requested HTTP URL was not available. This is usually returned by a HTTP server that does not support the WS-Management protocol. For more information, see the abou
    t_Remote_Troubleshooting Help topic.

    Can you help me ?

    • Eric Cobb Post author

      Hi Vipul,

      I would recommend posting that error/warning on the dbatools slack channel (https://dbatools.io/slack/). They will have more knowledge about what that means than I will.

  6. Pingback: Patch Update apply and automate SQL server patch for multiple servers patching compliance by DBATools Powershell – Sql server Blog Forum

  7. Javier I Villegas

    Hi Eric,
    Great tool !! Thanks. Have a quick question. How can you use it to apply a SQL CU to a server running 4 SQL instances? Can you apply the CU to all the instances on a particular server? or you have to specify them in the server list ? $ServerList = ‘SERVER1\INST1,SERVER1\INST2,SERVER1\INST3…..’ Thanks !!

    • Avinash

      Nope picking one by one..which is time consuming.

  8. eric

    Hi Javier,
    either i specify a list of servers in the $Serverlist or either one by one , ie $serverlist=’sql1′, ‘sql2’,

  9. Ewan Campbell

    This is awesome but….
    Do you know why it restarts the SQL servers so many times?
    Tried this in a test environment – it rebooted the SQL servers 4 or 5 times.
    Applying one update level up – SQL 2016 SP2 CU10.
    This is too many times, once is enough.
    In Production 4 or 5 reboots is excessive.

    • Eric Cobb Post author

      Thanks Ewan,

      I have never seen it restart that many times. I would recommend posting that question on the dbatools slack channel (https://dbatools.io/slack/). They will have more knowledge about that than I will.

      Many times when I run the script it will detect a pending restart, which it must do because SQL Server will not allow an SP or CU to be installed if there is a pending restart. Then it will also restart SQL after each update it installs. So in a case like that, if I were to apply 1 SP and 1 CU that would be 3 restarts.

  10. Ewan Campbell

    Hi Eric. Thanks for the reply !
    Yes that is what I would expect as well.
    I’ll do more testing.
    Like the approach, it’s a good idea, saves a lot of time when patching lots of SQL servers.

  11. Pingback: Mettre à jour et appliquer des updates cumulatives a plusieurs instances en quelques minutes – DBA SQL Server BLOG

  12. Atul Tandon

    It’s a great tool , but just wanted to check if it can update multiple instance(with different version) at same time on same server?

  13. Sanj

    Great script!

    Does this also patch ssis,ssrs, r services etc which are installed on the server?

    Can we also use this script to apply security updates / hot fixes? If so, would we need to amend the script in any way?

    • Eric Cobb Post author

      Thanks Sanj!
      This script runs the update installer just like you would if you ran it manually, so it should update the entire SQL install on the server.
      I haven’t tried it, but if you do not specify a patch level then it should try to update to highest available level, so if you have any hot fixes downloaded it should use them. That’s just my theory, though. You may want to ask on https://dbatools.io/slack to be sure.

  14. Avinash

    Hi ERIC,
    How can we make the script run parallel on multiple servers.

    Thank you
    Avinash

  15. Prashasta

    What could be the $version value for SQL2017 & 19, where there are no SP’s and only CU’s?
    I tried using: $version = ‘2017RTMCU25’

  16. ohlssrog

    How can i patch multiple servers with different sql edition and deploy both servcie pack and CU package in same command/script

    Example 2 SQL 2016 needs Servcie Pack 3 and 2 SQL 2017 need CU 26

    • Eric Cobb Post author

      I believe you can pass in a list of the versions you want to patch to:
      $version = ‘2016SP2′,’2017CU26’

      You may also be able to omit the version parameter and have it automatically update to the highest version. I don’t remember if it will try to update the highest version available from MS, or if it’s the highest version found on your patch drive.

      I would recommend asking this on the dbatools slack channel. (https://dbatools.io/slack/)

Comments are closed.