Home
.. About WSUS Wiki

WSUS
.. WSUS FAQ
.. WSUS on SBS
.. WSUS Troubleshooting
.. WSUS News Groups
.. Known WSUS Issues
.. WSUS Links
.. WSUS Wish List

WSUS Documents
.. WSUS Deployment Guide
.. WSUS Installation Guide
.. WSUS Release Notes
.. WSUS Best Practice

SUS
.. SUS FAQ
.. What Is SUS
.. SUS Troubleshooting
.. SUS Links
.. SUS Known Issues
.. SUS FAQ
.. What Is SUS
.. SUS Troubleshooting
.. SUS Links
.. SUS Known Issues

Wiki Community
.. Wiki Contributors
.. I Love WSUS
.. WSUS Wiki Diary
.. Wiki Statistics
.. To Do Page

Miscellaneous Stuff
.. Other Resources
.. Do You Know?

Migrating the database from MSDE or WMDSE to SQL Server 2000

Note: The following is DRAFT content for the Microsoft WSUS Operations Guide. Check the WSUS Ops guide (http://go.microsoft.com/fwlink/?LinkID=42188) for the final version in a couple of weeks. The title of the topic will remain the same in the ops guide.

Introduction

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

Reasons to migrate the WSUS database to SQL Server 2000

If you chose to use the MSDE or WMSDE to host the WSUS database when you set up your WSUS server and have been running WSUS for some time, you might be considering upgrading the database engine to a full installation of SQL Server 2000. Using SQL Server 2000 can provide the following:

· More storage capacity - For example, the MSDE can store a maximum of 2 GB of data. Depending on the types of updates you synchronize regularly, you might find that the 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.

Scenarios

The following scenarios are presented in this topic:

· Migrating the MSDE or WMSDE to SQL Server 2000 running on the same server

· Migrating the MSDE or WMSDE to SQL Server 2000 running on another server (remote SQL)

Migrating the WSUS database from a MSDE or WMSDE instance to SQL Server 2000 instance 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 might be some differences in the procedures if your server is running Windows 2000, which are noted within the procedure.

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 SQL Server 2000a Service Pack 3 or higher on your WSUS server.

2. In SQL Server Enterprise Manger, add the MSDE or WMSDE instance to the SQL Server Group. This enables you to manage the MSDE or WMSDE instance in Enterprise Manager:

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

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

c. Complete the Register SQL Server wizard, choosing the following options when prompted:

· In the Select a SQL Server page, under Available Servers, type: [ServerName]\WSUS, and then click Add.

· 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. Stop the IIS Admin service and the Update Services service:

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

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

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

5. Detach the WSUS database (SUSDB) from the WMSDE or MSDE instance

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

b. Right-click SUSDB, point to All Tasks, and then click Detach Database. Click OK, and then click OK, when the confirmation dialog boxes appear.

6. Attach SUSDB to the destination SQL instance. Note that the default instance is (Local)(Windows NT).

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

b. 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.) Click OK again when the confirmation dialog box appears.

7. In the destination SQL instance, add two new logins: [ServerName]\ASPNET and NT AUTHORITY\NETWORK SERVICE (the NT AUTHORITY\NETWORK SERVICE login is not required if the WSUS server is running Windows 2000 Server).

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

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

c. Leave the defaults for Authentication (Windows Authentication) and Domain ([ServerName]).

d. In the Database box, select SUSDB.

e. In the Database roles for SUSDB box, under Permit in Database Role, select public, and select webService, and then click OK.

f. Close Enterprise Manager.

g. Repeat steps a through f to add the NT AUTHORITY\NETWORK SERVICE account. Note: this is not required if the WSUS server is running Windows Server 2000.

8. Edit the registry to point WSUS to the SQL instance that now holds SUSDB.

a. Click Start, click Run, type: regedit, and then click OK.

b. Find the following key: HKLM\SOFTWARE\Microsoft\UpdateServices\Server\Setup\SqlServerName, and in the Value data box, type: [ServerName]\[InstanceName] and then click OK. If the instance name is the default instance, then simply type: [ServerName].

9. 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).

10. Open Services and then start the IIS Admin service and Update Services service.

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

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

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

