Appendix C: Remote SQL

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

WSUS offers limited support for running database software on a computer that is separate from the computer where the rest of WSUS is installed. This section offers step-by-step instructions for how to install WSUS in this configuration.

Setting up WSUS for remote SQL is a three-step process. First, run WSUS Setup on the front-end computer, which for WSUS is always the server running IIS in the remote SQL pair. Next, go to the back-end computer, which for WSUS is always the server running the database in the remote SQL pair, and then set up the WSUS database and configure permissions. Finally, configure the front-end computer to use the database on the back-end computer, and then manually start the Update Service.

Upgrading to WSUS 2.0 Service Pack 1

Before attempting an upgrade to WSUS SP1, if you have migrated to SQL Server (local or remote) using the steps outlined in the WSUS operations guide, make sure you change the value of the following registry key:

HKLM\Software\Microsoft\Update Services\Server\Setup\WmsdeInstalled

from "1" to "0".

To migrate your WSUS 2.0 installation to Service Pack 1:

1. Run the setup package on the front end with no switches and choose to upgrade.

2. Run the setup package on the back end with no switches and choose to upgrade.

Remote SQL Limitations

  • You cannot use Windows 2000 Server on the front-end computer 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 computer.

  • Both the front-end and the back-end computers must be joined to an Active Directory domain.

Database Requirements

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 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. For more information about what is stored in the WSUS database or how it functions, see Choose the Database Used for WSUS earlier in this guide.

Step 1: Install WSUS on the Front-end Computer

Install WSUS on the front-end computer. This server will need access to the Internet or another WSUS server to obtain updates. You need to prepare this computer with all the prerequisites for a normal WSUS installation, except for database software.

You run WSUS Setup from a command line, using the /f command-line option. This option installs WSUS as the front end of a remote SQL pair, skipping the database setup portion of the WSUS setup process and not starting the Update Service after installation is complete. Database setup is accomplished in Step 2: Install WSUS on the Back-end Computer. Manually starting the Update Service is one of the tasks you accomplish in Step 3: Configure the Front-end Computer to Use the Back-end Computer.

During this installation, be sure to note where you intend to store updates. Where you store updates dictates how you set up the back-end computer in Step 2: Install WSUS on the Back-end Computer.

For more information about hardware, hard disk, and software requirements, see Install the WSUS Server.

To install WSUS on the front-end computer

  1. At the command prompt, navigate to the folder containing the WSUS Setup program, and type:

    WSUSSetup.exe /f

  2. On the Welcome page of the wizard, click Next.

  3. Read the terms of the license agreement carefully, click I accept the terms of the License Agreement, and then click Next.

  4. On the Select Update Source page, you can specify where client computers get updates. If you click Store updates locally, updates are stored in WSUS and you can select a location in the file system to store them. If you do not store updates locally, client computers connect to Microsoft Update to get approved updates.

    To read more about update storage options, see Determine Where to Store Updates earlier in this guide. Make your selections, and click Next.

    If you click Store updates locally, make a note of the string used to identify the location for local update storage. You must use the same string when setting up the back-end computer.

  5. On the Web Site Selection page, you specify the Web site that WSUS uses. For more information, see Install and Configure IIS earlier in this guide. Note two important URLS: the URL to point client computers to WSUS and the URL for the WSUS console where you configure WSUS.

  6. On the Ready to Install Windows Server Update Services page, click Next.

  7. If the final page confirms that WSUS installation was successfully completed, click Finish.

Step 2: Install WSUS on the Back-end Computer

Install WSUS on the back-end computer. This server will need database software installed. You do not need to have IIS installed on the back-end computer. Except for IIS, all other prerequisites for a normal WSUS installation are required.

The steps for configuring permissions on the back-end computer differ, depending upon whether you are using Windows 2000 Server or Windows Server 2003. Both procedures are listed here. Make sure you use the right steps for the operating system you are using.

You must run WSUS Setup from a command line so that you can use command-line options. Use the /b command-line option. In addition to running Setup with a command-line option, you must run a SQL script to identify how update storage is being managed on the front-end computer.

These options install WSUS as the back end of a remote SQL pair, skipping most of the installation except for the database setup portion. After installation is complete, you must configure permissions on the back-end computer.

For more information about hardware, hard disk, and software requirements, see Install the WSUS Server.

Step 2 contains the following procedures:

  • Install WSUS on the back-end computer

  • Run a SQL script to identify how update storage is being managed on the front-end computer

  • Configure permissions on the back-end computer

