Sql-server – How to avoid automatic patching for SQL Server 2016 via Windows automatic update service

sql serverwindows-update

While updating OS patches, we see that SQL Server is also receiving hotfix patches; we don't want to install SQL Server patches and we don't want to stop OS patches from installing.

Microsoft says "By default, Windows Update client is configured to provide updates only for Windows. If you enable the Give me updates for other Microsoft products when I update Windows setting, you also receive updates for other products, including security patches for Microsoft SQL Server and other Microsoft software."

I did check this setting on the server and it was off and grayed out.

Hence, I believe when SQL Server was installed, the below option was checked and that is causing it to receive updates:

image

So how can we disable it through some policy or registry key?

Best Answer

Possibility 1 - Azure Update Management

From Configure Windows Update settings for Update Management, which your question quotes:

By default, Windows Update client is configured to provide updates only for Windows. If you enable the Give me updates for other Microsoft products when I update Windows setting, you also receive updates for other products, including security patches for Microsoft SQL Server and other Microsoft software.

It goes on to say:

This option can be configured if you have downloaded and copied the latest Administrative template files available for Windows 2016 and higher.

If you are running Windows Server 2012 R2, this setting cannot be configured by Group Policy. Run the following PowerShell command on those machines. Update Management complies with this setting.

$ServiceManager = (New-Object -com "Microsoft.Update.ServiceManager")
$ServiceManager.Services
$ServiceID = "7971f918-a847-4430-9279-4a52d1efe18d"
$ServiceManager.AddService2($ServiceId,7,"")

Possibility 2 - Automated Patching

Microsoft has another document for Automated Patching for SQL Server in Azure Virtual Machines.

Some of the references to "Enable/Disable Automated Patching" are ambiguous as to whether they apply to just the SQL updates or to all updates.

Setting             Possible values            Description
-------             ---------------            -----------
Automated Patching  Enable/Disable (Disabled)  Enables or disables Automated Patching
                                                 for an Azure virtual machine.

Additionally, some text in a screenshot implies that it is all-or-nothing.

Set a patching window during which all Windows and SQL patches will be applied. (emphasis added)

But then it goes on to provide some Powershell code that sure looks like it can be configured specifically for SQL updates. To enable:

$vmname = "vmname"
$resourcegroupname = "resourcegroupname"
$aps = New-AzVMSqlServerAutoPatchingConfig -Enable -DayOfWeek "Thursday" -MaintenanceWindowStartingHour 11 -MaintenanceWindowDuration 120  -PatchCategory "Important"

And to disable:

To disable Automated Patching, run the same script without the -Enable parameter to the New-AzVMSqlServerAutoPatchingConfig. The absence of the -Enable parameter signals the command to disable the feature.

Because the Powershell command has "SqlServer" in the name, that sure implies that it can enable/disable SQL updates independently of any others.

Caveats

  • The Automated Patching document is from 07 March 2018. One of the joys of Azure is that they can change things for the better at any time. And one of the drawbacks of Azure is that they can change things at any time.

  • That document also mentions that not all SQL updates will be applied as part of this updating mechanism.

    Important Only Windows and SQL Server updates marked as Important or Critical are installed. Other SQL Server updates, such as service packs and cumulative updates that are not marked as Important or Critical must be installed manually.

    If you are seeing SQL service packs or cumulative updates getting applied, they must be coming from some other updating process.

Related Topic