Monitoring SQL Server Version and Build Numbers

Problem

As proactive DBAs, we want to make sure that all of our instances are patched to the latest available build released by Microsoft or at least be aware of the latest available builds. Dealing with dozens or hundreds of instances can become a challenge. You might not have enough visibility and control over this particular item. This doesn’t mean that you should forget it, but rather overcome the challenge in a smart and efficient way, which this particular module aims to address.

Solution

The module presented within this article is built around my previous article to parse an HTML document using a PowerShell script to a get SQL Server build numbers. On top of that, it wraps the obtained output into a SQL Server table that can be used with this inventory solution. In the end, you will have a cool solution that can be automated to help you keep track of the patch level of all the instances under your support, so that you can plan and act accordingly.

Prerequisites

To properly use this module, it is required that you have the following elements in place:

Creation of the Master Server List
Creation of the database objects from the module Get-MSSQL-Instance-Values
The server from where the PowerShell script will be executed must have internet access.
The server from where the PowerShell script will be executed must also have an installation of Microsoft Office (a trial version can pull off the trick).

The script will notify you if there are elements missing, for instance, here’s what it looks like if you don’t have the elements created from the Get-MSSQL-Instance-Values:

Database Objects

For this particular module, only one temp table will be created. It will store the information of all the build numbers fetched from the HTML page. This is done this way so that we can compare, each time, what has been fetched from the URL against the values that we already have. That way, when missing entries are detected, it means that they belong to new releases that we previously didn’t have and we can act accordingly.

Table 1: Stores all the build numbers from the HTML page.

CREATE TABLE tmpBuildNumbers(
   [sp] [NVARCHAR](5) NULL,
   [cu] [NVARCHAR](5) NULL,
   [extra] [NVARCHAR](5) NULL,
   [build_number] [NVARCHAR](16) NULL,
   [release_date] [DATE] NULL
) ON [PRIMARY]

Table 2: Stores the build numbers that we will actually keep in our central database. This table is created and initially populated through the Get-MSSQL-Instance-Values script.

CREATE TABLE inventory.MSSQLBuildNumbers(
    [mssql_build_number_id] [int] NOT NULL IDENTITY PRIMARY KEY,
    [sp]                    [nvarchar](5) NULL,
    [cu]                    [nvarchar](5) NULL,
    [extra]                 [nvarchar](5) NULL,
    [build_number]          [nvarchar](16) NULL,
    [release_date]          [date] NULL
) ON [PRIMARY]

PowerShell Script

The PowerShell script that creates the temporary table, parses the content from the website that contains all the build numbers, and inserts data into the database is called:

Get-MSSQL-BuildNumbers.ps1

The script has some validations that will help you check if some key elements are missing for the script to run successfully. For instance, it will confirm that the inventory.MasterServerList table exists and that it has at least 1 active instance registered to be able to have something to work with.

If you have followed along the other modules, you will notice that we have been storing all objects in "C:\temp", but you can use any folder you want. If you make a change to the central folder location, you will need to edit all the references to the respective path.

How to Use

Navigate to the folder where you created the files and you can run the PowerShell script as follows:

Option 1

Right click on Get-MSSQL-BuildNumbers.ps1 and select Run with PowerShell

Option 2

Open a command window and navigate to the folder where you saved the above files and run

powershell "C:\temp\Get-MSSQL-BuildNumbers.ps1"

Option 3

Schedule this as a SQL Server Agent Job to run on a regular basis.

Option 4

Schedule this as a Windows Task Scheduler job to run on a regular basis.

Check Creation of Database and Objects

After running the PowerShell script, we can see the objects that are created.

If we query the inventory.MSSQLBuildNumbers, right after executing the script, we can see that it now contains additional records. This is because the module Get-MSSQL-Instance-Values initially populates the table with a set of build numbers from a file created some time ago.

The original file contains 370 records and the very first execution of the Get-MSSQL-BuildNumbers.ps1 script adds 18 entries that didn’t exist before. With each script execution, it will only keep adding the entries that are new.

To have a robust solution, after populating the inventory.MSSQLBuildNumbers table, the script will automatically call the module Get-MSSQL-Instance-Values. By doing this, you will automatically update any instance in your inventory that has been patched to a build number that you didn’t have before.

Checking for Errors

To check for errors query the monitoring.ErrorLog table using the following query:

SELECT *
FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-BuildNumbers'

If you’d like to know the SQL Server instance that got the errors, you would have to issue the query like this:

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' 
     THEN msl.server_name 
     ELSE CONCAT(msl.server_name,'\',msl.instance) 
     END AS instance,
   e.script,
   e.message,
   e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = 'Get-MSSQL-BuildNumbers

If you’d like to confirm if there were any updates in the build number for any of your instances, you can use the following query:

SELECT   *
FROM     audit.MSSQLInstanceValues
WHERE    field = 'Build Number'
ORDER BY data_collection_timestamp

Bonus Content

The main idea of this script is that you can automate it so that the build numbers list is always up-to-date, for all your instances. However, wouldn’t it be nice if you have a way to know when new build numbers are detected and logged inside the inventory.MSSQLBuildNumbers table? Of course, so that’s why I’m including an additional PowerShell script to send an email with that information.

The main script has a parameter called “$sendEmail” that has a default value of 0, so if you’d like to have the emails delivered, then you simply have to call the script like this:

powershell "C:\temp\Get-MSSQL-BuildNumbers.ps1 1"

At the bottom of the script you will find the values that you need to set for your own use case.

This is a sample screenshot of what the email looks like:

For the email script to work, you previously should have configured a database mail profile, which this article doesn’t cover, but you can find plenty of resources on MSSQLTips.

Here’s a useful query that you can use to determine if your instances are up-to-date, according to the current values stored within your own inventory.MSSQLBuildNumbers table:

SELECT 
   CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name
      ELSE CONCAT(msl.server_name,'\',msl.instance)
      END AS 'instance',
   bn.build_number,
   (SELECT TOP 1 build_number 
    FROM   inventory.MSSQLBuildNumbers 
    WHERE  LEFT(build_number,4) = LEFT(bn.build_number,4) 
    ORDER BY build_number DESC
   ) AS 'latest_build_number'
FROM   inventory.MSSQLInstanceValues iv
JOIN   inventory.MSSQLBuildNumbers bn ON iv.mssql_build_number_id = bn.mssql_build_number_id
JOIN   inventory.MasterServerList msl ON iv.serverId = msl.serverId

In my case, this is what the query returns, so you can get an idea:

Download Scripts

Download the scripts for this module

Next Steps

By automating the solution presented during this article, you can keep track of the patching status of all the instances under your care.

Check out the other parts of this series:

Resources

Community

Subscribe