2016 SQL SP1 patch issue

發行項
03/02/2018

False alert?

If you have SQL2016 SP1 monitored in SCOM, you most likely have Compliance monitor warnings

This is actually a problem with SP1 where SQL did not update the registry key.

Two options to remedy:

Disable SCOM monitor per instance (or class if SP1 is NOT in your environment)
Fix the offending SQL Servers that are patched to SP1

Steps to fix the offending SQL Servers patched to SP1

Update Registry Key

Via PowerShell

TechNet forum is nice as well, but had to tweak it (blog listed here )

# Get Instance

$Instance = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server").InstalledInstance )

NOTE: If you have multiple instances, you will need a foreach loop

# Get Version

$Version = (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$((Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$Instance)\Setup").Version

# Match Version and set Registry Key
if ($Version -match '13.1.4')

{

Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$((Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$Instance)\Setup" -Name 'SP' -Value 1

}

# Verify

Get-ItemProperty -path "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\Setup" | ft SP

Steps broken out

Get Registry Key value via PowerShell

Get-ItemProperty -path "HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\Setup" | ft SP