Properly determine SQL Server version and update level

Question

I have some existing code which tries to read the current version of SQL Server that's installed for a particular instance name by using a WMI query. It checks the first dotted part of the VERSION (to trigger an Upgrade install) and the SPLEVEL (to trigger a Patch install). This has worked well up to now. (I need to be able to get the version programmatically and without actually opening an SQL connection to the instance.)

However 2017 and 2019 have done away with service packs, rendering SPLEVEL useless, so I'm looking for an alternative to determine when only an older CU is present.

According to the table of version numbers, on my fresh 2019 install with CU4 applied, I should be seeing a VERSION of 15.0.4033.1 and a FILEVERSION of 2019.150.4033.1 (the actual page has a typo in the latter).

However while I am seeing the matching FILEVERSION, what I see for the VERSION is still the RTM value of 15.0.2000.5. This is also confirmed if I look at the Advanced Properties in SQL Server Configuration Manager. (And FWIW I also confirmed that SPLEVEL was still 0.)

Oddly, if I connect using Management Studio then it does show the version as 15.0.4033.1.

I don't mind switching to comparing the FILEVERSION if that's what's needed, but what has me concerned are the "GDR Builds". Looking at the equivalent table from 2017, there doesn't appear to be any particular rhyme or reason and a more recent GDR build can have a lower version number than an older CU build.

How can I make sense of this? What even is a GDR build and where does it come from anyway? Is it safe to assume they'll never "upgrade" an install that already has some CUs applied?

Answer 1

as a partial solution for this interesting problem, I would consider powershell:

first you need to Install the SQL Server PowerShell module on every server you need.

Get-SqlAgent -ServerInstance "MY_SERVER"

On the picture above you can see the ServerVersion that you are looking for.

Answer 2

[This is only a partial answer, as it doesn't address my concern regarding GDR build numbers.]

I've decided to take an alternate approach to reading the existing SQL Server instances and versions, which appears to work a bit more consistently (though the question about GDR builds remains), by reading the registry instead of using WMI. The process is as follows:

1. Read a list of instance names and associated registry key names from HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL.
2. For each one of interest, read version information from HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\key name\Setup. In particular:
   • PatchLevel has the full version number including CUs, eg. 15.0.4033.1.
   • Version has the full version number excluding CUs, eg. 15.0.2000.5.
   • SP has the service pack number (though this is presumably always going to be 0 from now on).
   • Oddly enough, there doesn't appear to be anywhere that you can read "SQL Server 2019" as a product name, or even just "2019".

One of the cases where I needed to distinguish these things was for an installer wrapper; you need to figure out in advance whether to pass /ACTION=Install, /ACTION=Upgrade, or /ACTION=Patch (and for the latter you have to pass it directly to a hotfix installer; it doesn't work on the full installer, no matter what the confusing error message says).

It'd be nice if the default installer figured this out for itself, but it doesn't. You just get very weird errors if you pass the wrong thing.