Migrating from Windows Internal Database to SQL Server 2005

  • Article

Applies To: Windows Server 2003 with SP2, Windows Server Update Services

This topic explains how to migrate the Windows Server Update Services (WSUS) database from a Windows Internal Database instance (installed by default during WSUS setup) to a full version of Microsoft SQL Server 2005.

Why you should migrate the WSUS database to 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 lets you administer the WSUS database through the SQL Server Management Studio.

SQL Server 2005 database requirements

  • 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.

Scenarios

The topic presents the following scenarios:

  • Migrating the Windows Internal Database database to an instance of SQL Server 2005 that is running on the WSUS server

  • Migrating the Windows Internal Database database to an instance of SQL Server 2005 that is running on another server (remote SQL)

Warning

The procedures in this document use Registry Editor. Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require you to reinstall the operating system. Microsoft cannot guarantee that these problems can be resolved. Modify the registry at your own risk. Before you edit the registry, export the keys in the registry that you plan to edit, or back up the whole registry. If a problem occurs, you can then restore the registry to its previous state.

Migrating the WSUS database from a Windows Internal Database instance to an instance of SQL Server 2005 running on the WSUS server

Use the following steps to migrate the WSUS database from a Windows Internal Database instance to an instance of SQL Server 2005.

  1. Install SQL Server 2005 with the Server and Client Tools option and SQL Server 2005 Service Pack 1 or a later version on your WSUS server.

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

  3. Right-click IIS Admin Service, and then click Stop.

  4. Right-click Update Services, and then click Stop.

  5. Run the following SQL command to detach the WSUS database (SUSDB) from the Windows Internal Database instance, by using the sqlcmd utility. This utility 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

  6. In SQL Server Management Studio, under the instance node, right-click Databases, select Properties, and then click Attach.

  7. In the Attach Databases box, under Databases to attach, locate the susdb.mdf file (by default, this is C:\WSUS\UpdateServicesDbFiles if you installed Windows Internal Database), and then click OK.

  8. In this step, you will verify that NT AUTHORITY\NETWORK SERVICE has login permissions to the instance of SQL Server and to the WSUS database. If it does not, you will have to add it to both locations. This account should also be a member of the webService role on the WSUS database.

    • To verify permissions on the instance of SQL Server, in SQL Server Management Studio, open the instance and select Security, and then Logins. The NT AUTHORITY\NETWORK SERVICE account should be listed as a login. If it is not, it should be added.

    • To 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.

    • To verify members of the webService role, under the WSUS database, select Roles, right-click webService, and then 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.

  9. In this step, you will edit the registry to both point WSUS to the instance of SQL server that now holds the WSUS database and recognize the new database for future WSUS updates. If you have not already done this, export the keys in the registry that you plan to edit, or back up the whole registry.

    1. Click Start, click Run, type regedit, and then click OK.

    2. 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, type [ServerName].

    3. Find the following key: HKLM\Software\Microsoft\Update Services\Server\Setup\wYukonInstalled. In the Value box, type 0, and then click OK.

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

  11. Right-click IIS Admin Service, and then click Start.

  12. Right-click Update Services, and then click Start.

  13. Verify that the database migration was successful by opening the WSUS administrative console. (Click Start, click Administrative Tools, and then click Microsoft Windows Server Update Services 3.0.)

Note

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 running in a Windows Internal Database instance on the WSUS server and move and upgrade it to an instance of SQL Server 2005 that is running on a remote server. Only a full SQL Server 2005 database may be used in a remote SQL installation. Each step, where appropriate, indicates the server on which you perform the procedure.

Remote SQL scenario limitations

  • You cannot use a server configured as a domain controller for either the front end or the back end of the remote SQL server pair.

  • You cannot use a server that is running as a Terminal Services server for the front end of the remote SQL server 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 directory service domain.

Prerequisites

Front end server starting configuration:

  • Windows Server 2003 Service Pack 1 or Windows Server 2008 operating system

  • WSUS with Windows Internal Database

Back end server starting configuration:

  • Windows Server 2003 Service Pack 1 or Windows Server 2008 operating system

  • SQL Server 2005

To migrate the WSUS database from a Windows Internal Database instance to an instance of SQL Server 2005 on a remote server

  1. On the front end server: Install Microsoft SQL Server 2005 with the Server and Client Tools option and SQL Server 2005 Service Pack 1 or a later version. This step will enable you to use the SQL Server Enterprise Manager on the front end server.

  2. On the front end server:

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

    2. Right-click IIS Admin Service, and then click Stop.

    3. Right-click Update Services, and then click Stop.

  3. On the front end server: Run the following SQL command to detach the WSUS database by using the sqlcmd utility. The utility 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

  4. On the front end server: copy the SUSDB.mdf and SUSDB_log.ldf files from the front end server to the back end server.

  5. On the back end server:

    1. To attach SUSDB to the destination instance of SQL server, under the instance node, right-click Databases, select Properties, and then click Attach.

    2. In the Attach Databases box, under Databases to attach, locate the susdb.mdf file (by default this is C:\WSUS\UpdateServicesDbFiles if you installed Windows Internal Database), and then click OK.

  6. On the back end server:

    • To verify permissions on the instance of SQL Server, in SQL Server Management Studio, open the instance ,select Security, and then Logins. The front end server machine account should be listed as a login. If it is not, it should be added.

    • To verify permissions on the database, right-click the database, select Properties, and then click Permissions. The front end server machine account should be listed as a login. If the server account is not listed, it should be added.

    • To verify members of the webService role, under the WSUS database, select Roles, right-click webService, and then select Properties. The front end server machine account should be listed as a member of this role. If the server account is not listed, it should be added.

  7. On the front end server: In this step, you will edit the registry to point WSUS to the destination instance of SQL and to recognize the new database for future WSUS updates. If you have not already done so, export the keys in the registry that you plan to edit, or back up the whole registry.

    1. Click Start, click Run, type regedit, and then click OK.

    2. 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, type [BEName].

Note

When typing [BEName], do not add the domain name before the name. 

3.  Find the following key: **HKLM\\Software\\Microsoft\\Update Services\\Server\\Setup\\wYukonInstalled**. In the **Value** box, type **0**, and then click **OK**.

  1. On the front end server:

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

    2. Right-click IIS Admin Service, and then click Start.

    3. Right-click Update Services, and then click Start.

  2. On the front end server: Verify that the database migration was successful by opening the WSUS administrative console. (Click Start, click Administrative Tools, and then click Microsoft Windows Server Update Services 3.0).

Note

You might have to restart the front end server in order for these settings to take effect. 

For more information about the databases that you can use with WSUS, see the following:


  - In this guide, see [Managing the Databases](cc708600\(v=ws.10\).md).

  - In [Deploying Microsoft Windows Server Update Services](https://go.microsoft.com/fwlink/?linkid=79983), see "Choose the Database Used for WSUS 3.0."

  - In [Deploying Microsoft Windows Server Update Services](https://go.microsoft.com/fwlink/?linkid=79983), see "Appendix B: Configure Remote SQL" for general information about how to set up WSUS by using a remote SQL Server 2005 server to host the WSUS database.