Microsoft products such as Windows Server Update Services (WSUS) 3.0 and Windows Sharepoint Services (WSS) 3.0 ship with SQL Server 2005 Embedded Edition. Now called the Windows Internal Database, more and more system administrators charged with managing WSUS and WSS are faced with the challenge of managing these databases. Since most of these system administrators are not full-fledged DBAs, how do they manage the Windows Internal Database?

Solution

The Windows Internal Database is an embedded data service that can only be used by a handful of Windows Services. It is designed in such a way that you are not allowed to connect to and use this particular database service for non-Microsoft products. By default, installing either WSUS 3.0 or WSS 3.0 will create the databases on the C:\ partition and will cause administration issues such as insufficient disk space.

It is quite confusing to manage this SQL Server instance as it does not appear as a SQL Server service nor are there management tools included with the products. The easiest way is to use SQL Server Management Studio Express.

You can install a copy of SQL Server Management Studio Express on the server running your WSUS 3.0 or WSS 3.0. You then register this instance using Named Pipes as this is the only configuration for connectivity. Use this server name when you register this instance:

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

NOTE: you need to access to this instance using, often, your Local Administrator account on your server, not the Domain one, if you have joined your WSUS server in a domain.

Once you've managed to register this instance in SQL Server Management Studio Express, you can now administer the WSUS 3.0 and WSS 3.0 databases like shrinking the database files or moving them to a different disk partition dedicated for them to avoid insufficient disk space issues.

If you prefer to use scripts to manage these databases, you can download and install the SQL Server 2005 Command Line Query Utility - sqlcmd . This tool will be installed, by default, on this directory:

C:\Program Files\Microsoft SQL Server\90\Tools\binn

To connect to the database instance, you need to run the sqlcmd.exe utility, passing the instance name and your credentials:

sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -E

You can then run your administrative T-SQL scripts once connected to this database instance.

General Considerations

While these are possible reasons to administer the Windows Internal Database, it is not recommended to do anything beyond performing backups, moving or shrinking the database files. Modifying database schema or database properties would break supportability of these products.

Range protocol header error

One day, while verifying your WSUS syncronizations, you could see in the Event Log error with ID 364:

Content file download failed. Reason: The server does not support the necessary HTTP protocol. Background Intelligent Transfer Service (BITS) requires that the server support the Range protocol header.

You may experience this problem if a computer is behind a firewall or behind a proxy server. This problem occurs if one of the following conditions is true:

• The proxy server environment does not support the HTTP 1.1 range request feature.
• You are behind a SonicWALL firewall device, and the Enable HTTP Byte-Range request with Gateway AV setting is not enabled for the device.

When you copy a file by using BITS in background mode, the file is copied in multiple small parts. To perform this kind of copy operation, BITS uses the HTTP 1.1 Content-Range header. If you are behind a proxy server or behind a firewall that removes this header, the file copy operation is unsuccessful.

IMPORTANT RESOLUTION NOTE: When BITS copies files in foreground mode, BITS does not use this header.

Solution

The table that we need to access, in the WSUS database, is called tbConfigurationC and the property/column for which we need to change the value is named BitsDownloadPriorityForeground.

Modify the proxy server settings to support HTTP 1.1 range requests. If you cannot modify the proxy server in this manner, configure BITS to work in foreground mode. To do this, follow these steps on your WSUS server:

Click Start, click Run, type one of the following commands, and then click OK.

If you are using WSUS 2.0 with a MSDE or WMSDE database that was created by a default WSUS installation, type the following command:

%programfiles%\Update Services\tools\osql\osql.exe -S %Computername%\WSUS -E -b -n -Q "USE SUSDB update tbConfigurationC set BitsDownloadPriorityForeground=1"

If you configured WSUS 2.0 to use an existing installation of Microsoft SQL Server, type the following command:

%programfiles%\Update Services\tools\osql\osql.exe" -S %Computername% -E -b -n -Q "USE SUSDB update tbConfigurationC set BitsDownloadPriorityForeground=1"

If you are using WSUS 3.0 with a Windows Internal Database that was created by a default WSUS installation, type the following command:

%programfiles%\Update Services\Setup\ExecuteSQL.exe -S %Computername%\MICROSOFT##SSEE -d "SUSDB" -Q "update tbConfigurationC set BitsDownloadPriorityForeground=1"

If you configured WSUS 3.0 to use an existing installation of SQL Server, type the following command:

%programfiles%\Update Services\Setup\ExecuteSQL.exe -S %Computername% -d "SUSDB" -Q "update tbConfigurationC set BitsDownloadPriorityForeground=1"

Restart the Update Services service (or your server). To do this, follow these steps:

• Click Start, click Run, type services.msc, and then click OK.
• In the Services dialog box, right-click Update Services, and then click Restart.