11. Verify that the database migration has been successful by opening the WSUS console from Internet Explorer (in the Address box, type: http://[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 a SQL Server 2000 instance on another server

The goal of this scenario is to take the WSUS database (SUSDB) running in a MSDE or WMSDE instance (which you can choose to install during WSUS Setup) and to move and upgrade it 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 scenario, 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 Scenario 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 this procedure

There are 11 steps that make up this procedure. Some of the steps require a number of sub-procedures.

For ease of explanation, Server1 and Server2 are used to indicate the front-end and back-end servers respectively. Note that in each step, where appropriate, it is noted on which server you must perform the procedures. More information about Server1 and Server2:

Server1

· This will the front-end server at the completion of this scenario.

· Starting configuration: Windows Server 2000 or 2003 operating system running WSUS using MSDE or WMSDE to host the WSUS database.

Server2

· This will be the back-end server at the completion of this scenario.

· Starting configuration: Windows 2000 or 2000 Server operating system running full version of SQL Server 2000 sp3 or higher. Note: Verify that nested triggers are turned on.

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

Step 1 [On Server2]: Install WSUS with the /b option.

Completion of this step requires the following procedures:

· Install WSUS on the back-end server

· Identify how update storage is being managed on the front-end server
· Set permissions on the back-end server

Notes:

· You must run WSUS Setup from a command line so that you can use command-line options. Use the /b command-line option.

· You do not need to have IIS installed on the back-end server. Except for IIS, all other prerequisites for a normal WSUS installation are required.

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 you downloaded and type the following command:

wsussetup.exe /b

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

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

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

To identify how update storage is being managed on the front-end server

You must run a SQL script to identify how update storage is being 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 use one of the following scripts. Note that you established where clients obtain updates when you originally installed WSUS on the front-end server.

If you cannot remember how you configured the front-end server, you can find out by reading the log file located on the front-end server by typing the following at a command prompt:

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

Where [InstallDate] is the date you installed WSUS. From this file you need the value for two keys: HostOnMu, and LocalContentCacheLocation.

· 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 Server1 where update files are stored. For example, C:\WSUS\WSUSContent.

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

· 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

To set permissions on the back-end server

·         If the back-end server is running Windows Server 2003:
a.      On the Server2, click Start, point to Administrative Tools, and then click Computer Management.
b.      In the tree, expand Local Users and Groups, and then click Groups.
c.      In the details pane, double-click WSUS Administrators.
d.      In the WSUS Administrators Properties dialog box, click Add.
e.      In Select Users, Computers or Groups, click Object Types.
f.       In Object Types, click Computers, and then click OK.
g.      In Select Users, Computers, or Groups, enter the name of the front-end server, and then click OK.
h.      In the WSUS Administrators Properties dialog box, click OK.

· If the back-end server is running Windows 2000 Server
This procedure has two parts--if Server2 is running Windows 2000 Server, you must first create a global security group and add Server1 as a member, then configure permissions on Server2.

Part 1: Create a global security group and add the front-end server as a member
a.      On a computer with Active Directory Administrative Tools installed, click Start, point to Administrative Tools, and then click Active Directory Users and Computers.
b.      In the tree, right-click the folder in which you want to create the new group.
c.      Point to New, and then click Group.
d.      Type the name of the new group.
e.      In Group Scope, click Global.
f.       In Group Type, click Security, and then click OK. A global security group is created.
g.      Double-click the global security group you just created.
h.      In the group properties dialog box, click the Members tab.
i.        Click Add.
j.       In Select Users, Contacts, or Computers, click Object Types.
k.      In Object Types, click Computers, and then click OK.
l.        In Enter the Object names to select (examples), enter the name of Server1, and then click OK.
m.   In the global security group properties dialog box, click OK.

Part 2: Set permissions on the Windows 2000 Server back-end server
n.      On Server2, click Start, point to Administrative Tools, and then click Computer Management.
o.      In the tree, expand Local Users and Groups, and then click Groups.
p.      In the details pane, double-click WSUS Administrators.
q.      In the WSUS Administrators Properties dialog box, click Add.
r.       In Select Users or Groups, select your domain from the Look in drop-down list.
s.      Double-click the global security group you created in the preceding procedure, and then click OK.
t.       In the WSUS Administrators Properties dialog box, click OK.

Step 2 [On Server2]: Detach the WSUS database (SUSDB).

1. 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. Click OK, and then click OK, when the confirmation dialog boxes appear.

3. Locate and then rename the existing susdb.mdf and susdb_log.ldf files. It is important to note where they are stored in the file system. You will be copying the files of the same name from Server1 later in this process to the same location.

Step 3 [On Server1]: Install Microsoft SQL Server 2000 with "Client Tools Only" option.

Step 4 [On Server1]: 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.

Step 5 [On Server1]: 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. Click OK, and then click OK, when the confirmation dialog boxes appear.

3. Close Enterprise Manager.

Step 6: Copy the SUSDB.mdf and SUSDB_log.ldf files from Server1 to Server2.

· In Step 2, you noted the folder location on Server2 where these files are stored. Copy the files to this folder on Server2.

Step 7 [On Server2]: Attach the WSUS database 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 both SUSDB.mdf and SUSDB_log.ldf, which is the master log file. SQL will add both for you.) Click OK again when the confirmation dialog box appears.

Step 8 [On Server1]: Configure the front end computer to use the database on the backend computer.

In this step, you edit the registry to point WSUS to destination SQL instance.

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

2. Find and double-click the following key:

HKLM\SOFTWARE\Microsoft\UpdateServices\Server\Setup\SqlServerName

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

Note When entering [Server2Name], do not add the domain name, such as [DomainName]\[Server2Name].

Step 9 [On Server1]: Delete the WMSDE or MSDE instance.

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

Step 10 [On Server1]: Start the IIS Admin service and the Update Services service.