It took a little effort to figure it out along with some research but I’ve finally managed to move the SUSDB from the SQL Server instance running on my System Center Configuration Manager VM back over to the WSUS VM with SQL Server 2005 Express Edition. Essentially I followed a combination of procedures that can be found at the following links:

In essence I did the following procedure:

  1. Install SQL Server 2005 Express Edition SP2 on the WSUS VM
  2. Stop “Update Services” on WSUS VM (to avoid updating and locking the SUSDB on the SQL Server 2005)
  3. Stop “IISAdmin” service on WSUS VM (this also stops the World Wide Web Publishing Service, the Windows Remote Management (WS-Management) service, and the HTTP SSL service)
  4. On the SCCM VM, stop the “IISAdmin” service (as above that also stops three other dependent services)
  5. Detach the SUSDB on SQL Server 2005 – this can be done using either the SQLCMD command line interface with the ‘sp_detach_db’ command or the SQL Server Management Studio
  6. With the SUSDB database detached, copy it from it’s current location (under SQL Server 2005 SP2 it’s found in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data) to the appropriate directory on the WSUS VM (the default location for the data files with SQL Server 2005 Express is the same as the full SQL Server)
  7. Once the SUSDB (and the attendant log file) has been copied over, attach it to the SQL Server 2005 Express instance. To do this I used the SQL Server Express Management Studio as shown i the pictures below
  8. Adding SUSDB to SQL Server 2005 Express

    Adding SUSDB to SQL Server 2005 Express

  9. After attaching the database to the SQL Server 2005 EE I then followed through with the steps outlined in the >WSUS Support Team’s blog: How to migrate your WSUS Windows Internal Database to SQL Server 2005 Express Edition. Except that in this case I don’t think I really needed to add the NT AUTHORITY\NETWORK SERVICE account as a login on the SQL Server 2005 EE instance since the WSUS software and the SQL Server were on the same VM.
  10. Nevertheless, I added NT AUTHORITY\NETWORK SERVICE to the SQL Server 2005 EE logins as shown below
  11. Adding NT AUTHORITY\NETWORK SERVICE to SQL Server 2005 EE Logins

    Adding NT AUTHORITY\NETWORK SERVICE to SQL Server 2005 EE Logins

  12. I discovered that you also needed to add the NT AUTHORITY\NETWORK SERVICE as a user in the actual SUSDB. This is subtle point that is not made clear by the WSUS Team Blog on this issue in their post. To do that you need to go to the actual database (in this case SUSDB) and under -> Security -> Users you create an account for the NETWORK SERVICE account.
  13. Addint NETWORK SERVICE as a user on SUSDB

    Addint NETWORK SERVICE as a user on SUSDB

  14. You also need to add NETWORK SERVICE to the webService role in SUSDB as shown below
  15. Adding NETWORK SERVICE to webService role in SUSDB

    Adding NETWORK SERVICE to webService role in SUSDB

  16. Finally, I changed the SqlServerName registry key in HKLM\Software\Microsoft\Update Services\Server\Setup from the original SQL Server 2005 system (winsrv-ca) to the WSUS server (SCCM). However, because I went from a SQL Server 2005 system to a SQL Server 2005 Express Edition database I had to append the instance name (in this case SQLExpress) in order to get the MMC snap-in to work.

A bit of a sidenote — the name of my WSUS server virtual machine is SCCM and the name of the machine running the System Center Configuration Manager software is actually winsrv-ca. It’s kind of confusing but I had already installed WSUS on the virtual machine SCCM before I decided that adding System Center Configuration Manager would be pretty much an overload on that one machine. That’s why I ended up installing Configuration Manager on winsrv-ca and not SCCM…but I had already modified AD to point to SCCM for WSUS services so I found it less troublesome to just leave the names as they are)

Finally, the job is done. Yes, I realize that it’s probably not a big thing but I wanted to see if it could be done. Most of the sites out there talk about migrating the WSUS database either from the Windows Internal Database (SQL Server Embedded Edition) to a SQL Server 2005 system or from an older SQL Server 2000 database to a SQL Server 2005 database. I haven’t seen anyone who tried to go from a full blown version of SQL Server 2005 to a SQL Server 2005 Express Edition (which, in many cases is more limited that the Windows Internal Database that WSUS can use). Anyway, it was fun…with a few snags along the way…but fun nontheless.

Advertisements