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 1,332 times, 5 visits today)

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

  1. Eric Cobb Post authorReply

    You would just specify that in the version variable:

    $version = ‘2017CU5’

  2. Nama Reply

    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 ?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.