Sunday, August 21, 2016

New SQL Server Builds API.... improve your health checks with version and support information

Things have been a bit busy for me personally over the last few months but that is another story. What I have done is use some of this time to get back to a little project I started a while back.

Introducing the SQL Server Builds API..... providing version information including release dates, and known support life-cycle information.

The SQL Server Builds API provides SQL Server Version information and known support life-cycle information. As it is a personal project of mine the data is currently manually maintained based on known sources. The main objective of the tool is to enhance health check and project planning type processes, it is not intended for licensing or other legal auditing processes.

In prior roles and particularly in my current role one of the most common checks I perform on a SQL Server is the version and also the patching process. To aid in this what I would do is run reports based off my own custom database of version numbers to check and validate the version of the instance being checked.

Querying the API
To query the API it is pretty simple. The base URI is

The API takes two parameters

  • Version - This is a string representation of the version of your SQL Instance returned by SELECT SERVERPROPERTY('PRODUCTVERSION') e.g. '12.00.2342.00'
  • MissingUpdates - A boolean value (True/False) of if to include any updates released since the current patch level in the results

So if I wanted to query for information about the version number 12.00.2342.00 my completed URI would be something like:

NOTE: The MissingUpdates param defaults to false so you could omit it from the URI above.

Where the data comes from
The official source of truth with life-cycle data has to be Microsoft Support Life-cycle (, and this API/tool is by no means trying to replace that. In fact if you are doing any official auditing for licensing or any other legal means you MUST use that source over this API's data. At least perform your own validation of the data for each record.

Most DBA's and SQL admins would refer to I am no different and I would previously refer to that site as well. So, it is only natural that it is still one of the sources I go to for building my API's database.

The database behind this API is currently manually maintained, and even if it was somewhat automated mistakes can happen. If you find I am missing any updates, or if there is any incorrect data please make sure you reach out and let me know.

Suggestions for using the API
The API could be used in any number of tools and checks. Here are just some I can think of:
  • Custom health check scripts
  • Enhance on-premise CMDB data
  • PowerBI reports
  • Other custom Applications that interface/manage SQL Server instances

PowerShell Examples

#1 - Get version information (static version number)
# Set the version number to test
$VersionNumber = '12.00.2342.00' # SQL 2014

# Call the API to get the version information
$VersionData = Invoke-RestMethod -Uri "$($VersionNumber)";

# at this point simply return the $VersionData variable for the raw info

# Want to improve your Health Check script, calculate the health of Support Status
if ($VersionData.ExtendedSupportEnd -le (Get-Date)) {$SupportStatus = 'Critical'}
elseif ($VersionData.MainstreamSupportEnd -le (Get-Date)) {$SupportStatus = 'Warning'}
else {$SupportStatus = 'Ok'}

# format the output data string
$OutputData = @"
Instance = $($Instance.Name)
Version = $($VersionData.Version)
Product = $($VersionData.Product)
Branch = $($VersionData.Branch)
Update = $($VersionData.Update)
MainstreamSupportEnd = $($VersionData.MainstreamSupportEnd)
ExtendedSupportEnd = $($VersionData.ExtendedSupportEnd)
SupportStatus = $($SupportStatus)

# Return the hashtable
ConvertFrom-StringData -StringData $OutputData;

#2 - Get Missing updates (static version number)
# Set the version number to test
$VersionNumber = '11.00.2342.00' # SQL 2012

# Call the API to get the known missing updates (released since current version)
$VersionData = Invoke-RestMethod -Uri "$($VersionNumber)&MissingUpdates=$true";

# return the output, it is already formated as an Array

# or as a table
$VersionData | Format-Table -Property Version,Branch,Update,@{Name="Released";Expression={Get-Date $_.Released -Format 'yyyy-MM-dd'}},Description -AutoSize

Other Examples
There are more examples in my GitHub repository -


  • Simple static value examples like above
  • Both Single and Multiple Instance examples with live version number data direct from a SQL Instance
  • c# Windows Form client
Just want to see it in action, I have an MVC form available at

Where is the source code for the API
At this time I am not making the API opensource.

Want to partner with me
This is a personal project. If you have a similar project or a project that you would like to use this API in please reach out and lets improve the world together.

If you have any questions around this API please make sure you comment or reach out to me through any of my contact means.

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.


  1. Buy hublot fake watch, best breitling replica, breitling replica rolex watches , breitling replikas, u-boat italo fontana, replica cartier, hublot replica watches, fake watches breitling, replica montblanc, come here buy replica watches uk, rolex watches fake, watch replica breitling, replica tag heuer watches, omega watch copy, cheapest fake rolex, omega.

  2. The AAA Cartier Replica watches sale at are fake watchesbest quality cheap fake watch from China,The high end Cartier replica watches include Ballon Bleu De Cartier, replica cartier watchesCalibre De Cartier,Cartier La Dona,Cartier Love Watch,Cartier Pasha,Cartier Roadster,Cartier Santos,Cartier Tank replica watches,We can shipp to Uk,USA,canada,australia,and almost all country worldwide

  3. Originally introduced in 1972 and best replica watchesdesigned by Gerald Genta. replica royal oak watchesThe AP Royal Oak stands as the first luxury sports watch to be constructed from stainless steel, the Royal Oak collection has achieved iconic status and is now considered to be Audemars Piguet's flagship model. The watches have unique aesthetic appeal, thanks to their unmistakable octagonal bezels, while the dials each feature a trademark “tapisserie” pattern, adding to the pure visual appeal. Today, many variants of the watch are available, with their own unique features. These models include the Royal Oak Chronograph, which offers three different counters, as well as date display, and is water resistant for up to 50 meters.

  4. The Royal Oak Offshore collection replica watches uk has defied established conventions since 1993,replica royal oak watches giving an ever more sporty, masculine and powerful take on the iconic Royal Oak and its aesthetic codes.

  5. Late in the 19th century it was pocket and chatelaine watches that were most popular, but Louis replica Cartier watches saw a future in timepieces that could be worn on the wrist.Invest in an heirloom to cherish forever with a timepiece from the replica Ballon Bleu de Cartier watches watch collection.A fusion of cutting-edge technology and heritage craftsmanship, Cartier’s fine watches carry the prestige of the brand’s illustrious history and exceptional quality.

  6. Another Michele hit,Replica handbags the more structured Sylvie replica Gucci bags presents Gucci’s saddle-inspired red and green racing stripes – a house signature since the 1950s – in a fresh new light. Smooth leather and chunky hardware lends contemporary appeal to this instantly iconic Gucci bag.

  7. Best cheap Soccer Jerseys Shirts Wholesale Shop for Custom Team Soccer Jerseys Online,cheap xherdan shaqiri JerseysFCB Jerseys,cheap Euro 2020 Kit Top.

  8. Since 1848, Omega watches built a reputation of being the ultimate in luxurious elegance and sporty sophistication. In 1931, replica watches salethe brand entered the Geneva Observatory trials, replica omega watches salecompeting in several categories designed to test the rigor and mechanical reliability of the Omega timepiece. The brand won all six categories and thereby adopted the slogan, "Omega, exact time for life." In 1965, NASA selected Omega to be the official watch used in training and space exploration. A true giant in name and reliability, Omega produces elegant watches that are impervious to changes in fashion. At , we offer a variety of Omega watches for sale — at the lowest prices online. When you are ready to buy an Omega watch, browse our selection that includes the: Seamaster, Speedmaster, 1957 Trilogy, DeVille, Planet Ocean, and Constellation.

  9. replica cartier watches is the renowned French jeweller and watchmaker with a history of pioneering design and a drive for excellence. The replica calibre de cartier watchesfirst references to Cartier wristwatches date back to 1888. Late in the 19th century it was pocket and chatelaine watches that were most popular, but Louis Cartier saw a future in timepieces that could be worn on the wrist.

  10. Rolex offers a wide range of models ranging from professional to classic watches to suit any wrist.replica watches UK Explore the Rolex collection by selecting your favourite models, materials, bezels, cheap replica rolex watchesdials and bracelets to find the watch that was made for you.