Windows Automatic Updates on SQL Server: Control Them Instead of Turning Them Off

On a SQL Server machine where Windows Update runs with default settings, the decision about when updates are downloaded, installed and followed by a restart is left largely to the operating system. This can lead to an unexpected restart during business hours, a lengthy database recovery or, in clustered environments, an unplanned failover. Turning updates off entirely, on the other hand, leaves the server exposed to known security vulnerabilities.

The goal on SQL Server machines is therefore not to disable Windows updates, but to control when and how they are applied. In this guide, we explain why automatic updates pose a risk for SQL Server, how to check your current configuration and which configuration we recommend.

Why Automatic Updates Are a Risk for SQL Server

Most Windows updates require a restart to complete installation. On a desktop computer that means a minor interruption, but on a database server the impact is considerably wider:

• Open transactions are rolled back. When the SQL Server service stops, incomplete transactions are terminated, and when it starts again every database goes through recovery. On servers with long-running transactions, this can leave databases unavailable for several minutes. We cover what to check during such an interruption in our article on the first 30 minutes of a SQL Server outage.
• Clustered environments can see unplanned failovers. If a node in an Always On availability group or failover cluster instance restarts on its own, the workload moves to another node at an unexpected moment.
• Resource consumption increases during installation. Downloading and installing updates uses CPU, disk and network resources, and when this coincides with peak hours it can cause performance problems whose source isn’t obvious.
• SQL Server updates can be part of the same process. SQL Server security updates are also distributed through Microsoft Update, and the SQL Server service is stopped while they are installed.

Why Turning Updates Off Entirely Isn’t the Answer

Because of these risks, disabling Windows Update entirely is a common response. While this removes the restart problem, it introduces a far more serious one: a server that receives no updates remains exposed to every vulnerability that is published. Since a significant share of attacks reach SQL Server through the operating system, keeping the operating system up to date is a fundamental part of database security as well. We cover this in detail in our SQL Server security hardening guide.

How to Check Your Current Configuration

You can check how updates behave on your server in several places:

• sconfig: When you run sconfig on Windows Server and open the update settings menu, you can see whether the current mode is Automatic, Download only or Manual. According to Microsoft, the default mode is “Download only”: updates are scanned for and downloaded, but installation waits for an administrator. In “Automatic” mode, updates are installed every day at 3:00 AM.
• Group Policy: On domain-joined servers, the effective settings are defined through Group Policy. You can see which policies apply in the report generated by gpresult /h report.html.
• Registry: Settings defined by policy are stored under the registry key shown below.

# Windows Update settings defined by policy
Get-ItemProperty -Path 'HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU' -ErrorAction SilentlyContinue |
    Select-Object NoAutoUpdate, AUOptions, ScheduledInstallDay, ScheduledInstallTime, UseWUServer

# Who or which process initiated the most recent restarts?
Get-WinEvent -FilterHashtable @{ LogName = 'System'; Id = 1074 } -MaxEvents 10 |
    Select-Object TimeCreated, Message

Event 1074 in the System log shows which process initiated a restart and for what reason. If Windows Update components appear in this list for unexpected restarts, your update settings need to be reviewed. On the SQL Server side, you can see when the service last started with the following query:

SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;

Recommended Configuration

For SQL Server machines, the goal is for updates to be detected and downloaded automatically, while installation and restarts happen only during a planned maintenance window under an administrator’s control. Depending on the size of your environment, there are two ways to achieve this.

1. Centralized update management

In environments with more than a handful of servers, the most reliable approach is to distribute updates through WSUS, Configuration Manager or your central patch management tool. We recommend placing SQL Server machines in a dedicated group, approving updates for a test group first and releasing them to the production group only after the test results have been reviewed. This keeps full control over which server receives which update, and when.

2. Configuration through Group Policy or the registry

For the Computer Configuration > Administrative Templates > Windows Components > Windows Update > Configure Automatic Updates policy, we recommend option 7 – Notify for install and notify for restart, which Microsoft provides for servers. It is available on Windows Server 2016 and later: updates are downloaded automatically, but installation and restart wait for an administrator. On older servers, option 3 – Auto download and notify for install achieves a similar result.

We don’t recommend option 4 – Auto download and schedule the install for SQL Server machines. Even if the schedule matches the maintenance window, the restart happens without a DBA watching, and nobody checks the state of the service, the databases and the jobs afterwards.

On servers that aren’t joined to a domain, you can apply the same setting through the registry:

# Download updates, wait for an administrator to install and restart
$path = 'HKLM:\SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU'
New-Item -Path $path -Force | Out-Null
Set-ItemProperty -Path $path -Name NoAutoUpdate -Value 0 -Type DWord
Set-ItemProperty -Path $path -Name AUOptions    -Value 7 -Type DWord   # Windows Server 2016 and later

On domain-joined servers, Group Policy overrides local registry values, so the change should be made in the policy itself.

SQL Server Updates and Microsoft Update

If the option to receive updates for other Microsoft products is enabled in Windows Update, or if servers are connected to WSUS, SQL Server updates can also be offered through these channels. According to Microsoft’s documentation, the automatic updates channel offers only the GDR version of security updates to instances that haven’t yet received a CU, and the CU-branch version of security updates to instances that are already on the CU branch. The WSUS and Microsoft Update Catalog channel, by contrast, can offer a CU directly to an instance that has never been updated, on supported versions.

Planning a Monthly Maintenance Window

Microsoft releases security updates on the second Tuesday of each month, which makes it easier to plan updates on a predictable schedule. For standalone servers, the process we recommend is as follows:

1. Apply the updates to test servers in the week they are released, and confirm that applications, jobs and reports work as expected.
2. For production servers, use a maintenance window agreed in advance with the application owners.
3. Before updating, confirm that the most recent backups completed successfully.
4. Install the updates and restart the server.
5. After the restart, confirm that the SQL Server and SQL Server Agent services are running, that every database is online and that the error log shows no unexpected errors.

-- After the restart: is any database not online?
SELECT name, state_desc
FROM sys.databases
WHERE state_desc <> N'ONLINE';

In Always On availability group and failover cluster instance environments, updates must be applied node by node, in a specific order and with manual failovers. We cover this process step by step in our guide to patching SQL Server and Windows on Always On.