In this article I will demonstrate how to migrate an existing WSUS 3.0 SP1 server from a local SQL Express instance to a remote SQL Server 2005 Database Services instance.
Firstly be aware of these limitations when deploying WSUS with a remote SQL Server instance.
- You cannot use a server configured as a domain controller for either the front end (FE) or the back end (BE) of the remote SQL pair.
- You cannot use a server running as a Terminal Services server for the front end of the remote SQL pair.
- You cannot use Windows Internal Database for database software on the back-end server.
- Both the front-end and the back-end servers must be joined to an Active Directory domain.
1. Download and install the SQLCmd tool on the WSUS server.
2. Install SQL Server 2005 "Client Tools Only" on the WSUS server so that you have access to the SQL Management Studio console.
3. Stop the IISAdmin and Update Services services in Computer Management. Note the other services that IISAdmin will stop, usually the World Wide Web service and the HTTP SSL service.
4. Use SQLCmd to detach the SUSDB.
C:\>sqlcmd -S np:\.pipeMSSQL$MICROSOFT##SSEEsqlquery 1> use master 2> alter database SUSDB set single_user with rollback immediate 3> go Changed database context to 'master' Nonqualified transactions are being rolled back. Estimated rollback completion 100% 1> sp_detach_db 'SUSDB' 2> go 1> exit
5. Copy the SUSDB.mdf and SUSDB_Log.ldf files from the WSUS server to the remote SQL server. Place them in the default locations for MDF and LDF files on the SQL server.
6. Attach the SUSDB to the remote SQL server.
7. Grant the WSUS server computer account permissions to the SUSDB on the remote SQL server.
8. Configure the WSUS server to use the remote SQL server for SUSDB by modifying the HKLMSoftwareMicrosoftUpdate ServicesServerSetupSQLServerName registry key.
9. Start the IISAdmin, World Wide Web Publishing Service, HTTP SSL, and Update Services services. Or you can just reboot the server.
10. Launch the WSUS administration console to verify the WSUS server is connecting to the database successfully. If WSUS is not working properly double-check the services in the previous step or try restarting the server. You can also review the Application event log for WSUS errors.