Is your SQL Server up to date? Returning the latest version of Service Pack (SP) or Cumulative Update (CU) for your instance

Views: 773
Reading Time: 6 minutes

Hey guys!
In this article I want to bring you a solution I created yesterday to include in a checklist I'm developing, which allows me to identify the latest version of Service Pack (SP) and Cumulative Update (CU) for the version of SQL Server where I am running this code.

To identify the latest updates for each version, I will use the site https://sqlserverbuilds.blogspot.com/, which is always up to date with the latest SQL Server updates (could also be the https://buildnumbers.wordpress.com/sqlserver/).

To extract page data to SQL Server, I will use the OLE Automation, which I have published several articles explaining how to WebScraping WEB pages and APIs using this feature.

After retrieving the page's HTML source code, I need to identify the table that contains the data according to the version I'm looking for and also need to perform some treatment on the returned code to correct some inconsistencies:

  • HTML entity conversion (eg)
  • Unquoted tag attributes (eg class = sp instead of class = ”sp” = invalid HTML)
  • Open and not closed tags (ex: <td> Column1 <td> Column2 instead of <td>Column1</td> <td> Column2 </td>)

This turns out to be the hardest part of this script, because if these code inconsistencies are not remedied, SQL Server returns me an error when trying to convert XHTML to XML.

After performing all of these treatments and converting the XHTML code to XML, I apply XQuery commands to identify the first row of the respective SQL Server version table and return to SP

Source code

After a brief summary of what I needed to do to accomplish this task, I will provide below the source code of the solution created, which can be used with a simple “F5”.

Examples of use

Now I will demonstrate some examples of how this Stored Procedure works in practice.

Retrieving the last update from an updated SQL Server 2017

Result:

Retrieving the last update from a SQL Server 2017

Result:

Retrieving the last update from a SQL Server 2008 R2

Result:

Retrieving the last update from a SQL Server 2019

Result:

I need to show ALL updates of a version of SQL Server

If you want to show all updates of the desired version of SQL Server and not just the last one, you can use the code below (after the 150 line):

Result:

Conclusion

Well guys, I hope you enjoyed this article. I am starting to use it in environments to do a quick check up and it turned out to be very practical. If you want to create monitoring for this in your environment by analyzing all instances, you can create this Stored Procedure on all instances and use the returned data to send automated emails as needed.

In this monitoring scenario, another very interesting solution is that of Reginaldo Silvawho made the post Get-SQLBuildVersion a Different Way to Stay Updated, where he shared a solution written in PowerShell, which he developed, where he passes the instance name as a parameter and the script already does this validation and parameters to email the result. Very practical to use in multiple instances.

Like Reginaldo, I will highlight the article Cumulative update, apply or not, that's the question! do Luciano Moreira (Luti), where he gives his views on the constant application of SQL Server updates (I agree with him), since many DBAs do not have the habit of keeping SQL Server updated for fear of introducing a new bug after applying any updates.

In my view, this doesn't make much sense these days, as these events are quite rare, and as you always apply updates, your environment is always up to date with new features, bug fixes, and security enhancements, as well as having a process more mature and common application updates in the company.

Ah, remember that from SQL Server 2017, we will have no more Service Packs, only Cumulative Updates, as documented in this article here.

That's it, guys.
I hope you enjoyed this article and even more!