Category Archives: WSUS 3.0

SBS 2011 install and why Windows 2008 R2 SP1 doesn’t get offered

**After installing SBS 2011 I immediately wanted to install Windows 2008 R2 SP1 but instead I got offered 59 other updates and no SP1!  So of course you can just roll with that and eventually after cycles of updates and reboots you’ll get SP1 offered …but I really wanted to lay down SP1 saving time and then build from there.  Why isn’t it being offered though …even if you scan via Windows Updates?  Google didn’t give me any good results on Windows 2008 R2; however, remembering that since the Vista ‘major release’ kernel unification change to align the desktop and server kernel led me to look at Windows 7 information.

The following article identifies three required updates that must be installed in order to get offered W2k8R2 SP1.  Interestingly I was offered the KB2534366 patch (required a reboot) via WSUS but I had to go out to Windows Update to be offered the KB2533552 patch (didn’t require a reboot).  KB2454826 is the latest patch to be added to this list as this KB article has gone through multiple revisions over the years.

You do not have the option of downloading Windows 7 SP1 when you use Windows Update to check for updates

Advertisements

Moving WSUS 3.0 database and content

If ever you wish to move this sizable chunks of data to a different partition, you can do it.  Each data type has its own method for moving.

The WSUS content is moved via the wsusutil command-line utility.  This is something I briefly covered yesterday …or was that this morning?  The days and nights are running together – ughh its only Monday! :O

The database, SUSDB, is a bit more involved being that it runs on SQL or the Windows Internal Database (the default SBS 2003 R2 location).  The general steps are to detach the database from its instance (default is MICROSOFT##SSEE), move the actual files – SUSDB.mdf & SUSDB_log.ldf, and then re-attach these files from their new placement.

Those steps each involve a lot of special knowledge.  Just gaining access to the database can be a challenge and may require using the named pipe path that I’ve blogged on before.  Once there you cannot merely detach the database as it will be in use.  To end these usage tentacles you need to fully stop the W3SVC & WsusService services – World Wide Web Publishing Service & Update Services respectively.  When stopped you should be able to gain the green light from SQL Server Management Studio Express to detach the database.  Once done then you move the files via your method of choice such as xcopy …etc or a simple cut and paste.  After moved, you again use the SSMSE tool to attach the database files.

image

That’s it.

References – different and not the exact same techniques but worthy of noting:

http://msmvps.com/blogs/bradley/archive/2007/08/11/detach-and-reattach.aspx

http://www.smallbizserver.net/Forums/tabid/53/forumid/11/postid/74702/view/topic/Default.aspx

**tip**  Use the search tool of this blog to find related topics previously blogged.

Reference – WSUS 3.0 wsusutil command-line utility

http://technet.microsoft.com/en-us/library/cc708604.aspx

Summary of wsusutil Commands

 

configuressl

Updates the WSUS server registry key after the IIS configuration has changed.

healthmonitoring

Configures health monitoring values in the database. If new values are not specified, the current values are displayed.

export

Part of the export/import process used to synchronize a downstream WSUS without using a network connection.

Exports update metadata to an export package file. You cannot use this parameter to export update files, update approvals, or server settings.

import

The second part of the export/import process.

Imports update metadata to a server from an export package file created on another WSUS server. This synchronizes the destination WSUS server without using a network connection.

movecontent

Changes the file system location where the WSUS server stores update files, and optionally copies any update files from the old location to the new location

listfrontendservers

Lists the front-end servers related to this WSUS server.

deletefrontendserver

Deletes the specified front-end server from the WSUS database.

checkhealth

Checks the health of the WSUS serve. Results will appear in the Application Event log.

reset

Checks that every update metadata row in the database has corresponding update files stored in the file system. If update files are missing or have been corrupted, downloads the update files again.

listinactiveapprovals

Returns a list of update titles with approvals that are in a permanently inactive state because of a change in server language settings.

removeinactiveapprovals

Removes approvals for updates that are in a permanently inactive state because of a change in WSUS server language settings.

usecustomwebsite

Changes the port number used by the WSUS Web services from 80 to 8530 or vice versa.

BITS Peer Caching – many clients only one Windows Update download – WSUS optimization

Inside the WSUS 3.0 SP2 Operations Guide document in Appendix E (p123) I found this very cool nugget.  This applies to WSUS deployments where updates are not downloaded, stored, and distributed locally.

Assuming all the clients are online and scheduled to query and sync for Windows Updates (WSUS, WU, MU) together you could greatly benefit from only downloading a patch once.  For my peers in Australia who pay for bandwidth used this may be quite valuable.

Peer caching

Peer caching is a new feature of BITS 3.0 that allows peers (computers within the same subnet of a network that have the peer caching feature enabled) to share files. If peer caching is enabled on a computer, the Automatic Update agent instructs BITS to make downloaded files available to that computer’s peers as well.

When the files have been downloaded, BITS caches them. When another (peer caching-enabled) computer tries to download the same update, BITS on that computer sends a multicast request to all of that computer’s peers. If one or more of the peers responds to the request, BITS will download the file from the first computer to respond. If the download from the peer fails or take too long, BITS continues the download from the WSUS server or Microsoft Update.

This feature of BITS can optimize the bandwidth used by WSUS in several ways.

1. Peer caching decreases the amount of data transferred from the WSUS server to its clients, because computers in the same subnet will usually download the updates from each other.

2. Peer caching decreases the amount of data transferred across the WAN when some or all of the clients of a WSUS server are located in different locations.

3. Peer caching decreases the amount of data transferred across the Internet if WSUS clients in the same subnet are configured to download updates from Microsoft Update.

Note

BITS peer caching requires computers to be running Windows Vista or Windows Server 2008, and to be part of an Active Directory Domain.

For more information about peer caching and peer servers, see Peer Caching (http://go.microsoft.com/fwlink/?LinkId=79432).

To enable peer caching (on Windows Vista)

1. Start the Group Policy Object Editor (click Start, click Run, and then type gpedit.msc).

2. Expand Computer Configuration, then Administrative Templates, then Network, then Background Intelligent Transfer Service.

3. Enable the Allow BITS Peercaching setting.

4. Enable the Maximum network bandwidth used for Peercaching setting, and set the maximum bandwidth in bits per second (the default is 104857), then click OK.

5. Enable the Limit the BITS Peercache size setting, and set the percentage of disk space to be used for the peer cache (the default is 5 percent), and then click OK.

6. Enable the Limit age of items in the BITs Peercache setting, and set the number of days (the default is 90), and then click OK.

Note

You must be an administrator to perform this procedure.

**author note**  besides modifying the applicable GPO on Vista you could also do this from a Server 2008 server.  Vista is the first client OS to provide domain scope GPO management.

Re-index the WSUS 3.0 Database via a GUI

Combining forces and ideas you can use the scripting provided by TechNet with my blog post on using the SQL Server Management Studio Express (SSMSE 2005) to get this maintenance task done easily.  When done your WSUS database will be healthy and far more responsive.

This can have a significant beneficial impact when you consider how the clients submit sync queries to the WSUS 3.0 server especially at startup.  Be prepared for this script to run from anywhere between 5-30 minutes depending on amount of work needing done to get things to good health. Use at your own risk. Script below is copied from the TechNet source.

TechNet script source and info (recommending reading once before using script):
http://www.microsoft.com/technet/scriptcenter/scripts/sus/server/susvvb01.mspx?mfr=true

 /******************************************************************************
This sample T-SQL script performs basic maintenance tasks on SUSDB
1. Identifies indexes that are fragmented and defragments them. For certain
tables, a fill-factor is set in order to improve insert performance.
Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx
and tailored for SUSDB requirements
2. Updates potentially out-of-date table statistics.
******************************************************************************/

USE SUSDB;
GO
SET NOCOUNT ON;

-- Rebuild or reorganize indexes based on their fragmentation levels
DECLARE @work_to_do TABLE (
objectid int
, indexid int
, pagedensity float
, fragmentation float
, numrows int
)

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @numrows int
DECLARE @density float;
DECLARE @fragmentation float;
DECLARE @command nvarchar(4000);
DECLARE @fillfactorset bit
DECLARE @numpages int

-- Select indexes that need to be defragmented based on the following
-- * Page density is low
-- * External fragmentation is high in relation to index size
PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)
INSERT @work_to_do
SELECT
f.object_id
, index_id
, avg_page_space_used_in_percent
, avg_fragmentation_in_percent
, record_count
FROM
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f
WHERE
(f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count 50 and f.avg_fragmentation_in_percent > 15.0)
or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)

PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))

PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)

SELECT @numpages = sum(ps.used_page_count)
FROM
@work_to_do AS fi
INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

-- Declare the cursor for the list of indexes to be processed.
DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do

-- Open the cursor.
OPEN curIndexes

-- Loop through the indexes
WHILE (1=1)
BEGIN
FETCH NEXT FROM curIndexes
INTO @objectid, @indexid, @density, @fragmentation, @numrows;
IF @@FETCH_STATUS < 0 BREAK;

SELECT
@objectname = QUOTENAME(o.name)
, @schemaname = QUOTENAME(s.name)
FROM
sys.objects AS o
INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE
o.object_id = @objectid;

SELECT
@indexname = QUOTENAME(name)
, @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END
FROM
sys.indexes
WHERE
object_id = @objectid AND index_id = @indexid;

IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation = 5000 AND @fillfactorset = 0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
ELSE
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;
EXEC (@command);
PRINT convert(nvarchar, getdate(), 121) + N' Done.';
END

-- Close and deallocate the cursor.
CLOSE curIndexes;
DEALLOCATE curIndexes;

IF EXISTS (SELECT * FROM @work_to_do)
BEGIN
PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20))
SELECT @numpages = @numpages - sum(ps.used_page_count)
FROM
@work_to_do AS fi
INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20))
END
GO

--Update all statistics
PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)
EXEC sp_updatestats
PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)
GO

Blog on Connected to Windows Internal Database with SSMSE via named pipes:
https://duitwithsbs.wordpress.com/2008/09/01/getting-access-into-windows-internal-database-instance/

Another helpful tool is to identify which version of SQL your database actually is.  Is it SQL or Windows Internal Database?
https://duitwithsbs.wordpress.com/2008/07/17/what-sql-version-is-it/