SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Download SQL patch Info

By Theo Ekelmans,

Any DBA worth his salt checks for available SQL patches on a regular basis and then checks the patch list against his servers. But let’s be honest here… it’s a mind numbingly boring repetitive task, especially if you have to check 130+ servers or so, and I for one was fed up with it and decided to automate this chore. In this article I will share with you the way I did it, the script I used and give a few pointers on how to go from there.

Everything starts by knowing what patches are available out there, and I think most of us get the latest SQL server patch info from this most excellent website: http://sqlserverbuilds.blogspot.com/ which is (as far as I can tell) maintained by the mysterious TZ since January 2007 (who or whatever you are, we all thank and love you for it!).

The most requested feature in the comments section of that site is a downloadable version of the build list, which to this day was not made available to the SQL community. Well.... look no further, my script joins forces with a truly brilliant standalone GNU tool called wget.exe to automate the extraction.

It works like this. I have a DB called SeverInfo on our central SQL management server that has linked servers to all the other SQL servers my team maintains. In this DB I have created 2 tables:

  • tblBuildListImport, the staging table (latest version)
  • tblBuildList, the reporting table (previous version)  

Phase 1 of the script does the following:

  • Executes WGET to download the HTML
  • Finds all the tables on the HTML
  • Identifies latest SP, CU, RTM and *new entries
  • Strips all the HTML tags from the rows
  • Chops up every row in their respective columns
  • Then it does a boatload of corrections on the ProductVersion column so you can use “select serverproperty('ProductVersion')” to match your SQL instance build against this table
  • Stores the output in a table variable @out

Phase 2 of the script does the following:

  • Saves @out into tblBuildListImport
  • Adds calculated columns for major, minor, buid and revision for easy sorting
  • It tries to save new records into tblBuildList
  • If it finds new records It sends an alert email notifying you of new patches saving you the hassle of checking for them
  • It then updates key columns in tblBuildList with the updated values found in tblBuildListImport because columns like URL’s and latest SP or CU do change.
  • And it tries to fill some usefull flags I added, like SP, CU, HF, CTP for reporting counts.

OK, now we have had an alert and we have a table with all the patches that are available, now what ?

Check to see what the lastest patch level is for every SQL version?

select  Version, ProductVersion, KBDescription, ReleaseDate
from    ServerInfo.dbo.tblBuildList 
where   LatestSP = 1 
or      LatestCU = 1
order by Major desc,Minor desc,BuildNr desc,Revision desc

Result:

Version ProductVersion KBDescription ReleaseDate
2016 13.0.4435.0 4019916 Cumulative update 3 (CU3) for SQL Server 2016 Service Pack 1 15-05-2017
2016 13.0.4001.0 Microsoft SQL Server 2016 Service Pack 1 (SP1) 16-11-2016
2014 12.0.5546.0 4013098 Cumulative update package 5 (CU5) for SQL Server 2014 Service Pack 2 18-04-2017
2014 12.0.5000.0 SQL Server 2014 Service Pack 2 (SP2) 11-07-2016
2012 11.0.6598.0 4016762 Cumulative update package 9 (CU9) for SQL Server 2012 Service Pack 3 15-05-2017
2012 11.0.6020.0 SQL Server 2012 Service Pack 3 (SP3) 23-11-2015
2008R2 10.50.6000.34 SQL Server 2008 R2 Service Pack 3 (SP3) 26-09-2014
2008 10.0.6000.0 SQL Server 2008 Service Pack 4 (SP4) 30-09-2014
2005 9.00.5266.00 2507769 Cumulative update package 3 (CU3) for SQL Server 2005 Service Pack 4 22-03-2011
2005 9.00.5000.00 SQL Server 2005 Service Pack 4 (SP4) 17-12-2010
2000 8.00.2039.0 SQL Server 2000 Service Pack 4 (SP4) 06-05-2005
7 7.00.1063.0 SQL Server 7.0 Service Pack 4 (SP4) 01-01-1900

Tip: dont try to get the server build by using select @@version, use select serverproperty('ProductVersion') instead. The column ProductVersion in tblBuildList has been normalized in such a way that it matches the output of serverproperty('ProductVersion').

Well…. just take it from here and run with it on your site / customers, or situation :)

How do I use it you say?

I shoved the script into a job that runs every 2 hours, so my info stays up to date, and built a website that shows me the enhanced data.

On my central management server I have made a script that enumerates all our SQL servers my team maintains and saves the info into a table. My colleague Arco has built automated monthly SSRS reports with the current patch state per customer per instance and we plan our patches from there.

Can't I I just use use your import, instead of setting up my own?

Sure!

I have made a public version available for you guys on: http://sqlbuilds.ekelmans.com/

On this site you can also download CSV, XML and JSON files or use a JSON API to get the tabular data if you don’t want to go through the hassle of maintaining you own import script.

And yes, they do update every 2 hours from  08:00-18:00 CET…. and you’re welcome to use them! 

Any remarks, ideas, extensions, flamewars, throw ‘em all into the discussion.

Happy patching,

Theo Ekelmans

The Netherlands

Installation Instructions and Links

You can download the wget tool from https://eternallybored.org/misc/wget/. There no install needed, you only need to download the wget.exe file and place it in the SQL log folder (search for a file named ERRORLOG), this is where the default script expects it. And to all that disagree with that location... just change the path to any location that works for you.

Since download links on SqlServerCentral are static, I have added the following download links which will be updated based on the community feedback and/or if the layout of the website changes:  http://www.ekelmans.com/FTP/Web/CreateServerInfoDbAndTables.sql and http://www.ekelmans.com/FTP/Web/ImportSqlserverbuildsBlogspotCom.sql

 
Total article views: 1626 | Views in the last 30 days: 1
 
Related Articles
ARTICLE

Patch Week

With SQL Server releasing new cumulative updates this week, Steve Jones talks about the patching str...

BLOG

Patch and Update SQL Server via Command Line

No one likes to patch or update SQL Server, it takes time and can make for very long days.  I...

FORUM

SP2 Patching

SQL Server SP2 patching process

BLOG

SQL Server Patching within an Always On Environment

SQL Server Patching with the introduction and adoption of Always On has changed.  The traditional me...

FORUM

SQL Server Patching

How often to do the SQL Server Patching, like quaterly patching done by Oracle

Tags
automate    
builds    
check    
download    
patches    
sql patches    
sqlserverbuilds    
 
Contribute