Using dbatools to improve your patching process

One of the the key responsibilities of a production DBA is keeping SQL servers patched. You have to have a good process in place, however; it won’t do you any good to apply a patch that just broke your application.

How do I get started?

Generally speaking, you’re either running an in-house application, or a 3rd party one. 3rd party is relatively easy! You should always check with your vendor first to see what is their supported level. They might only specify a major version (2012, 2016, etc), or even a minimum Service Pack or CU level. Once you have that information, spin up a test server, patch it and test it. I like to give it plenty of baking time, at least a week, before I’m comfortable with the patch.

An in-house application takes a bit more work, but it should also (hopefully) have more eyes on it. You need to get that patch through every pre-prod environment before you can even begin to think of production. My workflow will usually follow the same path as the application itself:

Dev -> QA -> Stage ->Prod

I still take a week between each pre-prod environment, all the while communicating with the dev and QA teams so they can report back on any issues they notice. Ideally you also have metrics in place to help you spot drops in performance, where you can check against your per-environment baseline.

Taking inventory

Your first step is to figure out where things are at, and organize them in some fashion. I have lots of teams, each with their environments, and that’s a lot of servers! I’m a huge fan of SQL Server Central Management Servers and Registered Servers, they’re a must have if you have more than a few servers. I try to keep the organization simple, like this

Obviously, you can group this however you want! I’ve learned that a smaller number of folders makes it easier to find things quickly. It’s also important to keep your list up-to-date! It may seem like extra work, but it really pays off, particularly for the topic at hand.

dbatools to the rescue

Now that you have your servers organized, it’s time to collect everyone’s server levels. First we create a table to store all the information:

CREATE TABLE [dbo].[PatchLevels] (
    [SqlInstance] [NVARCHAR](MAX) NULL,
    [Build] [NVARCHAR](MAX) NULL,
    [NameLevel] [NVARCHAR](MAX) NULL,
    [SPLevel] [NVARCHAR](MAX) NULL,
    [CULevel] [NVARCHAR](MAX) NULL,
    [KBLevel] [NVARCHAR](MAX) NULL,
    [BuildLevel] [NVARCHAR](MAX) NULL,
    [SupportedUntil] [DATETIME2] NULL,
    [MatchType] [NVARCHAR](MAX) NULL,
    [Warning] [NVARCHAR](MAX) NULL,
    [Compliant] [BIT] NULL,
    [MinimumBuild] [NVARCHAR](MAX) NULL,
    [MaxBehind] [NVARCHAR](MAX) NULL,
    [SPTarget] [NVARCHAR](MAX) NULL,
    [CUTarget] [NVARCHAR](MAX) NULL,
    [BuildTarget] [NVARCHAR](MAX) NULL
)

Now to the good stuff! I absolutely love dbatools, and cannot express how grateful I am for the work they’ve done, it’s saved me many hours of work and frustration. I just run this script to update the PatchLevels table:

$datatable = get-dbaregserver -sqlinstance <your central management server> |test-dbabuild -maxbehind "2CU" 
Write-DbaDbTableData -SqlInstance <your DBA server> -InputObject $datatable -table dbo.PatchLevels -database DBA -AutoCreateTable -Truncate

1. First we create a variable $DATATABLE to store the results of GET-DBAREGSERVER — this is your registered server list.
2. Next up we pipe those servers through TEST-DBABUILD, which gives us our server’s build levels. -MAXBEHIND flags any servers older than 2 Cumulative Updates (CU) as not being compliant
3. We then call on WRITE-DBADBTABLEDATA to save the values of our variable. It doesn’t apply a lot of smarts to the table creation, which is why the columns are all nvarchar(max), but you can definitely optimize the data types, and this will still work. This is the final results

This is the final result for the table

Look ma, no RDP

This is one of the things I still get giddy about — the actual patching without needing to login to each server. Like everything in life, there are a few caveats:

• We keep all our installation media and patches on a network share, organized by major version, so we need to run a script for each major version I support. I make that easier by just using a variable.
• The UPDATE-DBAINSTANCE command doesn’t know how to use your logged in credentials, so you have to prompt the user for them.

And here’s the actual script

#create a list of servers that you want to patch. Change the group name to match your target
$ServerList = get-dbaregserver -sqlinstance mssqlmgmt -Group QA
 
#create a variable to pass in to the Update-DbaInstance command; this will prompt you for your password
$cred = Get-Credential 
 
#Set the version that you want to update to
$version = '2017'
$UpdatePath = '\\<some server>\SQL\'+$($version)+'\updates'


#Start Patching! The -Restart option will allow it to restart the SQL Server as needed
Update-DbaInstance -ComputerName $ServerList -Path $UpdatePath -Credential $cred -Version $version -Restart
 

And that’s it! Like, really, that’s it. Just this last weekend, I updated 17 servers across 2014, 2016 and 2017 in about 30 mins, most of which I spent playing videogames while I waited for the patches to complete installing. The installs are not serialized, they’re run in parallel, so that’s part of the magic.

Once the scripts are completed, I just run the script to update the patch levels table. Now it’s time to let the proper teams know that updates are completed.

Thanks for reading! Here’s a few more links for your enjoyment:

dbatools website

SQL Server builds – wonderful list of every SQL server version and build numbers, along with links for their respective updates

Other things I’ve written