This post is a part of my series on Patching SQL Server AG using SCCM and Powershell
Please read this post for the complete overview

To check if there are pending updates through the SCCM client using Powershell can be very straightforward by just using the get-WMIObject cmdlet:

#Getting Pending Updates through WMI

$Server='YourServername'

Get-WmiObject -Namespace "root\ccm\clientSDK" -Class CCM_SoftwareUpdate -ComputerName $Server | Where-Object { ($_.EvaluationState -like "*$($AppEvalState0)*" -or $_.EvaluationState -like "*$($AppEvalState1)*")}

We are only interested in Updates that are actually ready to install. These will have one of the following states:
ciJobStateNone ($AppEvalState0) or
ciJobStateAvailable ($AppEvalState1)

For a complete list of other states, see this page

But sometimes, the SCCM-admins approve stuff that needs a little bit more attention, that you – as a DBA- want to have more control of (think of CU’s for SQL Server or VMWareTools that can cause network connectivity issues during install – yes, this happenend to me) and you want to prevent that from installing automatically.

The Blacklist

If you want to prevent something from installing automatically try using some kind of Blacklist functionality. A place where you keep track of “forbidden” KBNumbers that cannot be installed unless you approve them.

To use this, all you have to do is filter the list of updates which is easy, because the WMI Call returns the pending updates including an ArticleID, which corresponds to the KB number (but without ‘KB’).

In the next example we get the contents of a .txt file (with a KBNumber per line) and then we filter the list.
we also want to send an email to the SCCM team to warn them when “blacklisted Articles” are found. We then continue getting the Pending Updates but without the blacklisted items:

$Server='ServerName'
$list= get-content 'c:\temp\Blacklist.txt'

#Get the list of KB numbers that are not allowed to be installed
$PendingBlacklistUpdates= (Get-WmiObject -Namespace "root\ccm\clientSDK" -Class CCM_SoftwareUpdate -ComputerName $Server | Where-Object { ($_.EvaluationState -like "*$($AppEvalState0)*" -or $_.EvaluationState -like "*$($AppEvalState1)*") -and ($_.ArticleID -in $list)})

IF($PendingBlacklistUpdates){
     
   Write-warning "Blacklisted Updates ready to be installed found...Sending warning email to SCCM Team"  
               
   $subject='Blacklisted Updates found on server: ' + $server
   $sendFrom='emailadress here'
   $sendTo='emailaddress here'
   $smtpServer='smtpServer here'
   
   Send-MailMessage -From $sendFrom -To $sendTo -Subject  $subject -SmtpServer $smtpServer 
}  

#Continue getting the updates but filter out the blacklist items
$PendingUpdateList = (Get-WmiObject -Namespace "root\ccm\clientSDK" -Class CCM_SoftwareUpdate -ComputerName $Server | Where-Object { ($_.EvaluationState -like "*$($AppEvalState0)*" -or $_.EvaluationState -like "*$($AppEvalState1)*") -and ($_.ArticleID -notin $list)})

Installing the updates

Now that we figured out how to get the updates we want we can now install them using the invoke-WMIMethod cmdlet:

Invoke-WmiMethod -ComputerName $Server -Class CCM_SoftwareUpdatesManager -Name InstallUpdates -ArgumentList (, $PendingUpdateList) -Namespace root\ccm\clientsdk | Out-Null

This will kick the SCCM client and the updates will be installed. During the installations you will have to wait for the intallations to finish:

#Invoking WMI method takes some time to kick in
    Start-Sleep -Seconds 60
        
    #Wait till all patches are installed
    $Result = $true
    while ($Result -eq $true) {
        $CCMUpdate = get-wmiobject -query "SELECT * FROM CCM_SoftwareUpdate" -namespace "ROOT\ccm\ClientSDK" -ComputerName $Server
        $Result = if (@($CCMUpdate | where-object { $_.EvaluationState -eq 2 -or $_.EvaluationState -eq 3 -or $_.EvaluationState -eq 4 -or $_.EvaluationState -eq 5 -or $_.EvaluationState -eq 6 -or $_.EvaluationState -eq 7 -or $_.EvaluationState -eq 11 }).length -ne 0) { $true } else { $false }  
       
    }

I hope my guide on Installing Pending updates through SCCM client using Powershell was helpful—feel free to leave questions in the comments!

If the task still seems a bit daunting, book a call with Data Masterminds. We have the expertise to execute this and other SQL Server management and troubleshooting tasks to save you the headache.

This post is a part of my series on Patching SQL Server AG using SCCM and Powershell
Please read this post for the complete overview

Checking the health of your Always On Availability Groups should always be included in your patching scripts, especially when failovers or even multiple reboots are required. You should only continue when everything is “green”!

There is a really good blog written on how to get the health status of your Always On configuration using Powershell. You can take it as far as you like (e.g.: report on individual database states), but I am sticking to the general health: Test-SqlAvailabilityGroup

When you add this to your scripts, you need to deal with some extra stuff to make this work nicely. In some cases the dashboard is reporting “Unhealthy” or “Warning” when there is actually nothing wrong…..yet. So you need some kind of retry mechanism.

For example: When you perform a failover and there are databases in the Availability Group that deal with lots of transactions, you may see that, after a failover, the database is still seeding data for a couple of minutes. (I dealt with a SAP database that needed 9 minutes of seeding after a failover). During the seeding, the dashboard shows a warning. In that case you will have to wait till the signal “Healthy” comes back.

Keep in mind: when using powershell to check for Health on your Availability Groups you should always connect to the primary replica, otherwise status “unkown” is returned!

In the example below:
– I am checking if $Server is the primary Replica. If not, we will switch context.
– Then, we will initiate a stopwatch and let it run for max 10 minutes. If the dashboard is not reporting “Healthy” within those 10 minutes, we will no longer wait and report “Unhealthy”.

