WSUS configuration and clean-up: your complete guide

What is WSUS and why does it matter?

WSUS (Windows Server Update Services) allows IT Administrators to easily deploy updates for Microsoft products to devices in their environment. Admins are provided a console, through which they can choose which updates to approve and which groups of devices to deploy them to. This helps admins ensure that vulnerabilities in their Windows operating systems and, in some cases, 3rd party applications are patched as quickly and efficiently as possible.

The functionality provided to admins by WSUS can be extended further by integration with Microsoft Configuration Manager, or PowerShell automation for daily tasks they would otherwise perform manually.

Not utilizing a system like WSUS potentially leaves your organization open to threats like malware, or ransomware attacks, which are becoming more and more frequent and can result in data breaches and loss of business.

WSUS deployment considerations

Before you install and configure WSUS, there are some considerations to think about. Let’s take a high-level look at them.

This information is taken from the Microsoft documentation.

System Requirements

The system requirements for WSUS are controlled by the number of devices in your organization that will receive updates from WSUS, so there is no one-size-fits-all guide, but there are some baseline recommendations.

These guidelines are from the Microsoft documentation and assume up to 30,000 clients are syncing with WSUS every 8 hours.

• 2Ghz or faster Processor
• 2GB of RAM above what is required by all other services and software
• 40GB of disk space or greater
• 1GB network adapter
• Server 2012 or later
  • Note: Server 2012 and 2012r2 will go end of support on October 10th 2023

Firewall rules

To obtain updates from Microsoft Update, the WSUS server uses port 80 for HTTP protocol and port 443 for HTTPS protocol. This is not configurable.

Access to the following URLs is required

• https://windowsupdate.microsoft.com
• https://*.windowsupdate.microsoft.com
• https://*.windowsupdate.microsoft.com
• https://*.update.microsoft.com
• https://*.update.microsoft.com
• https://*.windowsupdate.com
• https://download.windowsupdate.com
• https://download.microsoft.com
• https://*.download.windowsupdate.com
• https://wustat.windows.com
• https://ntservicepack.microsoft.com

Deployment

The following deployment types are available when designing and deploying WSUS.

• Single server
  • This scenario consists of 1 WSUS server which synchronizes with Windows Update and deploys updates to all devices in the organization.
• Multi-server
  • This scenario consists of 2 or more WSUS servers, 1 of which will be your upstream WSUS server.
  • The only server in this scenario that requires access to Windows Update is your upstream server, from which your other WSUS servers, or downstream servers, will synchronize.
  • Downstream WSUS servers can be spread out geographically to ensure the best connectivity for all devices.
• Disconnected Server
  • This scenario consists of 2 or more WSUS servers, 1 connected to the internet and 1 connected to your intranet but not the internet.
  • Admins would download and test updates on the internet-connected WSUS server and transfer them via USB or CD to the intranet WSUS server once testing was completed

Storage (Database)

There are 2 options for storing your WSUS Database:

1. WID (Windows Internal Database)

WID is the default configuration when deploying WSUS. The data is stored in %windir%\wid\data\ local to the server WSUS is being installed on.

Microsoft recommends the use of WID in the following cases:

• The organization has not purchased and does not require SQL server for any other applications
• The organization doesn’t require an NLB WSUS solution
• You intend to deploy multiple WSUS servers.

2. SQL-hosted database

Microsoft recommends the use of an SQL server database in the following cases:

• You require an NLB WSUS solution
• You already have at least one instance of SQL server installed
• You can’t run the SQL Server service under local, non-system accounts or by using SQL Server authentication. WSUS supports Windows authentication only.

Additional considerations for SQL server database

• WSUS supports the following editions of SQL Server:
  • Standard
  • Enterprise
  • Express
• WSUS can be remote from its database server, with the following additional criteria
  • The database server cannot be a domain controller
  • The WSUS server cannot run remote desktop services
  • The database server must be in the same Active Directory domain as the WSUS server, or have a trust relationship in place
  • The WSUS server and Database server must be in the same time zone or be synchronized to the same Coordinated Universal time source

Storage (Updates)

Each time WSUS synchronizes with Microsoft Update, metadata is stored in your WSUS database, and content is stored elsewhere, depending on how you have it configured.

There are 2 options when configuring where to store WSUS update content:

• Locally or on
• Microsoft Update servers.

Local storage is the default option when deploying WSUS, and has the following considerations

• Network connection between devices and WSUS server
• Sufficient disk space
  • 20GB minimum, 30GB recommended.

