Migrating the WSUS Database from WID to SQL

Use the following steps to migrate the WSUS database (SUSDB) from a Windows Internal Database instance to a Local or Remote instance of SQL Server.

• SQL Instance. This can be the default MSSQLServer or a custom Instance.
• SQL Server Management Studio
• WSUS with WID role installed
• IIS (This is normally included when you install WSUS through Server Manager). It is not already installed, it will need to be.

From PowerShell (elevated), run:

    Stop-Service IISADMIN
    Stop-Service WsusService

1. Right-click SUSDB -> Tasks -> click Detach:
2. Check Drop Existing Connections and click OK (optional, if active connections exist).

        sqlcmd -S \\.\pipe\Microsoft##WID\tsql\query
        use master
        GO
        alter database SUSDB set single_user with rollback immediate
        GO
        sp_detach_db SUSDB
        GO

1. Copy SUSDB.mdf and SUSDB_log.ldf from the WID Data Folder (%SystemDrive%\Windows\WID\Data) to the SQL Instance Data Folder.

1. In SQL Server Management Studio, under the Instance node, right-click Databases, and then click Attach.
2. In the Attach Databases box, under Databases to attach, click the Add button and locate the SUSDB.mdf file (copied from the WID Folder), and then click OK.

   USE master;
   GO
   CREATE DATABASE SUSDB
   ON
       (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\SUSDB.mdf'),
       (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\SUSDB_Log.ldf')
       FOR ATTACH;
   GO

After attaching the SUSDB, verify that NT AUTHORITY\NETWORK SERVICE has login permissions to the instance of SQL Server by doing the following:

1. Go into SQL Server Management Studio
2. Opening the Instance
3. Click Security
4. Click Logins

The NT AUTHORITY\NETWORK SERVICE account should be listed. If it is not, you need to add it by adding New Login Name.

1. Right Click Logins and click New Login…
2. On the General page, fill out the Login name (NT AUTHORITY\NETWORK SERVICE), and set the Default database to SUSDB.
3. On the Server Roles page, ensure public and sysadmin are selected.
4. On the User Mapping page:
   • Under Users mapped to this login: select SUSDB
   • Under Database role membership for: SUSDB, ensure the following are checked:
     • public
     • webService
5. Click OK

You should now see NT AUTHORITY\NETWORK SERVICE under Logins.

1. Right-click the SUSDB
2. Select Properties
3. Click Permissions

The NT AUTHORITY\NETWORK SERVICE account should be listed.

1. If it is not, add the account.

2. On the Login name textbox, enter the WSUS machine in the following format:

   [FQDN]\[WSUSComputerName]$

3. Verify that the Default database is set to SUSDB.

   Tip

   In the following example, the FQDN is Contosto.com and the WSUS machine name is WsusMachine:

   

4. On the User Mapping page, select the SUSDB Database under Users mapped to this login

5. Check webservice under the Database role membership for: SUSDB:

6. Click OK to save settings.

   Note

   You may need to restart the SQL Service for the changes to take effect.

1. Click Start, click Run, type regedit, and then click OK.

2. Locate the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\UpdateServices\Server\Setup\SqlServerName

3. In the Value text box, type [ServerName]\[InstanceName], and then click OK. If the instance name is the default instance, type [ServerName].

4. Locate the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Update Services\Server\Setup\Installed Role Services\UpdateServices-WidDatabase

5. Rename the Key to UpdateServices-Database

   Note

   If you do not update this key, then WsusUtil will attempt to service the WID rather than the SQL Instance to which you have migrated.

From PowerShell (elevated), run:

    Start-Service IISADMIN
    Start-Service WsusService

Using PowerShell:

Uninstall-WindowsFeature -Name 'Windows-Internal-Database'

After the WID role is removed, verify that the following registry key is present: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Update Services\Server\Setup\Installed Role Services\UpdateServices-Database