How to Move the WSUS Database

How to Move the WSUS Database

  • Comments 6
  • Likes

 

I ran into an issue where a customer needed to move the WSUS Database to a different SQL server.  In my research I found most of the information below.  In testing I added a few pieces to it since my customer was using a remote SQL server.  To move your WSUS Database to another SQL  server just follow these steps, this should work with SQL 2005 or SQL 2008:

1. In the Services tool Right-click IIS Admin Service, and then click Stop.  It will stop other services (WWW Publishing, Windows Remote, and HTTP SSL).

2. In the Services tool, Right-click Update Services, and then click Stop.

3. On the old SQL server, DETACH the SUSDB database.  You can do this by right clicking the SUSDB, choosing Task and then choose detach.  You get the "Detach Database" wizard.  You should be able to just choose "OK" and it will start the detach process.  You may need to choose the "Drop Connections" box.

4. Copy the database files (MDF and LDF) from the old SQL server to the new SQL server. (Make a backup copy of these files as well.)  Most of the time these files will be in the MSSQL\Data directory.

5. Attach the database files as SUSDB to the new SQL server.  Right click Databases and choose Attach.  Point to the place where you copied the files and choose "OK".

a. Verify permissions on the SQL Server instance. In SQL Server Management Studio, open the instance and select Security, then Logins. The NT AUTHORITY\NETWORK SERVICE account should be listed as a login. If not listed, add the account as a login.  If the SQL server is a remote box then add the WSUS server's machine account to the permissions.

b. Verify permissions on the database. Right-click the database, select Properties and then click Permissions. The NT AUTHORITY\NETWORK SERVICE account should be listed as a login. If not listed, add the account as a login.  If the SQL server is a remote box then add the WSUS server's machine account to the permissions.

c. Verify members of the webService role. Under the WSUS database, select Security, select Roles, select Database Roles, then right-click webService and select Properties.  The NT AUTHORITY\NETWORK SERVICE account should be listed as a member of this role. If it is not, it should be added.   If the SQL server is a remote box then add the WSUS server's machine account to the permissions.

6. Back on the WSUS server, open the registry editor: Click Start, click Run, type regedit, and then click OK.

7. Browse to HKLM\SOFTWARE\Microsoft\UpdateServices\Server\Setup\SqlServerName, and in the Value box, type [ServerName]\[InstanceName],and then click OK. If the instance name is the default instance, then simply type [ServerName].

8. Click Start, point to Programs, point to Administrative Tools, and then click Services.

9. Restart the following services:   IIS Admin, HTTP SSL, WWW Publishing, and the Updates Services.

10. To verify that everything is working run the Update Services Console.

Comments
  • Thanks for the article - very concise and easy to follow.

  • Thanks a lot - move was simple and successful.

  • The only thing I can't figure out is how to "If the SQL server is a remote box then add the WSUS server's machine account to the permissions". I assume it means to add the computer account of the WSUS server to the list of logins that I checked to make sure the NT AUTHORITY\NETWORK SERVICE account is listed as a login. Because I am trying to put the database on a different server than the WSUS server.  But when I tried to add the computer account to the list, SQL server would not let me do that. That type of object is not allowed. I am trying to move it from the WSUS server to another SQL server so I can remove SQL from the WSUS server. I was able to detach, copy and then attach the database, and edit the registry on the WSUS box, but I could not add the machine account to the permissions. So far I cannot get it to work, I guess because of that.

  • @Sam - on the SQL Server login property, you should be able to add the computer account. In the Security>logins>New Login dialog, new Login Name: specify Domain\WSUSServerName$

    then the appropriate User Mapping.

  • I also had to set the registry value "SqlInstanceIsRemote" to 1. With an internal DB setup it's defaulted to 0.

  • Not to appear as stupid as I really am, but in the WSUS instance we have, the local database is accessed by the NT AUTHORITY\NETWORK SERVICE account and APPS1\WSUS Administrators account. APPs1 is the server name, not the domain account.

    So what do I need to do to "If not listed, add the account as a login. If the SQL server is a remote box then add the WSUS server's machine account to the permissions." in Steps 5 (a) and (b) above? This is just completely new to me and not sure just what is being asked.

    In Steve's comment above, I am not sure just what he recommends. I can't create a Windows login since it looks like the WSUS Administrators group is local on APPS1 and I don't see how I can make the APPS1 local NT AUTHORITY\NETWORK SERVICE user talk to another server without a domain account.

    I was able to create a SQL login called "domain\APPS1$" and in User Mapping, grant it DB_Owner and webService membership. Is that enough or am I missing something else. This just doesn't seem right but security isn't my strong suite...

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment