House of Cards–The ConfigMgr Software Update Point and WSUS

A Card house; fun to build but not very solid and when one card falls the whole house often goes down with it. It’s a little like that with the WSUS server and Configuration Manager. Installing WSUS seems so easy but there are still some moving part, and if you get one of the wrong maybe the whole House of Cards falls. Recently I have seen that happen at several customers. This blog post is divided into 3 parts:

1. Introduction

2. Problem overview and symptoms

3. Solutions to fix issues and avoid it in the future

Let’s start by looking at some of the cards in the game:

• The WSUS IIS App pool: Without this running nothing is really working and clients are unable to perform a successful scan.
• The WSUS database: You need to ensure that you manage the database and perform some weekly/monthly maintenance like running a custom defragmentation and re-indexing job. Not doing this can result if poor performance and also cause a timeout when trying to run the WSUS Server Cleanup Utility.
• The WSUS Server Cleanup utility: Removing unused and obsolete updates from the database helps keeping the overall size of the catalog down to a minimum. New clients will have to download the catalog (and also clients falling over from one Software Update Point to another), and size matters when dealing with slow WAN links and thousands of clients.

What can cause the House of Cards to a fall?

Couple of weeks ago I had three different customers experiencing identical issues almost at the same time causing the House of Cards to a fall. The problem started patch Tuesday, one customer noticed a high utilization on the MPLS line in the main Datacenter caused by the Software Update Point. At almost the same time the WSUS IIS App pool chrased and stopped. Starting the IIS pool up again would once again flood the MPLS line, run for a few minutes and crash again. Other customers said the Windows Update agent didn’t perform a successful scan and reported these errors in the wuahandler.log

OnSearchComplete – Failed to end search job. Error = 0x80244022.
Scan failed with error = 0x8024402

In common for all customers was that the WSUS App pool stopped. When starting the IIS WSUS App pool back, most clients downloaded 5-10 MB. from the software Update Point. Clients looked to be downloading the full catalog from the SUP which was the reason why one customer saw the high utilization of their MPLS lines. 

So why does the WSUS App pool crash and how to fix it?

The WSUS App pool “private memory limit” setting is by default configured to 1,7 GB, the pool  crashed because it couldn’t keep up. In order to fix that, we increased the memory on the Software Update Point and also configure the WSUS App pool memory limit with 8-12 GB. After that we started the App pool and let the clients flood the network for a couple of hours and everything was back to normal. So why couldn’t the WSUS App pool keep up? This is due to the larger number of updates in the catalog which continues to grow over time.  After the Patch Tuesday sync from Microsoft, clients started scanning successfully at first, but the Update catalog reached a size that increased the load on the Software Update Point. Due to the default memory limit of the app pool the server ran out of memory causing the WSUS App pool card to fail and took the House of Cards down with it.

Maintaining the database

Maintaining the susdb database is as important as maintaining the ConfigMgr database meaning that you should run a weekly maintenance task that performs a defragmentation of the database. For this you can run the “Ola Hallengren” defrag solution as described here by Steve Thompson or you can use this script from Microsoft MSDN. Running the script (either of them) for the first time can easily take hours all depending on the number of updates in the database and the server specs.

The WSUS Server Cleanup Utility

Running a WSUS cleanup can be initiated from the WSUS GUI or using PowerShell. Needless to say that PowerShell is the preferred method. Below is an example where I invoke WSUSSeverCleanup to cleanup obsolete updates along with a few other tasks.

Get-WsusServer -Name cm01 -PortNumber 8530

Get-WsusServer | Invoke-WsusServerCleanup –CleanupObsoleteUpdates -CleanupUnneededContentFiles -CompressUpdates -DeclineExpiredUpdates -DeclineSupersededUpdates

Above step will usually run just fine and will tell you how many objects were removed. However if you have had your WSUS server running for a long period and never performed any cleanup before, you will likely run into this time-out error:

Running the WSUS Server Cleanup tool using the GUI will give throw you a connection error like this:

Looking at the underlying error you will see this:

The WSUS administration console was unable to connect to the WSUS Server via the remote API.

Verify that the Update Services service, IIS and SQL are running on the server. If the problem persists, try restarting IIS, SQL, and the Update Services Service.

System.Net.Sockets.SocketException — An existing connection was forcibly closed by the remote host

Source
System.Windows.Forms

Stack Trace:
   at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
   at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
   at Microsoft.UpdateServices.UI.SnapIn.Wizards.ServerCleanup.ServerCleanupWizard.OnCleanupComplete(Object sender, PerformCleanupCompletedEventArgs e)

What we are seeing is a SQL stored procedure timeout error. There can several reasons why this happens, database is not indexed, not enough resources etc. A few things you can try to solve this issue is adding more memory, defragment and re-index the database and/or connect to SUSDB in SQL Management Studio and delete the obsolete updates.

I have seen time-out issues so many times at customer who has never performed any past cleanup. Sometimes I can fix the issue by running the database defragmentation job but most often I have to delete the obsolete software update directly in the susdb.

Before deleting any updates first run this stored procure exec spGetObsoleteUpdatesToCleanup. The stored procedure will return the update id of each obsolete update in the database. I have seen any number from 100 to 8,000. What I have learned is that there is no magic limit to when the server will time-out. In order to delete the updates you need to run this store procedure exec spDeleteUpdate @localUpdateID=<50697> where <50697> is the update id. You can choose to delete the updates one by one, or use the script below to delete x-number of updates at the time (thanks for Henrik Rading for helping out). Simply change the number in the SELECT TOP (1000) statement to any number you want to delete. I will warn you, it can easily take several hours to delete the updates and while the process is ongoing you might see WSUS synchronization errors.

