Migrating the Database from MSDE or WMDSE to SQL Server 2000

  • Article

Applies To: Windows Server Update Services

This topic discusses how to migrate the WSUS database (SUSDB) from an MSDE or WMSDE instance to a full version of Microsoft SQL Server 2000.

Reasons To Migrate the WSUS Database To SQL Server 2000

If you chose to use MSDE or WMSDE to host the WSUS database when you set up your WSUS server, and have been running WSUS for some time, you may want to consider upgrading the database engine to a full installation of SQL Server 2000. Using SQL Server 2000 provides:

  • More storage capacity - For example, the MSDE can store a maximum of 2 GB of data. Depending on the types of updates that you synchronize, you might find that 2 GB gets filled up quickly, and you need to frequently manage the space in your database.

  • Ability to administer the WSUS database directly - you can utilize the management capabilities provided by SQL Server 2000 through the Enterprise Manager.

Database Requirements

  • WSUS requires SQL Server 2000 with Service Pack 3a. If you use the full version of SQL Server, the SQL server administrator should first verify that the nested triggers option on the SQL server is turned on. Do this before setting up the WSUS database.

  • You cannot use SQL authentication. WSUS only supports Windows authentication. WSUS Setup creates a database named SUSDB.

  • You can use database software that is 100-percent compatible with Microsoft SQL and not listed in "Remote SQL Limitations.” Microsoft SQL Server 2000 has been tested exclusively for use with remote SQL.

WSUS Database Migration Options

There are two migration options presented in this topic. Select the option that is applicable to your environment, and then follow the procedures in the corresponding section:

  • Migrating from an MSDE or WMSDE instance to SQL Server 2000 running on the same server.

  • Migrating from an MSDE or WMSDE instance to SQL Server 2000 running on another server (remote SQL).

Migrating the WSUS Database From an MSDE or WMSDE Instance To SQL Server 2000 Running On the Same Server

This procedure migrates the WSUS database to a SQL Server 2000 instance running on the same server. Note that there are differences in the procedures if your server is running Windows 2000.

To migrate the WSUS database from an MSDE or WMSDE instance to a SQL Server 2000 instance on the same server

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

  2. In this step you will use SQL Server Enterprise Manager to add the MSDE or WMSDE instance to the SQL Server Group. This enables you to manage the MSDE or WMSDE instance in Enterprise Manager.

    1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

    2. Under the Console Root, expand Microsoft SQL Servers, right-click SQL Server Group and then click New SQL Server Registration.

    3. As you complete the Register SQL Server wizard, on the Select a SQL Server page, under Available Servers, type [ServerName]\WSUS, and then click Add.

    4. Under Connect Using, select The Windows account information I use to log on to my computer (Windows Authentication).

  3. Close any Internet browser that is connecting to the WSUS console.

  4. In this step you will stop the IIS Admin service and the Update Services service.

    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.

  5. In this step you will detach the WSUS database SUSDB from the WMSDE or MSDE instance.

    1. In SQL Server Enterprise Manager, under the [ServerName\WSUS] node, expand Databases.

    2. Right-click SUSDB, point to All Tasks, and then click Detach Database.

    3. Click OK, and when the confirmation dialog boxes appear, click OK again.

  6. In this step you will attach SUSDB to the destination SQL instance. Note that the default instance is (Local)(Windows NT).

    1. Under the instance node, right-click Databases, point to All Tasks, and then click Attach Database.

    2. In the Attach Database box, under MDF file of database to attach, browse to the location of the susdb.mdf file (by default this is C:\Program Files\Microsoft SQL Server\MSSQL$WSUS\Data if you installed the MSDE, and C:\WSUS\MSSQL$WSUS\Data if you installed WMSDE), and then click OK. Note that SUSDB includes both SUSDB.mdf and SUSDB_log.ldf, which is the master log file. SQL will add both for you.

    3. When the confirmation dialog box appears, click OK.

  7. In this step, in the destination SQL instance, you will add two new logins: [ServerName]\ASPNET and NT AUTHORITY\NETWORK SERVICE. Note that the NT AUTHORITY\NETWORK SERVICE login is not required if the WSUS server is running Windows Server 2000.

    1. Under the instance name (for example, (Local)(Windows NT)) click Security, right-click Logins, and then click New Login.

    2. In the SQL Server Login Properties – New Login dialog box, in the Name box, type [ServerName]\ASPNET.

    3. Do not change the default values for Authentication (Windows Authentication) and Domain ([ServerName]).

    4. In the Database box, select SUSDB.

    5. In the Database roles for SUSDB box, under Permit in Database Role, select Public, then select Web Service, and then click OK.

    6. Close Enterprise Manager.

    7. Repeat steps a through f to add the NT AUTHORITY\NETWORK SERVICE account. This step is not required if the WSUS server is running Windows Server 2000.

  8. In this step you will edit the registry to point WSUS to the SQL instance that now holds SUSDB.

    1. On the Start menu, click Run, type Regedit, and then click OK.

    2. Locate the HKLM\SOFTWARE\Microsoft\UpdateServices\Server\Setup\SqlServerName key.

    3. In the Value data box, type [ServerName]\[InstanceName] and then click OK. If the instance name is the default instance, then type [ServerName].

  9. Use Add or Remove Programs in Control Panel to uninstall the Microsoft SQL Server Desktop Engine (WSUS) WMSDE or MSDE instance.

  10. In this step you will start the IIS Admin service and Update Services service.

    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.

  11. In this step you will verify that the database migration has been successful by opening the WSUS console by using Internet Explorer.

    • In Internet Explorer, in the Address box, type https://[ServerName]/WSUSAdmin.