To install WSUS on the back-end computer

  1. At the command prompt, navigate to the folder containing the WSUS Setup program, and type the following command:

    WSUSSetup.exe /b

  2. On the Welcome page of the wizard, click Next.

  3. Read the terms of the license agreement carefully, click I accept the terms of the License Agreement, and then click Next.

  4. On the Database Options page, you select the software used to manage the WSUS database. You must click Use an existing database server on this computer. Select the instance name from SQL instance name box, and then click Next.

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

  6. On the Mirror Update Settings page, you specify the management role for this WSUS server. If you want a central management topology, or if this is not the first WSUS server on your network, enter the name of the upstream WSUS server here. If this is the first WSUS server on your network or you want a distributed management topology, skip this screen.

    Make your selection, and then click Next. For more information about management roles, see Choose a Management Style.

  7. On the Ready to Install Windows Server Update Services page, click Next.

  8. If the final screen confirms that WSUS installation was successfully completed, click Finish.

Once you install WSUS on the back-end computer, you must run a SQL script to identify how update storage is being managed on the front-end computer. The syntax of the SQL script you run depends on whether client computers are getting updates locally from the front-end computer or are downloading updates directly from Microsoft. You established where clients obtain updates when you installed WSUS on the front-end computer.

If you cannot remember how you configured the front-end computer, you can find out by reading the log file located on the front-end computer here:

%programfiles%\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

  • LocalContentCacheLocation

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

  • At the command prompt, type one of the following commands, depending on how you set up the front-end computer:

    • If you chose local storage on the front-end computer, type:

      "*%programfiles%\*update services\tools\osql\osql.exe" -S SQLServerName -E -b -n -Q "USE SUSDB UPDATE dbo.tbConfigurationA SET HostOnMu = '0' UPDATE dbo.tbConfigurationB SET LocalContentCacheLocation = N'LocalContentCacheLocation Value'"

      whereSQLServerName is the name of the SQL Server instance that holds the SUSDB database; where %programfiles% is the location of the Program Files folder on the front-end computer; and where LocalContentCacheLocation Value is the actual string used to identify where in the file system of the front-end computer to store content, with \WSUSContent appended to the end. For example, if you used C:\WSUS on the front-end computer, you would type C:\WSUS\WSUSContent. Do not use a network location or a UNC path. Do not add a trailing backslash (\).

    • If you chose remote storage on Microsoft Update, type the following:

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

      where SQLServerName is the name of the SQL Server instance on the back-end computer; and where %programfiles% is the location of the Program Files folder on the front-end computer.

Set Permissions on Windows Server 2003

If your back-end computer is running Windows Server 2003, use the following procedure to set up permissions, and then see Step 3: Configure the Front-end Computer to Use the Back-end Computer.

To set permissions on the Windows Server 2003 back-end computer

  1. On the back-end computer, 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 computer, and then click OK.

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

Set Permissions on Windows 2000 Server

If your back-end computer is running Windows 2000 Server, use the following procedures to set up permissions, and then see Step 3: Configure the Front-end Computer to Use the Back-end Computer.

To create a global security group and add the front-end computer as a member

  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 the front-end computer, and then click OK.

  13. In the global security group properties dialog box, click OK.

To set permissions on the Windows 2000 Server back-end computer

  1. On the back-end computer, 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 or Groups, select your domain from the Look in drop-down list.

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

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

Step 3: Configure the Front-end Computer to Use the Back-end Computer

You configure the front-end computer to use the database on the back-end computer by editing the registry. With the registry changed, you manually start the Update Service. You are then ready to configure WSUS using the WSUS console on the front end.

Step 3 contains the following procedures:

  • Configure the front-end computer to use the database.

  • Manually start the Update Service.

To configure the front-end computer to use the database

  1. On the front-end computer, click Start, and then click Run.

  2. In the Open box, type regedit, and then click OK.

  3. In Registry Editor, navigate to the following registry key:

    HKLM\SOFTWARE\Microsoft\Update Services\Server\Setup\

  4. In the details pane, double-click the SQLServerName key.

  5. In Value data, type the name of the back-end computer, and then click OK. When entering data, use the syntax servername\instance. If your server uses the default instance, you can omit the instance name—for example, servername.

To manually start the Update Service

  1. Click Start, and then click Run.

  2. In the Open box, type services.msc, and then click OK.

  3. In the details pane, double-click the Update Services service, and then click Start.