WSUS – Migrate WSUS Database On Windows 2012 From Internal Database To SQL 2012

WSUS – Migrate WSUS Database On Windows 2012 From Internal Database To SQL 2012

Introduction

This article provides the steps required to migrate the WSUS Database from a Windows Internal Database to SQL 2012.

The process of moving the WSUS Database from a Windows Internal Database to an SQL 2012 server consist of the following :-

  • Stop the IIS Admin and Update Services Services
  • Prepare and detach the WSUS Database
  • Copy the WSUS Database to the SQL Server
  • Attach The WSUS Database In SQL 2012
  • Add Logins to the SQL Server
  • Modifying WSUS to connect to the SQL Server
  • Start the IIS Admin and Update Services Services
  • Confirm WSUS connectivity to the SQL Server
  • Uninstalling the Windows Internal Database

For the purposes of this article the SQL 2012 Server is running on the same server as the WSUS which are both running on a Windows 2012 Core Services installation.  The SQL 2012 Management Studio is running on a seperate Windows 2012 server.

Stop The IIS Admin And Update Services Services

The first step is to stop the IIS Admin and Update Services Services on the WSUS Server by performing the following steps :-

  • At the command prompt execute the following to stop the IIS Admin Service

net stop “IISAdmin”

  • At the command prompt execute the following to stop the Update Services Service

net stop “UpdateServices”

Prepare And Detach The WSUS Database

The next step of the process is to prepare the WSUS database and then detach it from the Windows Internal Database by performing the following steps :-

  • At the command prompt execute the following to connect to the Windows Internal Database

sqlcmd -S \\.\pipe\MICROSOFT##WID\tsql\query

  • The prompt will change to 1> when you are connected to the Windows Internal Database
  • At the 1> prompt execute the following connect to the master database

use master

  • The prompt will change to 2> when you are connected to the master database
  • At the 2> prompt execute the following commands to prepare the WSUS database

alter database SUSDB set single_user with rollback immediate

go

  • At the 2> prompt execute the following commands to detach the WSUS database

sp_detach_db ‘SUSDB’

go

  • Execute exit twice to disconnect from the Windows Internal Database

Copy The WSUS Database To The SQL Server

The next step is to copy the detached WSUS Database to the SQL Server by performing the following steps :-

  • In Windows Explorer navigate to the following directory on the server

c:\windows\WID\Data

  • Copy the two files SUSDB.mdf and SUSDB_log.ldf files to the SQL Server

Attach The WSUS Database To The SQL Server

The next step is to attach the copied WSUS Database to the SQL Server by performing the following steps :-

  • Open the SQL Management Studio and connect to the SQL Server
  • Expand the Server if necessary, Right Click on Databases, and then select Attach
  • Click on Add, navigate to the directory on the SQL Server where you copied the SUSDB.mdf and SUSDB_log.ldf files
  • Select the SUSDB.mdf file and then click on OK

Add Logins To The SQL Server

The next step is to add the logins required for the WSUS Database to the SQL Server by performing the following steps :-

  • Expand the Security branch, right click on Logins, and then select New Login
  • When prompted enter NT AUTHORITY\NETWORK SERVICE for theLogin Name
  • Click on User Mapping and confirm that the user has the dbo Default Schema granted on the SUSDB Database
  • Click on OK to create the login
  • Right click on Logins, and then select New Login
  • When prompted enter {Server Name}\WSUS Administrators for the Login Name

E.G. WSUSServer\WSUS Administrators

  • Click on User Mapping and confirm that the WSUS Administrators Group is granted access to the SUSDB Database
  • Click on OK to create the login.

Modify WSUS To Connect To The SQL Server

The next step is to modify WSUS to connect to the SQL Server by performing the following steps :-

  • Edit the Registry on the WSUS Server
  • Navigate to the Registry Key below :-

HKLM\SOFTWARE\Microsoft\Update Services\Server\Setup

  • Change the SqlServerName Value to the name of the SQL Server
  • Change the SqlInstanceIsRemote Value to 0

If the SqlInstanceIsRemote Value does not exist create it as a DWORD value

N.B. Setting it to 0 is used for if SQL is running on the same server, if you are moving it to a remote SQL Server change it to 1

  • Change the wYukonInstalled Value to 0

If the wYukonInstalled Value does not exist create it as a DWORD value

  • Exit the registry

Start The IIS Admin And Update Services Services

The next step is to stop the IIS Admin and Update Services Services on the WSUS Server by performing the following steps :-

  • At the command prompt execute the following to stop the IIS Admin Service

net start “IISAdmin”

  • At the command prompt execute the following to stop the Update Services Service

net start “UpdateServices”

Confirm WSUS Connectivity To The SQL Server

The next step is to confirm that WSUS is connecting to the SQL Server by performing the following steps :-

  • Open the Windows Server Update Services console
  • Confirm that WSUS is connected to the SQL Server and displaying the correct information

Uninstall The Windows Internal Database

The final step is to uninstall the Windows Internal Database by performing the following steps :-

  • Open Powershell
  • Execute the two commands below to uninstall the Windows Internal Database

uninstall-windowsfeature UpdataServices-WidDB

uninstall-windowsfeature Windows-Internal-Database

  • Once the Windows Internal Database features have been uninstalled Reboot the server