Using dbatools to verify your SQL Server instances version compliance

One of the main DBA’s duties is to guarantee that SQL Server instances are up-to-date in terms of patches (Service Packs, Cumulative Updates or Security Updates).

Recently, dbatools added a new command that turns this validation a piece of cake. Thanks to Simone Bizzotto (@niphlod) for baking up the command that Shawn Melton (@wsmelton) and I initially requested.

Some dbatools users already expressed their happiness with the command, like Jess Pomfret

So, I thought that this information should be shared with other people too.

Let me introduce to you – Test-DbaSqlBuild

This new command is available since v0.9.150.

If you are running this version or higher you can already take advantage of it, otherwise, you need to upgrade your module version first. Assuming you have installed the module from the PowerShell Gallery and that you have internet access, you can update as easy as running the following command:

Update-Module dbatools -Force

Otherwise, you can use the Save-Module command and then copy the files to your destination host.

How does the command works?

This command uses the dbatools-buildref-index.json file that contains all the information about SQL Server builds. This is the same file that feeds the dbatools builds table already shown on the introducing the community-driven build reference blog post.

The combinations

To run the command, we need at least two parameters. The -SqlInstance or -Build and one of the following 3: -MinimumBuild, -MaxBehind or -Latest.

The most straight example is when you want to check if the instance is running the latest build (it can be a security update even if not labelled as CU). To do that you just need to run:

Test-DbaSqlBuild -SqlInstance <instance> -Latest

In this example, I’m testing an instance that I have patched with SQL Server 2012 to SP4 but after that, the new security fix for Meltdown/Spectre was released, that is why the Compliant property shows False, it is not on the Latest existing build.
Note: If you just want to check for the latest SP and CU (leaving out the security patches) you need to use -MaxBehind "0CU"

Now, let’s say that you want to confirm that a specific instance is no more than 1 CU behind.
It’s easy as:

Test-DbaSqlBuild -SqlInstance <instance> -MaxBehind "1CU"

The output:

In this example, you can see that this instance is not compliant. Why? Because it is running the SQL Server 2016 SP1 CU5 but we asked for a max behind of 1 CU and that is the SP1 CU6 (because at the moment I’m writing this text, the most recent version is SP1 CU7).

Easy, right?
Keep in mind that for -MaxBehind you can also specify the number of service packs using -MaxBehind "1SP" and even use both, SP and CU like -MaxBehind "1SP 1CU".
Now, you can use multiple instances and verify them all like:

$SQLInstances = "SQL1", "SQL2", "SQL3"
Test-DbaSqlBuild -SqlInstance $SQLInstances -MaxBehind "1SP"

Other (real and useful) scenarios

We saw the “online” example where we will query each instance at the moment. Now, I want to share with you two more examples.

Using central database as data source

Let’s say you have a central database where you keep some of the information about your estate and one of those pieces of information is the SQL Server build version.

One code example:

$Instance = "<instance>"
$Database = "<centralDatabase>"
$InstancesTable = "dbo.Instances"
$SQLServersBuilds = Invoke-DbaSqlcmd -ServerInstance $Instance -Database $Database -Query "SELECT serverName, productVersion FROM $InstancesTable" 
$SQLServersBuilds | ForEach-Object {
    $build = $_.ProductVersion.SubString(0, $_.ProductVersion.LastIndexOf('.'))
    $serverName = $_.ServerName
    Test-DbaSqlBuild -Build $build -MaxBehind "1CU" | Select-Object @{Name="ServerName";Expression={$serverName}}, *
} | Out-GridView

For this example, I will query my dbo.Instances table and get the serverName and productVersion columns.
This is how it looks when running the select statement on SSMS:

You can pick that data and pass it to the Test-DbaSqlBuild command to know if it is compliant or not.

Then for each result, we will format the productVersion value to use just a 3 part value (it is how we catalog on dbatools build reference file) and pass it to the Test-DbaSqlBuild command.
In this example, I’m piping the output to Out-GridView so I can filter my results and add a filter for compliant equals false
.

Doing ad-hoc testing

The other example I would like to share is using the -Build parameter.
Imagine that you know that your SQL server instance is running build “13.0.4001” corresponding to SQL Server 2016 SP1, and you want to know if it is too far behind compared with the last available CU update. If we run the following command we will know it:

Test-DbaSqlBuild -Build "13.0.4001" -MaxBehind "0CU"

From this output we know that the most recent version is SP1 CU7 and we asked for latest SP1 (without CU), this means we are not Compliant

To give another example of this “ad-hoc” testing, we can use the following code provided by Simone Bizzotto to verify if our instances have the Meltdown/Spectre fix in place:

#Meltdown/Spectre check:
$mapping = @{
    '2008'   = '10.0.6556'
    '2008R2' = '10.50.6560'
    '2012'   = '11.0.7462'
    '2014'   = '12.0.5571'
    '2016'   = '13.0.4466'
    '2017'   = '14.0.3015'
}
$serv = 'SQL01','SQL02'
foreach($ref in (Get-DbaSqlBuildReference -SqlInstance $serv)) {
    Test-DbaSqlBuild -SqlInstance $ref.SqlInstance -MinimumBuild $mapping[$ref.NameLevel]
}

Thanks for reading.

3 thoughts on “Using dbatools to verify your SQL Server instances version compliance

  1. Awesome module – very useful, thanks ! Question: for SQL servers with no internet access, when a new CU is released. How is this updated to know about it? dbatools-buildref-index.json file
    I guess I’d need to update dbatools first before running Test-DbaSqlBuild?

    Like

    1. Hi Ewan, glad you enjoy it! Thanks for the feedback.

      We don’t need internet access to run the command 🙂 If you are running the command from a central place, you just need to have the module on that machine up-to-date. You can run the command with the -Update parameter so it will goes online and grab the most recent version and replace the local one.

      If you need to run the command local on a server without internet access you must replace the file wiht a most recent one.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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