Migrating from Windows Internal Database to SQL Server 2005
This topic explains how to migrate the WSUS database (SUSDB) from a Windows Internal Database instance (installed by default during WSUS setup) to a full version of Microsoft SQL Server 2005.
- If you chose to use Windows Internal Database as the WSUS database when you set up your WSUS server, you may want to upgrade the database engine to a full installation of SQL Server 2005. SQL Server 2005 allows you to administer the WSUS database through the SQL Server Management Studio.
- WSUS requires SQL Server 2005 with Service Pack 1. If you use the full version of SQL Server, the database administrator should first verify that the nested triggers option is turned on before setting up the WSUS database.
- You cannot use SQL authentication. WSUS supports Windows authentication only. WSUS setup creates a database named SUSDB.
The following scenarios are presented in this topic:
- Migrating the Windows Internal Database database to a SQL Server 2005 instance running on the WSUS server
- Migrating the Windows Internal Database database to a SQL Server 2005 instance running on another server (remote SQL)
Migrating the WSUS database from a Windows Internal Database instance to a SQL Server 2005 instance running on the WSUS server
Use the following steps to migrate the WSUS database from a Windows Internal Database instance to a SQL Server 2005 instance.
Install SQL Server 2005 (with the Server and Client Tools option) and SQL Server 2005 Service Pack 1 or higher on your WSUS server.
Stop the IIS Admin service and the Update Services service:
- Click Start, point to Programs, point to Administrative Tools, and then click Services.
- Right-click IIS Admin Service, and then click Stop.
- Right-click Update Services, and then click Stop.
Detach the WSUS database (SUSDB) from the Windows Internal Database instance. You will need to use the sqlcmd utility, which can be downloaded from Feature Pack for Microsoft SQL Server 2005 (https://go.microsoft.com/fwlink/?LinkId=70728). For more information about the sqlcmd utility, see sqlcmd Utility (https://go.microsoft.com/fwlink/?LinkId=81183).
sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query use master alter database SUSDB set single_user with rollback immediate go sp_detach_db SUSDB go
- Attach SUSDB to the destination SQL instance.
- In SQL Server Management Studio, under the instance node, right-click Databases, select Properties, and then click Attach.
- In the Attach Databases box, under Databases to attach, browse to the location of the susdb.mdf file (by default this is C:\WSUS\UpdateServicesDbFiles if you installed Windows Internal Database), and then click OK.
- Verify that NT AUTHORITY\NETWORK SERVICE has login permissions to the SQL Server instance and to the WSUS database. If it does not, you will need to add it to both locations. This account should also be a member of the webService role on the WSUS database.
- 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 it is not, it should be added.
- 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 it is not, it should be added.
- Verify members of the webService role. Under the WSUS database, select 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.
- Edit the registry to point WSUS to the SQL instance that now holds SUSDB.
- Click Start, click Run, type regedit, and then click OK.
- Find the following key: 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].
- Open Services and then start the IIS Admin service and Update Services service.
- Click Start, point to Programs, point to Administrative Tools, and then click Services.
- Right-click IIS Admin Service, and then click Start.
- Right-click Update Services, and then click Start.
- Verify that the database migration has been successful by opening the WSUS administrative console (click Start, click Administrative Tools, and then click Microsoft Windows Server Update Services 3.0).
备注
You might have to restart the server for these settings to take effect.
Migrating the WSUS database from a Windows Internal Database instance to a SQL Server 2005 instance on a remote server
The goal of this scenario is to take the WSUS database (SUSDB) running in a Windows Internal Database instance on the WSUS server and move and upgrade it to a SQL Server 2005 instance running on a remote server. Only a full SQL Server 2005 database may be used in a remote SQL installation. Note that in each step, where appropriate, it is noted on which server you must perform the procedures.
- 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.
- FE starting configuration:
- Windows Server 2003 Service Pack 1 or Windows Server 2008 operating system
- WSUS with Windows Internal Database
- BE starting configuration:
- Windows Server 2003 Service Pack 1 or Windows Server 2008 operating system
- SQL Server 2005
This step will enable you to use the SQL Server Enterprise Manager on FE.
- Click Start, point to Programs, point to Administrative Tools, and then click Services.
- Right-click IIS Admin Service, and then click Stop.
- Right-click Update Services, and then click Stop.
sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
use master
alter database SUSDB set single_user with rollback immediate
go
sp_detach_db ‘SUSDB’
go
- In Step 2, you noted the folder location on FE where these files are stored. Copy the files to this folder on BE.
- Attach SUSDB to the destination SQL instance.
- Under the instance node, right-click Databases, select Properties, and then click Attach.
- In the Attach Databases box, under Databases to attach, browse to the location of the susdb.mdf file (by default this is C:\WSUS\UpdateServicesDbFiles if you installed Windows Internal Database), and then click OK.
Step 6 [on BE]: Verify that the FE machine account has login permissions to the SQL Server instance and to the WSUS database.
- Verify permissions on the SQL Server instance. In SQL Server Management Studio, open the instance and select Security, then Logins. The FE machine account should be listed as a login. If it is not, it should be added.
- Verify permissions on the database. Right-click the database, select Properties and then click Permissions. The FE machine account should be listed as a login. If it is not, it should be added.
- Verify members of the webService role. Under the WSUS database, select Roles, then right-click webService and select Properties. The FE machine account should be listed as a member of this role. If it is not, it should be added.
In this step, you edit the registry to point WSUS to the destination SQL instance.
- Click Start, click Run, type regedit, and then click OK.
- Find the following key: HKLM\SOFTWARE\Microsoft\UpdateServices\Server\Setup\SqlServerName
- In the Value data box, type [BEName]\[InstanceName], and then click OK. If the instance name is the default instance, then simply type [BEName].
备注
When typing [BEName], do not add the domain name before the name.
- Click Start, point to Programs, point to Administrative Tools, and then click Services.
- Right-click IIS Admin Service, and then click Start.
- Right-click Update Services, and then click Start.
Open the WSUS administrative console (click Start, click Administrative Tools, and then click Microsoft Windows Server Update Services 3.0).
备注
You might need to restart FE in order for these settings to take effect.
For more information about the databases you can use with WSUS, see the following:
- In this guide, see Managing the Databases.
- In Deploying Microsoft Windows Server Update Services, see "Choose the Database Used for WSUS 3.0".
- In Deploying Microsoft Windows Server Update Services, see "Appendix B: Configure Remote SQL" for general information about setting up WSUS using a remote SQL Server 2005 server to host the WSUS database.