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 did here

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

latestexample

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: online_maxbehind_1cu

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: sqloutput_servername_productversion 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 centraldatabase_ogv.

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"

test_buildmaxbehind0cu

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.