Note

You might have to restart the server for these settings to take effect.

Migrating the WSUS Database From an MSDE or WMSDE Instance To SQL Server 2000 Running On Another Server

This section contains procedures to upgrade the WSUS database (SUSDB) running in a MSDE or WMSDE instance to a SQL Server 2000 instance running on a remote server. By upgrading the WSUS database you are able to manage it using all of the features provided by SQL Server 2000.

At the completion of this section, you will have a WSUS implementation consisting of a front end server through which you access the WSUS console and administer WSUS, and a back end sever running SQL Server 2000, which will host the WSUS database.

Remote SQL Limitations

  • You cannot use Windows 2000 Server on the front end server in a remote SQL pair.

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

  • You cannot use WMSDE or MSDE 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.

About Server 1 and Server 2

Server 1 will be the front end server at the completion of this procedure. Server 2 will be the back end server at the completion of this procedure.

Confirm Server 1 starting configuration

  • Windows Server 2000 or 2003 operating system running WSUS

  • Using MSDE or WMSDE to host the WSUS database.

Confirm Server2 starting configuration

  1. Windows 2000 or 2000 Server operating system running a full version of SQL Server 2000 with SP3 or later.

  2. IIS is optional on the back end server. Except for IIS, all other prerequisites for a WSUS installation are required.

  3. Verify that Nested triggers is turned On.

Install WSUS and Configure the Update Storage Method

This section contains two procedures. The first procedure installs WSUS on the back end server. In the second procedure you will run a SQL script to specify how update storage is managed on the front end server. Depending on whether client computers are getting updates locally from the front end server or are downloading updates directly from Microsoft, you will run the applicable script.

To install WSUS on the back end server

  1. Download the WSUS setup files to the server. At the Run command or at a command line, navigate to the folder containing the WSUS setup files that you downloaded and type wsussetup.exe /b.

  2. Complete the WSUS Setup wizard, selecting the following options:

    1. On the Database Options page, in the Select SQL instance name box, select the SQL Server instance where you want to install the WSUS database. Note that <Default> will be the only instance available if you decide not to create any new instances.

    2. On the Connecting to SQL Server Instance page, wait to be prompted for a successful connection to the SQL server, and then click Next.

