Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

Check current patch levels for all SQL Servers in enviornment


By:   |   Read Comments (2)   |   Related Tips: More > Database Administration

Problem

Usually when a new database server gets built, it has the latest operating system service pack, and SQL Server is also installed with the latest service pack. Unfortunately, when there are many servers and instances, it's easy to forget to patch them all, both at the OS level and at the SQL level. Also, it is hard to determine if all of them have been patched to the latest version. And when there are issues, the OS and SQL build numbers are needed for the provider to troubleshoot.

Solution

Below you’ll find a PowerShell script that checks the OS version details and the SQL Server build, which then can be compared against the latest build to see if it needs to be patched or not. To speed up the process, it queries the servers in parallel, but constructs a single query to update the Inventory database table in a single connection.

In the script there are some variables you need to modify:

  • $server: the name of the server where you have stored your Inventory table
  • $database: the database where you have stored your Inventory table
  • $query: modify this depending on the names of your columns
  • $Maxthreads: query 20 servers at a time; if you’re tight on resources you can lower this number, but the results may take longer to appear

The way it works is as follows:

  1. Query table “Inventory” in database “Inventory”, which contains one entry for each instance with the details: ServerName, InstanceName, Version, BuildNumber, ServicePack, OSVersion, OSBuildNumber, OSServicePack and DatetimeLastUpdate.
  2. In parallel and for each server, query the SQL information (BuildNumber, ServicePack) and OS information (OSVersion, OSBuildNumber, OSServicePack), and construct a SQL update statement.
  3. When all threads have finished, we have a list of SQL update statements to run in our Inventory database. Note that if you don’t want to run these commands immediately, you need to comment the last line of the script.
  4. Finally, we can run a query to identify if we have the latest SQL Server build numbers, and a similar approach can be done to identify if we have the latest OS build number.

Script to create the inventory table and insert test records

The following script creates our Inventory table where we will list each server we need to connect to.  The script also adds a few sample records for testing.  You would need to insert the names of your servers to test in your environment.

CREATE TABLE [Inventory] (
   [ServerName] VARCHAR(128),
   [InstanceName] VARCHAR(128),
   [Version] VARCHAR(20),
   [BuildNumber] VARCHAR(20), 
   [ServicePack] VARCHAR(20), 
   [OSVersion] VARCHAR(20), 
   [OSBuildNumber] VARCHAR(20), 
   [OSServicePack] VARCHAR(20), 
   [DatetimeLastUpdate] DATETIME) 
 
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('nonexist', 'nonexist', 'Microsoft SQL Server 2000')
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('WIND01', 'WIND01', 'Microsoft SQL Server 2014')
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('WINT01', 'WINT01\TEST01', 'Microsoft SQL Server 2008')
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('WINP04', 'WINP04\PROD04', 'Microsoft SQL Server 2016')
INSERT INTO [Inventory] ([ServerName], [InstanceName], [Version]) VALUES ('WINP04', 'WINP04\PROD05', 'Microsoft SQL Server 2017')

PowerShell Script

Here is the script.  As mentioned, you will need to adjust the first couple of variables to match your environment.  Also, this script uses Windows authentication to connect to the servers.

$ErrorActionPreference = "Stop" #stop when an error is encountered
# Declare variables
$server = "."
$database = "Inventory"
$query = @"
SELECT [ServerName], [InstanceName]
  FROM [Inventory]