Storing updates locally can be useful if clients are more likely to connect direct to a high-speed corporate LAN, and help reduce bandwidth overheads on your WAN.

Remote storage is the alternative to storing updates locally and has its own considerations.

• Network connection between devices and Microsoft update

Remote storage becomes beneficial if local storage is at a premium, as the only thing stored locally is update metadata. All content is stored in Microsoft update, which is useful when your devices have a slow WAN connection but a high-speed internet connection.

ConfigMgr Integration

The requirements and considerations for your WSUS configuration and deployment may differ slightly if you plan to use ConfigMgr to manage your updates on top of WSUS.

More information on managing Windows server update services can be found here: Plan for software updates – Configuration Manager.

Summary so far

There are multiple ways to deploy and configure WSUS, and the considerations are going to be unique for each environment, so it isn’t my place to tell you how to plan your WSUS configuration. I’ll leave that up to you :). Where I can give you guidance and recommendations is keeping WSUS clean & tidy, making sure it performs optimally and provides a great experience for you and your users.

Going forward, we’ll focus on a simple WSUS deployment, with 1 WSUS server, 1 database (we’ll look at some WID and some SQL-related things), and some client devices. I’ll also touch on some of the benefits of using a SUP (Software Update Point) & ConfigMgr on top of WSUS.

So let’s take a look at that.

Configuring WSUS

We’re going to skip over the installation of WSUS, at least for now, but Microsoft has a great 14-step guide on how to install WSUS, which you can find here – Install the WSUS Server role.

Before we get into the nitty gritty of tweaking WSUS to run as optimally as possible, let’s take a quick look at the initial configuration of WSUS using the WSUS server configuration wizard as there are some important settings you don’t want to overlook.

Choose WSUS upstream server

For this example, we’ll stick with syncing from Microsoft Update, but if you were deploying a secondary, or downstream, WSUS server, you may want to sync from another WSUS server in your environment.

Specifying a proxy server

This is definitely going to be unique to your environment, but if your organization utilizes a proxy server, you will want to enter your proxy server information during this step.

Connect to upstream server

At this point, WSUS will attempt to connect to the upstream server configured previously to download update information before it can proceed. This can take a few minutes, so be patient 🙂

Choose languages

This step is the first one where you want to consider your choices. If you only have devices in English, then only download updates in English. If you have devices in multiple languages, select those specific languages. If you select all languages, WSUS will download all the localized packages for every update.

e.g. If an update is available in all 38 languages supported by WSUS, it will download that update in all 38 languages. This can impact bandwidth and disk space usage.

Choose products

Another important step to consider is what products you want to sync updates for. Similarly to the previous step, if you sync all products you’ll probably have a bad time. If you don’t have any Windows 10 devices, don’t sync Windows 10 updates, if you don’t have Windows Server 2003 (Which I hope you don’t), then don’t sync updates for it. Syncing unnecessary products will impact bandwidth and disk space usage.

Choose classifications

Each update from Microsoft, or 3rd party sources, is defined with an update classification to help organize different types of updates. Here are the available update classifications (taken from Microsoft documentation).

• Critical Updates: Specifies a widely released fix for a specific problem that addresses a critical, non-security-related bug.
• Definition Updates: Specifies a widely released and frequent software update that contains additions to a product’s definition database.
• Feature Packs: Specifies new product functionality that is first distributed outside of a product release and that’s typically included in the next full product release.
• Security Updates: Specifies a widely released fix for a product-specific, security-related vulnerability.
• Service Packs: Specifies a tested, cumulative set of all hotfixes, security updates, critical updates, and updates that are applied to a product. Additionally, service packs may contain additional fixes for problems that are found internally since the release of the product.
• Tools: Specifies a utility or feature that helps to complete one or more tasks.
• Update Rollups: Specifies a tested, cumulative set of hotfixes, security updates, critical updates, and updates that are packaged together for easy deployment. An update rollup generally addresses a specific area, such as a security or product component.
• Updates: Specifies a widely released fix for a specific problem. An update addresses a non-critical, non-security-related bug.
• Upgrade: Specifies an upgrade for Windows 10 or later features and functionality. These updates are also known as feature updates for Windows operating systems. Your software update points and sites must run a minimum of WSUS 6.2 with the hotfix 3095113 to get the Upgrade classification. For more information about installing this update and other updates for Upgrades, see Prerequisites for software updates.

Configure sync schedule

