Infrastructure at your Service

Stéphane Haby

SQL Server – Change Management: list all updates

I am looking to have all SQL Server updates on a server including Service Packs, Cumulative Updates and other fixes like we can see in the uninstall panel from Windows.

installed-update.png

I take the opportunity of this blog to remind you in a little introduction the different types of patches for SQL Server:

  • Service Pack
    • Service Pack, known as SP is big set of Hotfixes.
    • The particularity is that the regression is fully tested and sometimes you can have new features or new enhancements.
    • The frequency is about 1 per year per version. Generally, the Service Pack 4 is the last one.
    • The recommendation is to install it (you have 1 year to install it) to continue to have the Microsoft support.
  • Cumulative Update
    • Cumulative Update knows as CU is a set of bug fixes. The regression is not fully tested.
    • The frequency is about 1 per 2-3 months per version. The next Service Pack included all CUs.
    • The recommendation is to install it if you have one of the problems from the bugs list.
  • FIX
    • FIX is a specific patch to fix a bug or a security problem. It is included in the next CU or SP.
    • The recommendation is to install a bug fix only if you have a problem but a security fix is to install ASAP.
  • GDR/LDR/QFE
    • In some cases, you find one of this trigram associated to a FIX.
    • GDR is for “General Distribution Release” branch is more tested that a LDR (Limited Distribution Release) or a QFE (Quick Fix Engineering)
    • In SQL Server, the GDR doesn’t include previous CUs but the QFE include all previous CUs

For example, the security fix KB2977325, MS14-044 for SQL Server 2012.
You have a GDR and a QFE for SQL Server 2012 Service Pack 1.
GDR is the build 11.00.3153 and is directly after the Service pack 1 (build 11.00.3000)
QFE is the build 11.00.3460 and is between the CU 11 (build 11.00.3449) and the CU 12 (11.00.3470)
You can see that you have a GDR and a QFE for SQL Server 2012 without the service pack.

My first step was to see on google (like everyone) if a PowerShell command exists to see if I have a list of my Hotfix.
And surprise, Get-Hotfix exists, wonderful — I try it!

Get-Hotfix.png

I have a lot of KBs but there is no useful information to filter for SQL Server.
And when I search the specific KB2674319 for the SQL Server 2012 Service Pack 1, I don’t find it.
I have just updates and security fix… it’s a shame!
In the note from the msdn here, you find “This cmdlet uses the Win32_QuickFixEngineering WMI class” and then you understand that it is just for Quick Fix Engineering (QFE).

I continue my investigation and find the PowerShell Object “Microsoft.Update.Session”.
Like Get-Hotfix, it is based on the Win32_QuickFixEngineering, I don’t have the SP1 but I have more information for hotfixes.

SP_list.png

My last search was the good one. It is to check directly the registry on the uninstall key (old method can every time be the best).

All installations are recorded in the key: HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall
A filter with “Hotfix*SQL*” and “Service Pack*SQL*” is apply to have just information about SQL Server.
We can add the version if needed like this: “Hotfix*SQL*2012*”

Get-ChildItem -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall | Get-ItemProperty | Sort-Object -Property DisplayName | Select-Object -Property DisplayName, DisplayVersion, InstallDate | Where-Object {($_.DisplayName -like "Hotfix*SQL*") -or ($_.DisplayName -like "Service Pack*SQL*")}| Format-List

And voila! I have all updates of SQL Server on my server.

hotfix_sp_list.png

Finally, the morality of this story is to take care when you use a PowerShell Command and search with the WMI Class associated to the cmdlet.
This feature is included in our tool DMK SQL Server to manage SQL Server instances.

5 Comments

  • Big Data Training in chennai says:

    It’s always great to get recommendations from people who have used particular products. Brian Winstead Associate Editor SQL Server Magazine.
    Big data training in
    chennai | Informatica training in chennai |
    PHP Training in Chennai |
    Web Designing Training in Chennai

  • shiva says:

    Hi Haby,
    This really helps!

    Im trying to run it on remote computers but it still displays the local machine results. it would be great if you can post the same query
    to run on remote computers.

    thanks
    shiv

  • shiva says:

    Here is the code im using

    $computers=Get-Content D:\Computers.txt
    $path=”HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall”
    $results=Foreach ($C in $Computers)
    {
    Get-ChildItem -Path $path | Get-ItemProperty | Sort-Object -Property DisplayName |
    Select-Object -Property DisplayName, DisplayVersion, InstallDate |
    Where-Object {($_.DisplayName -like “Hotfix*SQL*”) -or ($_.DisplayName -like “Service Pack*SQL*”)} | Format-Table
    }
    $results

    • StĂ©phane Haby says:

      Thank you Shiva, to share your code with me.
      In your code, you are never connected to the remote computer ($C is ot used).
      I suggest you to use PSSession or CIMSession to access remotely to computers.
      The best is to use PSSsession in your code.
      Regards,
      Stéphane

  • Raju Prasad says:

    Hi Siva,

    Nice Script but could you please help me to get get report from selected date as well as I want to export these report.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Haby
Stéphane Haby

Delivery Manager and Senior Consultant