USE SUSDB
GO
IF object_id(‘tempdb..#MyTempTable’) is not null  DROP TABLE #MyTempTable
GO
IF (SELECT CURSOR_STATUS(‘global’,’myCursor’)) >= -1
BEGIN
DEALLOCATE myCursor
END
GO
sp_configure ‘Show Advanced Options’, 1
GO
RECONFIGURE
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
RECONFIGURE
GO

SELECT TOP (2000) * INTO #MyTempTable
    FROM OPENROWSET(‘SQLNCLI’, ‘Server=(local);Trusted_Connection=yes;’, ‘EXEC susdb.dbo.spGetObsoleteUpdatesToCleanup’)

DECLARE myCursor CURSOR FOR
SELECT LocalUpdateID FROM #MyTempTable

DECLARE @x INT
DECLARE @Msg VARCHAR(50)
DECLARE @Count INT
SELECT @Count = COUNT(*) FROM #MyTempTable

SELECT @msg = ‘Number of updates to be deleted:’ +  CAST( @Count AS VARCHAR(10))
RAISERROR(@msg, 0, 1) WITH NOWAIT

OPEN myCursor
FETCH NEXT FROM myCursor INTO @x

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @msg = ‘Deleting update with ID:’ + CAST (@x AS VARCHAR(10))
    RAISERROR(@msg, 0, 1) WITH NOWAIT
    EXEC spDeleteUpdate @localUpdateID=@x
   
    FETCH NEXT FROM myCursor INTO @x
END
CLOSE myCursor;
DEALLOCATE myCursor;
DROP TABLE #MyTempTable;
SELECT @msg = ‘Deletion completed’
    RAISERROR(@msg, 0, 1) WITH NOWAIT

How to avoid the House of Cards to Fall

Hopefully by now you have been convinced that it’s required to combine a defragmentation/re-indexing job with running the WSUS Server Cleanup Utility. Personally I run the defragmentation job once a week and after that I run the WSUS Server Cleanup job. The WSUS Server Cleanup process is a PowerShell script from Kaido Järvemets, that will run the WSUS cleanup and mail a nice HTML report with the status of the job. These are the steps you need to perform:

1. Modify this script with your SMTP information and save the script:
Add-Type -Path "C:\Program Files\Update Services\API\Microsoft.UpdateServices.Administration.dll"
$UseSSL = $False
$PortNumber = 8530
$Server = "cm02"
$ReportLocation = "E:\Install\WSUS\wsus\WSUS_CleanUpTaskReport.html"
$SMTPServer = "smtp.viamonstra.com”
$SMTPPort = 25
$To = "Kent Agerlund <kent.agerlund@viamonstra.com>"
$From = "ConfigMgr <configmgr@viamonstra.com>"
$WSUSConnection = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer($Server,$UseSSL,$PortNumber)
#Clean Up Scope
$CleanupScopeObject = New-Object Microsoft.UpdateServices.Administration.CleanupScope
$CleanupScopeObject.CleanupObsoleteComputers = $True
$CleanupScopeObject.CleanupObsoleteUpdates = $True
$CleanupScopeObject.CleanupUnneededContentFiles = $True
$CleanupScopeObject.CompressUpdates = $True
$CleanupScopeObject.DeclineExpiredUpdates = $True
$CleanupScopeObject.DeclineSupersededUpdates = $True
$CleanupTASK = $WSUSConnection.GetCleanupManager()
$Results = $CleanupTASK.PerformCleanup($CleanupScopeObject)
$DObject = New-Object PSObject
$DObject | Add-Member -MemberType NoteProperty -Name "SupersededUpdatesDeclined" -Value $Results.SupersededUpdatesDeclined
$DObject | Add-Member -MemberType NoteProperty -Name "ExpiredUpdatesDeclined" -Value $Results.ExpiredUpdatesDeclined
$DObject | Add-Member -MemberType NoteProperty -Name "ObsoleteUpdatesDeleted" -Value $Results.ObsoleteUpdatesDeleted
$DObject | Add-Member -MemberType NoteProperty -Name "UpdatesCompressed" -Value $Results.UpdatesCompressed
$DObject | Add-Member -MemberType NoteProperty -Name "ObsoleteComputersDeleted" -Value $Results.ObsoleteComputersDeleted
$DObject | Add-Member -MemberType NoteProperty -Name "DiskSpaceFreed" -Value $Results.DiskSpaceFreed
#HTML style
$HeadStyle = ""
$Date = Get-Date
$DObject | ConvertTo-Html -Head $HeadStyle -Body "
$($ENV:ComputerName) WSUS Report: $date
" | Out-File $ReportLocation -Force
Send-MailMessage -To $To -from $FROM -subject "WSUS Clean Up Report" -smtpServer $SMTPServer -Attachments $ReportLocation -Port $SMTPPort
2. Create a scheduled task in Windows that will run the script once a week with a script like this (notice this is tested on Server 2012 R2). You need to modify the –File to the location of the PowerShell script you created above.
$A = New-ScheduledTaskAction -Execute "PowerShell.exe" -Argument ‘-ExecutionPolicy ByPass -File F:\Install\scripts\wsus\Invoke-CleanUPWSUSTASK.PS1’
$T = New-ScheduledTaskTrigger -WeeksInterval 1 -DaysOfWeek Sunday -At "01:00" -Weekly
Register-ScheduledTask -User "NT AUTHORITY\SYSTEM" -TaskName ‘WSUS Clean UP TASK’ -Trigger $T -Action $A
3. From now on, once a week you will receive a nice looking HTML report with information about what has been done
   

link from : http://blog.coretech.dk/kea/house-of-cardsthe-configmgr-software-update-point-and-wsus/