Is SCCM failing to create WSUS indexes on your site server?

TL;DR: SCCM maintenance tasks for WSUS no longer work if you are using WSUS with the Windows Internal Database (WID).

When looking at the SCCM wsyncmgr.log, I noticed some SQL exceptions that occurred with each synchronization:

It’s easy to see what it’s trying to do: it’s trying to create some indexes to improve the performance of the WSUS database, and it’s trying to delete obsolete updates to keep the database a reasonable size. But those operations are always failing because it is specifying to use encrypted connections with that database — that’s not supported with the default Windows Internal Database (WID) instance used by WSUS (at least not on my Windows Server 2022 site server). Those aren’t fatal errors; the sync process continues on anyway. But it does mean that the WSUS optimizations implemented a long time ago in SCCM (looks like version 1906) aren’t working on the site server.

OK, but I can’t change how SCCM connects to WID, but I can do something else: I can move the database from WID to SQL Server (I’m using SQL Server 2019 at the moment) using the process documented here. Of course that documentation isn’t the greatest as it makes some assumptions, e.g. how to connect to the WID database instance using SQL Server Management Studio. A quick internet search (or a scan further down the page to the TSQL instructions) provides an answer for that:

The rest of the process is reasonably straight-forward. After that, the SCCM maintenance activities could be completed. Interestingly, the indexes were already present (so presumably SCCM didn’t try to make an encrypted connection at some point in the past) so that didn’t need to do anything, but the deletion of obsolete updates took 30 minutes, keeping an entire CPU core running the SQL Server process at 100% for the duration.

When did this process break? No idea, but if you are still using WID with WSUS and SCCM, you’ll want to take a look at your logs to see if it’s an action you should take.