If you’re using WSUS on its own, you’ll likely want to configure the schedule to synchronize automatically. However, if you’re using ConfigMgr, or would rather sync manually, you can leave this set to synchronize manually.

Finished!

This is where you can choose to start your initial synchronization or wait for it to run on its own at the time configured previously. Additionally, you can choose to open the Windows Server Update Services Administration Console as well (It’s greyed out for me as I already have WSUS configured and I’m just rerunning the Windows Server Update Services configuration wizard)

Configure auto-approval rules

We won’t go through how to configure auto-approval rules, but these let you decide which updates are automatically approved for specific groups, which can save you time by taking away the need to manually approve them every time.

Additionally, you can configure how WSUS approves revisions of existing updates, and whether or not to automatically approve updates to WSUS itself.

Optimizations!

Now that your WSUS server is configured and, hopefully, finished its initial synchronization, let’s take a look at some optimizations that are recommended, but not configured by default.

This gets deep, so stay with me just a little longer!

Optimizing IIS

For some reason, unknown to me, there are recommended changes to your WSUS app pool in IIS that are not configured by default, but they can make a significant increase to your WSUS Server!

Patch My PC has a great article that covers these optimizations as well, but let’s take a look at them!

• Queue Length 2000 (up from default of 1000)
  • The queue length determines how many HTTP requests can be queued before the service returns 503, Service Unavailable. Doubling this simply increases the number of requests that can be queued.
• Idle Time-out (minutes) 0 (down from the default of 20)
  • By default, IIS drops its worker processes after 20 minutes of inactivity. Lowering this inactivity time-out to 0 means resources are freed up quicker.
• Ping Enabled False (from default of True)
  • IIS app pools use ping as a heartbeat. If your app pool is too busy to respond to the ping request it will be recycled, which we don’t want!
• Private Memory Limit (KB) 0 (unlimited, up from the default of 1,843,200 KB)
  • By default, the WSUS app pool has a private memory limit of 1.8GB and if this limit is reached, it will recycle the app pool.
• Regular Time Interval (minutes) 0 (to prevent a recycle, and modified from the default of 1740)
  • Every 29 hours the WSUS app pool will recycle itself, by default.

Everything listed above can cause the app pool to recycle over and over again, and whenever the WSUS App Pool recycles, it rebuilds its cache, which can be resource-intensive!

Here is a before and after of these changes!

Optimizing the SQL server database

This one is maybe slightly more involved, and if you have a DBA who maintains your databases you should totally chat with them as well before making any changes!

The most recommended and significant optimization is to add 2 non-clustered indexes to your WSUS database, and this applies to both WID (Windows internal database) and SQL server databases. Similar to the IIS optimizations, these are not added by default, but there are a few different ways to add them.

Firstly, if you’re using ConfigMgr on top of WSUS, you can enable an option in your Software Update Point that will create these indexes for you – Enable the built-in WSUS maintenance in ConfigMgr 1906 and newer

However, as we are talking specifically about a standalone WSUS configuration, let’s look at how to add these manually!

Firstly, it’s worth checking whether or not the indexes already exist in your WSUS database. You can do this by running the following query against your WSUS database:

USE SUSDB

IF EXISTS (
    SELECT *
    FROM sys.indexes AS si
    JOIN sys.objects AS so on si.object_id=so.object_id
    JOIN sys.schemas AS sc on so.schema_id=sc.schema_id
    WHERE 
		(so.name = 'tbLocalizedPropertyForRevision' /* Table */
        AND si.name = 'nclLocalizedPropertyID' /* Index */))
PRINT 'nclLocalizedPropertyID index exists'
ELSE PRINT 'nclLocalizedPropertyID DOES NOT EXIST';
GO

IF EXISTS (
    SELECT *
    FROM sys.indexes AS si
    JOIN sys.objects AS so on si.object_id=so.object_id
    JOIN sys.schemas AS sc on so.schema_id=sc.schema_id
    WHERE 
		(so.name = 'tbRevisionSupersedesUpdate' /* Table */
        AND si.name = 'nclSupercededUpdateID' /* Index */))
PRINT 'nclSupercededUpdateID index exists'
ELSE PRINT 'nclSupercededUpdateID DOES NOT EXIST';
GO

If they already exist, great! If not, let’s create them!

Creating them is just as simple as checking whether or not they exist, We can use the following query. This is detailed in the Microsoft documentation.

Note: to add missing indexes to WID, SQL server management studio express must be installed locally to run the reindexing script. To connect to WID, please review the Microsoft Documentation.

