«

»

Apr 28 2012

Migrating WSUS 3.0 from SQL Express to a remote SQL Server

When you installed WSUS in your environment you selected the option to use the internal database. Time passed, your database has grown and now you want to put that WSUS database on a dedicated SQL server instance, but unfortunately there is no wizard to perform that migration. That’s why I created this post, to show you how to migrate that internal WSUS database to a dedicated instance of SQL server, and believe me, is not hard at all.

I will use the latest WSUS version at this time, which is 3.0 with SP2 installed on Windows Server 2008 R2 SP1 using SQL server 2005 Express. The WSUS database will be migrated to a SQL server 2008 Enterprise which is also installed on a Windows Server 2008 R2 SP1 box. All machines are part of a domain. Let’s begin.

First we need to detach the WSUS database to be able to copy it on the dedicated SQL server. For this operation we are going to use SQL Server Management Studio Express 2005. Download the installation kit from here, then install it on the WSUS server. When you’re done, open the management console and connect to this instance \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query. Click Connect.

Expand the Databases folder, right-click the WSUS database (SUSDB), go to Tasks and click Detach.

On the Detach Database window check the box under Drop Connections, then click OK to detach the database.

Now your WSUS database should not be present any more under the Databases folder.

Copy the WSUS database and log files to a preferred location on the dedicated SQL server machine.

On the SQL 2008 server open the SQL Management Studio and connect to your instance. In this example I used a default instance.

Expand the Databases folder, right-click and choose Attach.

Click the Add button and locate the two files you copied from the WSUS server (SUSDB.mdf and SUSDB_log.ldf). Select the .mdf file and click OK.

In the Attach Databases window select the SUSDB_log.ldf file then click the Remove button.

Now you can click OK to attach the database.



Don’t close the Management Studio, because we need to give the WSUS server permissions to this database. Expand the Security folder, right-click Logins and choose New Login.

Here we need to add the WSUS computer account, but that Search button is useless for this operation. On the Login name box type your WSUS computer name in the form domain\WSUSComputer$. In my case is vkernel\Server-WSUS$. On the Default database choose the WSUS database.

On the left click the User Mapping option. Check the box next to the WSUS database, and on the Database role membership check the webService box. Now you can click OK.

Now the WSUS server has rights to connect to this SQL server instance.

We are almost done. All we need to do now is point the WSUS server to use the new SQL server instance. Go back to the WSUS server and open the registry editor (Start > Run > regedit). Browse to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Update Services\Server\Setup. Here we need to change three keys:

SqlServerName – put the new name of your SQL server and instance (server\instance). If you are using the default instance, like I am, the server name will suffice.

wYukonInstalled – set to 0 (zero)

SqlInstanceIsRemote – set to 1 (one)

Restart the Update Services and World Wide Web Publishing Service services, either by using the terminal or the Services console.

On the WSUS Management Console click the Reset Server Node button.

Now everything should be back to normal.

     

Want content like this delivered right to your

email inbox?


2 comments

  1. Peter Fisk

    This almost worked. I got problems connecting to the database afterwards. I had to grant ‘NT AUTHORITY\NETWORK SERVICE’ the ‘webService’ right. Also, since this login already existed, I had to first delete it from the SUSDB users, before I could grant the right.

    Then it worked. Thanks.

    1. Adrian Costea

      I’m glad it worked out for you after all, and thanks for sharing.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

css.php