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.
In step8 unless you are connecting to a default SQL instance the SQLServername must be added in the form SQLSERVERSQLINSTANCE. Would be pretty obvious to DBAs but I had an issue with mine connecting until I realised the default instance had been renamed.
I also had to set the registry value “SqlInstanceIsRemote” to 1. With an internal DB setup it’s defaulted to 0.
as addition to Chris’ post:
7.6 right-click the SUSDB and click Properties
7.7 Select Permissions and highlight your Server account
7.8 Grant permissions.
At least when I followed the article and Chris’ post my SUS server only had Connect access to the database. After granting all of the permissions (dont know which are actually necessary, but it doesnt really matter either) the SUS server management worked again.
Had an issue moving an SP1 DB to an SP2 installation. The console would not connect after attaching the DB. We applied the windows update that updated our WSUS to SP2 from SP1 on the old server, but apparently applying to SP2 update does not update the DB schema. So after uninstalling and leaving all updates and the DB in place, I reinstalled and it said it would update the DB schema to SP2. After that, it worked sucessfully.
the App events didnt tell me much i’m afraid. I have got it working now.
Great article, thanks.
The Real Person!
The Real Person!
Colin, your event log should have entries indicating why it would not start.
Followed the steps exactly – wsus wont load after server restart.
Here’s a slightly more detailed #7:
7.1 Right click “Security”, it’s directly under “Databases” from #6. Select New -> Login…
7.2 In “Login name” enter {domain name}{wsus server}$ (ex, Contosowsus01$)
7.3 Select the “User Mappings” page in the upper left
7.4 Check the box next to SUSDB database, under the Default Schem column enter “dbo”
7.5 Click OK
This should be good for most simple installs. This is a bit insecure, as any application on the WSUS server can write anything to the database. If you have anything complicated going on contact a real DBA, and implement real security measures.
The Real Person!
The Real Person!
Hi markc, the machine login is added using the SQL management console. Here is a Technet article explaining the general steps:
http://technet.microsoft.com/en-us/library/aa337562.aspx
Yes I’m also struggling on step 7. It appears all the ms docs assume knowledge here, as does the above article. Where is this machine login added? Please elaborate with screenshots!
The Real Person!
The Real Person!
Hi Philip, you can create a login for a computer account by typing the computer name into the “Login name” field directly, instead of searching for it.
So for example, to add a login for the computer account WSUSSERVER in the domain LAB, you would type LABWSUSSERVER$ into the “Login name” field.
Hope that helps.
This is so helpful – but would you clarify point 7 please? I believe I’m failing in this step (and I’m not an SQL guy…).
I’m trying to add a computer account to the securitylogins group in SQL 2005 SP2, but I can only add user, group, or built-in security principal, no computer.