To identify how update storage is managed on the front end server and run the applicable script

  1. If you cannot remember how you configured the front end server when you originally installed WSUS, you can find out by using the log file located on the front end server. Type the following at a command prompt:

    %drive%\Program Files\Update Services\Logfiles\WSUSSetup_[InstallDate].log

    where [InstallDate] is the date you installed WSUS.

    Make a note of the value for the keys HostOnMu and LocalContentCacheLocation.

  2. If you chose remote storage on Microsoft Update, skip to the next step. If you chose local storage on the front end server, at a command prompt type

    "[%drive%]\program files\update services\tools\osql\osql.exe" -S [SQLServerName\InstanceName] -E -b -n -Q "USE SUSDB UPDATE dbo.tbConfigurationA SET HostOnMu='0' UPDATE dbo.tbConfigurationB SET LocalContentCacheLocation=N'[LocalContentCacheLocationValue]'"

    Where

    [%drive%]\program files\update services\tools\osql\osql.exe is the default location of the osql.exe tool on Server2.

    [SQLServerName\InstaceName] is the name of the SQL Server instance that holds the SUSDB database on Server2. Note that \InstanceName is not necessary if you are using the default instance.

    [LocalContentCacheLocationValue] is the path to the folder on Server 1 where update files are stored. For example, C:\WSUS\WSUSContent.

Important

Do not use a network location or a UNC path. Do not add a trailing backslash ().

  1. If you chose remote storage on Microsoft Update, at a command prompt, type

    "[%drive%]\program files\update services\tools\osql\osql.exe" -S [SQLServerName\InstanceName] -E -b -n -Q "USE SUSDB UPDATE dbo.tbConfigurationA SET HostOnMu='1' UPDATE dbo.tbConfigurationB SET LocalContentCacheLocation=N'[%Server1Drive]\program files\Update Services\WsusContent'"

    Where

    [%drive%]\program files\update services\tools\osql\osql.exe is the default location of the osql.exe tool on Server2.

    [SQLServerName\InstanceName] is the name of the SQL Server instance on Server2. Note that \InstanceName is not necessary if you are using the default instance.

    [%Server1Drive%]\program files\update services is the location of the Update Services folder on Server1

Set Permissions On the Back End Server

This section contains two procedures: one for the back end server running Windows Server 2003, and the other for the back end server running Windows Server 2000. If your back end server is running Windows 2000 Server, you first create a global security group, add your front end server as a member, and then configure permissions on the back end server. Do the applicable procedure for your back end server.

To set permissions on the back end server running Windows Server 2003

  1. On the Server2, click Start, point to Administrative Tools, and then click Computer Management.

  2. In the tree, expand Local Users and Groups, and then click Groups.

  3. In the details pane, double-click WSUS Administrators.

  4. In the WSUS Administrators Properties dialog box, click Add.

  5. In Select Users, Computers or Groups, click Object Types.

  6. In Object Types, click Computers, and then click OK.

  7. In Select Users, Computers, or Groups, enter the name of the front end server, and then click OK.

  8. In the WSUS Administrators Properties dialog box, click OK.

To set permissions on the back end server running Windows Server 2000

  1. On a computer with Active Directory Administrative Tools installed, click Start, point to Administrative Tools, and then click Active Directory Users and Computers.

  2. In the tree, right-click the folder in which you want to create the new group.

  3. Point to New, and then click Group.

  4. Type the name of the new group.

  5. In Group Scope, click Global.

  6. In Group Type, click Security, and then click OK. A global security group is created.

  7. Double-click the global security group you just created.

  8. In the Group Properties dialog box, click the Members tab.

  9. Click Add.

  10. In Select Users, Contacts, or Computers, click Object Types.

  11. In Object Types, click Computers, and then click OK.

  12. In Enter the Object names to select (examples), enter the name of Server1, and then click OK.

  13. In the Global Security Group Properties dialog box, click OK.

  14. On Server2, click Start, point to Administrative Tools, and then click Computer Management.

  15. In the tree, expand Local Users and Groups, and then click Groups.

  16. In the details pane, double-click WSUS Administrators.

  17. In the WSUS Administrators Properties dialog box, click Add.

  18. In Select Users or Groups, select your domain from the Look in drop-down list.

  19. Double-click the global security group you created in the preceding procedure, and then click OK.

  20. In the WSUS Administrators Properties dialog box, click OK.