"@
$SleepTimer = 1000 #after X milliseconds, check if the jobs have finished. 1000 is every second.
$MaxResultTime = 300 #after X seconds, all jobs are killed. 300 is 5 minutes.
$Maxthreads = 20 #number of parallel jobs
# import modules
Import-Module SqlPs -DisableNameChecking
$error.clear() #clear error generated by last command
# get list of instances
$objects = @(Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query)
# environment setup
$output = ""
$errors = ""
$Jobs = @()
$ISS = [system.management.automation.runspaces.initialsessionstate]::CreateDefault()
$RunspacePool = [runspacefactory]::CreateRunspacePool(1, $MaxThreads, $ISS, $Host)
$RunspacePool.Open()
$script = {
   Param ([string]$serverName, [string]$instanceName)
   $ErrorActionPreference = "Stop"
   $query = "
      SET NOCOUNT ON --No 'rows returned' message
      SELECT SERVERPROPERTY('productversion') [ProductVersion], SERVERPROPERTY('productlevel') [ProductLevel]"
   try {
      $command = "UPDATE [Inventory] SET "
      # Get SQL info
      $data = sqlcmd -S $instanceName -Q $query -h-1 -W -b -r1 -s"\t" 2>&1 #no header, no whitespaces, break on error, errors to output, tab separator, stderr to stdout
      if (!$data -or $data.length -eq 0) { return $instanceName + ": Unable to query server" | Out-String } #server may not exist anymore
      $data = $data.split('\t')
      $command += "[BuildNumber] = '"+$data[0]+"'"
      # Get OS info
      $data = Get-WmiObject -class Win32_OperatingSystem -computername $serverName | select Version, BuildNumber, ServicePackMajorVersion
      $command += ", [OSVersion] = '"+$data."Version"+"', [OSBuildNumber] = '"+$data."BuildNumber"+"', [OSServicePack] = '"+$data."ServicePackMajorVersion"+"'"
      "" #indicate there was no error
      $command+", [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = '"+$instanceName+"';"
   }
   catch {
      return $instanceName + ": " + $LastExitCode + " " + $_ | foreach { $_.ToString() } | Out-String
   }   
}
function CreateThread() {
   param ([string]$serverName, [string]$instanceName, [ref]$Jobs)
   $PowershellThread = [powershell]::Create().AddScript($script) #scriptToRun
   $PowershellThread.AddArgument($serverName) | out-null
   $PowershellThread.AddArgument($instanceName) | out-null
   $PowershellThread.RunspacePool = $RunspacePool
   $Handle = $PowershellThread.BeginInvoke()
   $Job = "" | select Handle, Thread, object
   $Job.Handle = $Handle; $Job.Thread = $PowershellThread
   $Jobs.value += $Job
}
$ResultTimer = Get-Date #start time
#start processing first task for each instance
for ($i=0; $i -lt $objects.length; $i++) {
   CreateThread $objects[$i].ServerName $objects[$i].InstanceName ([ref]$Jobs)
}
while (@($Jobs | where {$_.Handle -ne $Null}).count -gt 0) {
   #update completed jobs, get errors and result, and dispose them
   foreach ($Job in @($Jobs | where {$_.Handle -ne $Null -and $_.Handle.IsCompleted -eq $True})) {
      $results = $Job.Thread.EndInvoke($Job.Handle)
      if ($results[0] -and $results[0] -ne "") {
         $errors += $results[0]
      }
      for ($i = 1; $i -lt $results.count; $i++) {
         $output += $results[$i]+"`r`n"
      }
      #end thread
      $Job.Thread.Dispose()
      $Job.Thread = $Null
      $Job.Handle = $Null
   }
   #show progress
   $inProgress = @($Jobs | where {$_.Handle.IsCompleted -eq $False}).count
   Write-Progress `
      -Activity "Gathering data" `
      -PercentComplete (($objects.length - $inProgress) * 100 / $objects.length) `
      -Status "$inProgress pending"
   #exit on timeout
   $currentTime = Get-Date
   if (($currentTime - $ResultTimer).totalseconds -gt $MaxResultTime) {
      Write-Error "Child script appears to be frozen, try increasing MaxResultTime"
      break
   }
   #sleep
   Start-Sleep -Milliseconds $SleepTimer
}
#dispose thread pools
$RunspacePool.Close() | Out-Null
$RunspacePool.Dispose() | Out-Null
$errors #return errors
Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $output
			
			

The output generated by the script is similar to the following. This is stored in the $output variable and the last line executes these commands to update the Inventory table.  If you don't want to update the data you can comment out the last line and add $output as the last line to see the what is stored in the $output variable.

PS C:\Users\PabloEcheverria\Desktop> .\UpdateInventory.ps1
nonexist: 1 HResult 0x102, Level 16, State 1
 
UPDATE [Inventory] SET [BuildNumber] = '12.0.4100.1', [OSVersion] = '6.1.7601', [OSBuildNumber] = '7601', [OSServicePack] = '1', [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = 'DEV01';
UPDATE [Inventory] SET [BuildNumber] = '10.0.5890.0', [OSVersion] = '6.1.7601', [OSBuildNumber] = '7601', [OSServicePack] = '1', [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = 'TEST01';
UPDATE [Inventory] SET [BuildNumber] = '13.0.4466.4', [OSVersion] = '6.1.7601', [OSBuildNumber] = '7601', [OSServicePack] = '1', [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = 'PROD04';
UPDATE [Inventory] SET [BuildNumber] = '14.0.3022.28', [OSVersion] = '6.1.7601', [OSBuildNumber] = '7601', [OSServicePack] = '1', [DatetimeLastUpdate] = GETDATE() WHERE [InstanceName] = 'PROD05';
			

After the information has been updated in the Inventory database, you can run the following query to determine if you have the latest SQL Server versions. Note that you need to update the “Build” column values to match the latest version at the moment you’re running the query, I personally use this link to get the latest build numbers for each version of SQL Server.

USE [Inventory]
GO
 
CREATE TABLE #Info ([Version] VARCHAR(128), [Build] VARCHAR(20))
INSERT INTO #Info VALUES ('Microsoft SQL Server 2000',    '8.00.2305')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2005',    '9.00.5324.00')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2008',    '10.0.6556.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2008 R2', '10.50.6560.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2012',    '11.0.7462.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2014',    '12.0.5579.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2016',    '13.0.4474.0')
INSERT INTO #Info VALUES ('Microsoft SQL Server 2017',    '14.0.3023.8')
 
SELECT [msl].[ServerName], [msl].[InstanceName], [DatetimeLastUpdate], [msl].[BuildNumber], [i].[Build] [LatestBuild], CASE WHEN ISNULL([msl].[BuildNumber], '') <> [i].[Build] THEN 'NO' ELSE 'YES' END [Latest?] 
FROM [Inventory] [msl]
LEFT JOIN #Info [i] ON [i].[Version] = [msl].[Version]
ORDER BY [latest?], [msl].[InstanceName]
 
DROP TABLE #Info

And here is the output from running the above query:

ServerName InstanceName DatetimeLastUpdate BuildNumber LatestBuild Latest?
nonexist nonexist 8.00.2305 NO
WIND01 WIND01 3/14/2018 18:28 12.0.4100.1 12.0.5571.0 NO
WINT01 WINT01\TEST01 3/14/2018 18:28 10.0.5890.0 10.0.6556.0 NO
WINP04 WINP04\PROD04 3/14/2018 18:28 13.0.4466.4 13.0.4466.4 YES
WINP04 WINP04\PROD05 3/14/2018 18:28 14.0.3022.28 14.0.3022.28 YES

Note that this script can be easily converted into a SQL Server job to run on a scheduled basis to check if all servers are patched to the latest version and notify you about the ones that aren’t or it can be run manually when you know there have been updates or you want to provide the latest information accurately. Also, a similar approach can be followed to identify if you have the latest OS patch level.

Next Steps
  • Start by creating your inventory database, and then run the process to update the information. Then check if you have the latest version for them.
  • You can learn more about SERVERPROPERTY in SQL 2016 by following this link.
  • You can learn more about PowerShell Get-WmiObject function by following this link.
  • You can automate the part to get the latest build numbers by following this link.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Pablo Echeverria I've worked for more than 10 years as a software programmer and analyst. Last year I switched jobs to a DBA position, where I've been suited to implement new processes and optimize existing ones.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, April 26, 2018 - 10:52:55 AM - Pablo Echeverria Back To Top

Hi Steve, I see you separated it by SP, that's also feasible and you just need to tweak the script a little bit. Although it is recommended to apply the latest CU, as now there won't be any more service packs.


Thursday, April 26, 2018 - 9:53:31 AM - steve sofar Back To Top

Well that's the query i made and run to check if my SQL Server is running the last patch

-- RUN THIS QUERY TO KNOW IF THE SQL SERVER VERSION IS RUNNING THE VERY LAST RELEASED VERSION 
SELECT 
'SQL_Version (Full)' =
CASE
--- SQL 2000
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '8.00.1%' THEN 'SQL 2000 RTM__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP4 !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '8.00.3%' THEN 'SQL 2000 SP1__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP4 !!' 
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '8.00.5%' THEN 'SQL 2000 SP2__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP4 !!' 
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '8.00.7%' THEN 'SQL 2000 SP3__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP4 !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '8.00.203%' AND CONVERT(sysname, SERVERPROPERTY('ProductVersion')) <> '8.00.2305' THEN 'SQL 2000 SP4__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply Latest CU !!' 
--- SQL 2005
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '9.00.1%' THEN 'SQL 2005 RTM__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP4 !!' 
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '9.00.2%' THEN 'SQL 2005 SP1__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP4 !!' 
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '9.00.3%' THEN 'SQL 2005 SP2__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP4 !!' 
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '9.00.4%' THEN 'SQL 2005 SP3__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP4 !!' 
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '9.00.5%' AND CONVERT(sysname, SERVERPROPERTY('ProductVersion')) <> '9.00.5324' THEN 'SQL 2005 SP4__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply Latest CU !!' 
--- SQL 2008
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '10.0.1%' THEN 'SQL 2008 RTM__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP3 !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '10.0.2%' THEN 'SQL 2008 SP1__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP3 !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '10.0.4%' THEN 'SQL 2008 SP2__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP3 !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '10.0.5%' THEN 'SQL 2008 SP3__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP3 !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '10.0.6%' AND CONVERT(sysname, SERVERPROPERTY('ProductVersion')) <> '10.00.6556' THEN 'SQL 2008 SP3__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply Latest CU !!' 
--- SQL 2008R2
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '10.50.1%' THEN 'SQL 2008-R2 RTM__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP3 !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '10.50.2%' THEN 'SQL 2008-R2 SP1__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP3 !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '10.50.4%' THEN 'SQL 2008-R2 SP2__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP3 !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '10.50.6%' AND CONVERT(sysname, SERVERPROPERTY('ProductVersion')) <> '10.50.6560' THEN 'SQL 2008-R2 SP3__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply Latest CU !!' 
--- SQL 2012
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '11.0.2%' THEN 'SQL 2012 RTM__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP4 !!' 
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '11.0.3%' THEN 'SQL 2012 SP1__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP4 !!' 
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '11.0.5%' THEN 'SQL 2012_SP2__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP4 !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '11.0.6%' THEN 'SQL 2012 SP3__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP4 !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '11.0.7%' AND CONVERT(sysname, SERVERPROPERTY('ProductVersion')) <> '11.0.7462' THEN 'SQL 2012 SP4__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply Latest CU !!'
--- SQL 2014
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '12.0.2%' THEN 'SQL 2014 RTM__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP2 !!' 
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '12.0.4%' THEN 'SQL 2014 SP1__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP2 !!' 
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '12.0.5%' AND CONVERT(sysname, SERVERPROPERTY('ProductVersion')) <> '12.0.5579' THEN 'SQL 2014 SP2__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply Latest CU !!' 
--- SQL 2016
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '13.0.1%' THEN 'SQL 2016 RTM__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP2 !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '13.0.4%' THEN 'SQL 2016 SP1__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply SP2 !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '13.0.5%' THEN 'SQL 2016 SP2__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) 
--- SQL 2017
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '14.0.1%' THEN 'SQL 2017 RTM__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply Latest CU !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '14.0.2%' THEN 'SQL 2017 RTM__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply Latest CU !!'
WHEN CONVERT(sysname, SERVERPROPERTY('ProductVersion')) LIKE '14.0.3%' AND CONVERT(sysname, SERVERPROPERTY('ProductVersion')) <> '14.0.3025.34' THEN 'SQL 2017 RTM__ '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) + '  --> !!  Apply Latest CU !!' 
ELSE 'YOUR SQL Version is up to date '+ CONVERT(sysname, SERVERPROPERTY('ProductVersion')) 
END


Learn more about SQL Server tools