-- Create custom index in tbLocalizedPropertyForRevision
USE [SUSDB]

CREATE NONCLUSTERED INDEX [nclLocalizedPropertyID] ON [dbo].[tbLocalizedPropertyForRevision]
(
     [LocalizedPropertyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

-- Create custom index in tbRevisionSupersedesUpdate
CREATE NONCLUSTERED INDEX [nclSupercededUpdateID] ON [dbo].[tbRevisionSupersedesUpdate]
(
     [SupersededUpdateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Once added, it is recommended that you reindex your WSUS database, which again is relatively straightforward to do!

Note: to reindex WID, SQL server management studio express must be installed locally to run the reindexing script. To connect to WID, please review the Microsoft Documentation.

The following reindexing script is also provided by Microsoft and is detailed in their documentation.

USE SUSDB; 
GO 
SET NOCOUNT ON; 
 
-- Rebuild or reorganize indexes based on their fragmentation levels 
DECLARE @work_to_do TABLE ( 
    objectid int 
    , indexid int 
    , pagedensity float 
    , fragmentation float 
    , numrows int 
) 
 
DECLARE @objectid int; 
DECLARE @indexid int; 
DECLARE @schemaname nvarchar(130);  
DECLARE @objectname nvarchar(130);  
DECLARE @indexname nvarchar(130);  
DECLARE @numrows int 
DECLARE @density float; 
DECLARE @fragmentation float; 
DECLARE @command nvarchar(4000);  
DECLARE @fillfactorset bit 
DECLARE @numpages int 
 
-- Select indexes that need to be defragmented based on the following 
-- * Page density is low 
-- * External fragmentation is high in relation to index size 
PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)  
INSERT @work_to_do 
SELECT 
    f.object_id 
    , index_id 
    , avg_page_space_used_in_percent 
    , avg_fragmentation_in_percent 
    , record_count 
FROM  
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f 
WHERE 
    (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1) 
    or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0) 
    or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0) 
 
PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20)) 
 
PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121) 
 
SELECT @numpages = sum(ps.used_page_count) 
FROM 
    @work_to_do AS fi 
    INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
    INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id 
 
-- Declare the cursor for the list of indexes to be processed. 
DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do 
 
-- Open the cursor. 
OPEN curIndexes 
 
-- Loop through the indexes 
WHILE (1=1) 
BEGIN 
    FETCH NEXT FROM curIndexes 
    INTO @objectid, @indexid, @density, @fragmentation, @numrows; 
    IF @@FETCH_STATUS < 0 BREAK; 
 
    SELECT  
        @objectname = QUOTENAME(o.name) 
        , @schemaname = QUOTENAME(s.name) 
    FROM  
        sys.objects AS o 
        INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id 
    WHERE  
        o.object_id = @objectid; 
 
    SELECT  
        @indexname = QUOTENAME(name) 
        , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END 
    FROM  
        sys.indexes 
    WHERE 
        object_id = @objectid AND index_id = @indexid; 
 
    IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0) 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; 
    ELSE IF @numrows >= 5000 AND @fillfactorset = 0 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)'; 
    ELSE 
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; 
    PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command; 
    EXEC (@command); 
    PRINT convert(nvarchar, getdate(), 121) + N' Done.'; 
END 
 
-- Close and deallocate the cursor. 
CLOSE curIndexes; 
DEALLOCATE curIndexes; 
 
 
IF EXISTS (SELECT * FROM @work_to_do) 
BEGIN 
    PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20)) 
    SELECT @numpages = @numpages - sum(ps.used_page_count) 
    FROM 
        @work_to_do AS fi 
        INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id 
        INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id 
 
    PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20)) 
END 
GO 
 
 
--Update all statistics 
PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)  
EXEC sp_updatestats 
PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)  
GO

Checking your WSUS Server’s performance

As a rough guide to how well your WSUS server is performing, you can run another simple SQL query and measure the performance based on how long that query takes to complete.

use SUSDB;
GO
spSearchUpdates

Provided this query returns in under 30 seconds, this indicates a healthy environment. Anything over 150 seconds suggests potential performance issues with your database.

Declining unneeded updates

It is recommended not to have more than 1500 superseded & undeclined updates. This can cause both server and client-side performance issues.

ConfigMgr can perform a maintenance task based on your supersedence rules to automate this for you. You can read more about supersedence rules in the Microsoft documentation.

