Latest posts by Wolfgang Sommergut (see all)
- Migrate a WSUS database from WID to SQL Server - Mon, Jan 13 2020
- Audit changes in the Windows registry - Wed, Jan 8 2020
- Move WSUS database and content (updates) to a different drive or folder - Wed, Dec 11 2019
If you run WSUS on Server Core, all database options will unfortunately have a disadvantage. You cannot manage the default WID remotely; hence, you have to use command-line tools only.
SQL Server Express does not have this limitation, but it imposes a limit of 10 GB per database. A WSUS server can easily exceed this. A full-fledged SQL Server does not have these deficits, but there will be license fees.
Detaching SUSDB from WID ^
If you decide to switch from WID to SQL Server (Express) anyway, it works similarly to moving the WID database to another directory. The first step is to detach the WID database:
sqlcmd -E -S np:\\.\pipe\MICROSOFT##WID\tsql\query -Q "sp_detach_db 'SUSDB'"
Installing a SQL connection ^
After that, some preparatory work on the WSUS server is still necessary. This consists of removing the WID Connectivity feature and adding SQL Server Connectivity. You cannot use the two simultaneously.
Verify the current installation status with:
Get-WindowsFeature -Name UpdateServices*
The result also shows you whether WSUS is currently running on WID or SQL Server.
To uninstall WID, enter this command:
Remove-WindowsFeature -Name UpdateServices-WidDB
And add the SQL connection with this:
Install-WindowsFeature -Name UpdateServices-DB
Now, if necessary, copy the SUSDB files to another location to attach them to SQL Server.
Appending SUSDB to SQL Server ^
When configuring SQL Server, keep in mind that WSUS only allows Windows authentication. If the database is running on a separate server, you have to make sure the computer account of the WSUS server gets the necessary login permission.
Now you can attach SUSDB remotely from a workstation in SQL Server Management Studio. To do this, open the Databases context menu in the Object Explorer and execute the Attach command. In the following dialog, click on Add, navigate to the directory where you have stored SUSDB, and open the database.
Customizing WSUS configuration ^
Finally, you have to adapt the WSUS configuration to the new database. The following command serves this purpose:
wsusutil.exe postinstall SQL_INSTANCE_NAME="localhost" CONTENT_DIR=D:\Content
The CONTENT_DIR parameter is also required if the directory has not changed. In this example, SQL Server runs on the same server as WSUS, and they use the default instance. With a remote server, you would have to use the format hostname\instance name if the default instance is not used.
If you have set up a scheduled task for maintaining the WSUS database, you should also remember to switch it from WID to SQL Server. With a locally installed SQL Server, the command line for the default instance would change to:
sqlcmd -d SUSDB -i C:\<Path>\WsusDBMaintenance.sql
From SQL Server back to WID ^
You can also reverse the procedure described here. If for any reason you want to return from SQL Server to WID, first run the Tasks > Detach command from the SUSDB context menu in SQL Server Management Studio.
Follow this by uninstalling SQL Server Connectivity and adding WID Connectivity as described above. After that, attach the database to WID with:
sqlcmd.exe -E -S np:\\.\pipe\MICROSOFT##WID\tsql\query -Q "sp_attach_db @dbname=N'SUSDB',@filename1=N'D:\DB\SUSDB.mdf', @filename2=N'D:\DB\SUSDB_log.ldf'"
In this example, it is located under D:\db. You might want to adapt the path according to your environment. Finally, call this command:
wsusutil.exe postinstall CONTENT_DIR=D:\Content
Notice the command lacks the SQL_INSTANCE_NAME parameter.