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.

Editor's Note: SQLServerCentral maintains build lists, each of which has an XLS download.

It works like this. I have a DB called ServerInfo 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? Use this query:

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:

Tip: don't 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 created a script that enumerates all the SQL Servers that 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 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