However, for WSUS standalone you need to do this either manually, or with PowerShell.

To manually decline an update, you simply need to right-click on it in the WSUS console and click Decline, then Yes.

However, nobody likes doing things manually, so I definitely recommend going down the PowerShell route to keep on top of declining your superseded updates.

Microsoft provide a great script for this, which you can schedule to run periodically or just run it yourself whenever you want to.

PowerShell script to decline superseded updates in WSUS

This script requires you to specify your WSUS server name, the port to connect on (HTTP or HTTPS), with some option parameters as well.

.PARAMETER UpdateServer
        Specify WSUS Server Name

    .PARAMETER Port
        WSUS Server Port

    .PARAMETER UseSSL
        Specifies whether WSUS Server is configured to use SSL

    .PARAMETER SkipDecline
        Runs decline script in audit mode

    .PARAMETER DeclineLastLevelOnly
        Whether to decline all superseded updates or only last level superseded updates

        Supersedence chain could have multiple updates. For example, Update1 supersedes Update2. Update2 supersedes Update3. In this scenario,
        the Last Level in the supersedence chain is Update3. To decline only the last level updates in the supersedence chain, specify the DeclineLastLevelOnly switch

    .PARAMETER ExclusionPeriod
        The number of days between today and the release date for which the superseded updates must not be declined.

Some examples of how to use this script can be found in the documentation as well.

.EXAMPLE
        # To do a test run against WSUS Server without SSL
        Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -Port 8530 -SkipDecline

    .EXAMPLE
        # To do a test run against WSUS Server using SSL
        Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8531 -SkipDecline

    .EXAMPLE
        # To decline all superseded updates on the WSUS Server using SSL
        Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8531

    .EXAMPLE
        # To decline only Last Level superseded updates on the WSUS Server using SSL
        Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8531 -DeclineLastLevelOnly

    .EXAMPLE
        # To decline all superseded updates on the WSUS Server using SSL but keep superseded updates published within the last 2 months (60 days)
        Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8531 -ExclusionPeriod 60

It hopefully goes without saying that you should always test scripts first before running them in a production environment.

WSUS cleanup wizard

The last thing we’ll look at in the WSUS console is the WSUS cleanup wizard.

There are 5 options available there

• Remove expired and old update revisions
• Delete computers that have not contacted the server in thirty days or more
• Delete unneeded update files
• Decline updates that are not approved and have been expired by Microsoft
• Decline superseded updates that meet all of the following criteria
  • Have not been approved for thirty days or more
  • Are not currently reported as needed by any client
  • Are superseded by an approved update

Some important notes and warnings from the Microsoft documentation.

Important: In a WSUS hierarchy, it is strongly recommended that you run the cleanup process on the lower-most, downstream/replica WSUS server first, and then move up the hierarchy. The upstream server should be the last one on which the cleanup is run.

Warning: Incorrectly running cleanup on any upstream server prior to running cleanup on every downstream server can cause a mismatch between the data that is present in upstream databases and downstream databases. The data mismatch can lead to synchronization failures between the upstream and downstream servers.

Important: If you remove unnecessary content by using the Server Cleanup Wizard, all the private update files that you have downloaded from the Microsoft Update Catalog site are also removed. You must reimport these files after you run the Server Cleanup Wizard.

If you are using ConfigMgr on top of WSUS, there is an option to instruct ConfigMgr to carry out the removal of obsolete updates from WSUS automatically each time the software update point syncs.

If you have never run any sort of cleanup on your WSUS database, you may find that the WSUS cleanup wizard needs to be run a few times to clear up everything, but you may also find that it crashes entirely. If you find that it crashes each time, try running it with 1 option selected at a time.

If, even with 1 option selected, it still crashes then the 2 recommendations would be to either

• reindex your WSUS database again
• Uninstall and reinstall the WSUS role, scroll back to the start of this blog post, and start from scratch with a fresh database
  • There are obviously things to take into consideration here, for example, all your client devices rescanning against your new WSUS server.

Supported versions

A lot of the time WSUS tends to go overlooked, and with it so can whether or not the version that is in use within your organization is still supported by Microsoft. WSUS version supportability goes hand in hand with the version of Windows Server.

Summary

All of the optimizations we’ve talked about today are worth looking into, but of course can also prove to be time-consuming, especially if you have multiple WSUS servers to check.

Patch My PC has a great tool that can check all of these optimizations for you, and indicate which ones need to be investigated further. You can find out more about this tool here.