Complete the Database Upgrade

In this section you will detach the WSUS database on Server 1 and on Server 2, and install SQL Server 2000 on Server 1. The other procedures in this section complete the upgrade process and validate that the upgrade was done successfully. Do all of the procedures in this section in the order presented.

To detach the WSUS database on Server 2

  1. On Server 2, in SQL Server Enterprise Manager, under the Server2Name\InstanceName node, expand Databases.

  2. Right-click SUSDB, point to All Tasks, and then click Detach Database.

  3. Click OK, and when the confirmation dialog boxes appear, click OK again.

  4. Locate and then rename the existing susdb.mdf and susdb_log.ldf files.

Important

Note where these files are stored in the file system. Later in this section, you will copy files of the same name from Server 1 to this location.

To install SQL Server 2000a and detach the WSUS database on Server 1

  1. On Server 1, install SQL Server 2000a with the Server and Client Tools option and SQL Server 2000a Service Pack 3 or later.

  2. In this step, you will stop the IIS Admin service and the Update Services service, and close any Internet browser that is connecting to the WSUS console.

    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. In this step you will detach the WSUS database

    1. In SQL Server Enterprise Manager, under the [Server1Name]\WSUS node, expand Databases.

    2. Right-click SUSDB, point to All Tasks, and then click Detach Database.

    3. Click OK, and then click OK again when the confirmation dialog boxes appear.

    4. Close Enterprise Manager.

To copy the SUSDB.mdf and SUSDB_log.ldf files from Server1 to Server2

  1. In the procedure “To detach the WSUS database on Server 2” you noted the folder location on Server2 where the SUSDB.mdf and SUSDB_log.ldf files were stored.

  2. Copy both files from Server 1 to this folder on Server 2.

To attach the WSUS database on Server 2 to a SQL Server 2000 instance

  1. Under the [InstanceName] node, right-click Databases, point to All Tasks, and then click Attach Database.

  2. In the Attach Database box, under MDF file of database to attach, browse to the location of the susdb.mdf file , and then click OK. Note that SUSDB includes SUSDB.mdf and SUSDB_log.ldf, which is the master log file. SQL will add both for you.

  3. Click OK again when the confirmation dialog box appears.

To configure the front end computer to use the database on the back end computer

  1. On Server 1, on the Start menu, click Run. Type Regedit, and then click OK.

  2. Double-click the HKLM\SOFTWARE\Microsoft\UpdateServices\Server\Setup\SqlServerName key.

  3. In the Value data box, type [Server2Name]\[InstanceName] and then click OK. If the instance name is the default instance, then type [Server2Name]. Do not add the domain name, for example, [DomainName]\[Server2Name].

To delete the WMSDE or MSDE instance

  • On Server 1 use Add or Remove Programs in Control Panel to uninstall the WMSDE or MSDE instance. The name of the MSDE or WMSDE instance to remove is Microsoft SQL Server Desktop Engine (WSUS).

To start the IIS Admin service and the Update Services service

  1. On 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.

To verify that the database upgrade was successful and to prepare for WSUS SP1.

  1. From any computer in your network, open an Internet Explorer browser and access the WSUS console at https://[Server1 name]\WSUSadmin.

  2. You might need to restart Server1 for the settings to take effect.

  3. Prior to upgrading to WSUS SP1, change the value of the registry key HKLM \Software\Microsoft\Update Services\Server\Setup\WmsdeInstalled from 1 to 0.

See Also

Concepts

Managing the Databases Deploying Microsoft Windows Server Update Services 2.0

Other Resources

Deploying Microsoft Windows Server Update Services