$server='your servername'

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
              
        ## Dashboard only available on Primary Replica
        $Ags = $srv.AvailabilityGroups

        if ($AGs.PrimaryReplicaServerName -ne $Server) {
            $srv.ConnectionContext.Disconnect()
                       
            $AGName = $AGs.Name
       
            $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $AGs.PrimaryReplicaServerName
            $Ags = $srv.AvailabilityGroups[$AGName]
        }

        $Name = $srv.Name
        if ($srv.InstanceName -eq '') {
            $InstanceName = 'DEFAULT'
        }
        else {
            $InstanceName = $srv.InstanceName
        }
                             
        $Path = "SQLSERVER:\Sql\$Name\$InstanceName\AvailabilityGroups\$AgName"
              
        $timeout = new-timespan -Minutes 10
        $sw = [diagnostics.stopwatch]::StartNew()

        while ($sw.elapsed -lt $timeout) {
            if ( (Test-SqlAvailabilityGroup -Path $path -ErrorAction SilentlyContinue).HealthState  -eq "Healthy") {
               
                write-verbose "Availabilty Group is reporting Healty"
                $TimedOut = $false
                $AgState= "Healthy"
                break
            }
            else {
                write-warning "Availabilty Group is not reporting Healty... retrying for max 10 minutes"
               
            }
         
            start-sleep -seconds 5
            $TimedOut = $true
        }
        if($TimedOut){
        $AgState= "UnHealthy"
        }

        $AGState
        $srv.ConnectionContext.Disconnect()

I hope my guide on Monitoring the Health of Always On with Powershell was helpful—feel free to leave questions in the comments!

If the task still seems a bit daunting, book a call with Data Masterminds. We have the expertise to execute this and other SQL Server management and troubleshooting tasks to save you the headache.

This post is a part of my series on Patching SQL Server AG using SCCM and Powershell
Please read this post for the complete overview

Sometimes the SCCM client does not show any updates. Nice, nothing we have to do! Unfortunately, this does not mean that there are no updates available! I’ve learned that the SCCM client is VERY sensitive and can get stuck in a “I am just going to do nothing”-state. I also noticed that any WMI query against the SCCM namespace will then run forever. There can be a couple of reasons for this behavior:

• When you install (or deinstall) patches manually, or any other way outside SCCM, the client gets confused because his “supposed-to-be” -state differs from reality and simply stops working (in my experience).
• There is a pending reboot
• If there is a Windows Servicing Stack Update (SSU) available, install this one first as it blocks other updates to appear in the client! There are a bunch of upvotes of this “bug”to get fixed, filed by the SCCM community here

In these cases you want to trigger the client, by telling it to re-scan the catalog and provide the missing updates.
If you prefer (for some reason) to do this manually: From the control panel open de configuration manager. The tab “actions”, shows you a couple of triggers that you can run against the client:

The only way I got the SCCM client to work and show me the pending updates is the following list of steps (I was able to reproduce this!):

• Trigger SSCM trigger Schedule 113 ( Scan by Update Source )
• Wait 60 sec
• Restart Windows Update Service
• Trigger SSCM trigger Schedule 108 ( Software Updates Assignments Evaluation Cycle )
• Check for Windows Servicing Stack Update
• Install Windows Servicing Stack Update (Never requires reboot).
• Trigger SSCM trigger Schedule 113 (Scan by Update Source)
• Wait 60 sec
• Restart Windows Update Service
• Trigger SSCM trigger Schedule 108 ( Software Updates Assignments Evaluation Cycle )

To trigger an SCCM Schedule using Powershell and WMI:

$Server='MyServerName'

Invoke-WMIMethod -ComputerName $Server -Namespace root\ccm -Class SMS_CLIENT -Name TriggerSchedule "{00000000-0000-0000-0000-000000000113}"  | Out-Null

An extensive list of triggercodes can be found here

To Remote restart the Windows Update Service:

$Server='MyServerName'

Get-WmiObject Win32_Service -Computer $Server -Filter "Name='wuauserv'" | ForEach-Object {
                    $_.StopService()
                    $_.StartService()
                }|Out-Null

To determine if there is a pending Windows Servicing Stack Update:

$Server='MyServerName'

Get-WmiObject -Namespace "root\ccm\clientSDK" -Class CCM_SoftwareUpdate -ComputerName $Server | Where-Object { ($_.EvaluationState -like "*$($AppEvalState0)*" -or $_.EvaluationState -like "*$($AppEvalState1)*") -and $_.Name -match "Servicing Stack Update" }

Installing the Windows Servicing Stack Update:

$Server='MyServerName'

$PendingUpdateList = (Get-WmiObject -Namespace "root\ccm\clientSDK" -Class CCM_SoftwareUpdate -ComputerName $Server | Where-Object { ($_.EvaluationState -like "*$($AppEvalState0)*" -or $_.EvaluationState -like "*$($AppEvalState1)*") -and $_.Name -match "Servicing Stack Update" })
$MissingUpdatesReformatted = @($PendingUpdateList | ForEach-Object { if ($_.ComplianceState -eq 0) { [WMI]$_.__PATH } }) 

Invoke-WmiMethod -ComputerName $Server -Class CCM_SoftwareUpdatesManager -Name InstallUpdates -ArgumentList (, $MissingUpdatesReformatted) -Namespace root\ccm\clientsdk | Out-Null

I hope my guide on Triggering SCCM Client using Powershell was helpful—feel free to leave questions in the comments!

If the task still seems a bit daunting, book a call with Data Masterminds. We have the expertise to execute this and other SQL Server management and troubleshooting tasks to save you the headache.