Patching SQL Server AG using SCCM and Powershell

Patching an Availability Group * fully automated * is more complicated then you might think. During the patching process you will have to deal with:

  • Failovers.
  • AG Dashboards showing warnings.
  • Interact with the SCCM client to install and filter the patches.
  • Numerous heathchecks inside the process on levels like: Windows Failover Cluster, Always On Availability Groups and SQL Server.
  • Sending alerts when things go wrong.
  • (Multiple) Reboots during the patching process
  • Retry mechanisms when something takes longer than expected.
  • Returning to initial state when something goes wrong.

In this post I want to share my experiences during this process at a client, where I fully automated this patching process using Powershell. I will go through each step and share the code. Ultimately I will share the complete solution on my github.

This post will act as a wrapper to individual posts explaining every step in detail. In the end you will have a complete story and code you can use in your own processes.

The process describes the solution for:

  • Windows Failover Clustering (2 nodes)
  • A Single Availability Group (currently working on support for multiple AG’s)

Let’s get started!

Here is the flow :
(steps will turn into links to individual posts for more detail)

I hope my guide on Patching SQL Server AG using SCCM and 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.

Installing Pending updates through SCCM client using Powershell

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.

Monitor the Health of Always On with Powershell

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.

Rebooting in an automated patch process using Powershell

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

When you’ve automated your patching process, somewhere inside this process there is going to be a reboot somehow. If, after your reboot, you need to finish what you’ve started or check the health of your Availability Group, you need to know when the server is back online.

With powershell it’s fairly easy to reboot a computer using the Restart-Computer cmdlet:

$Server='MyServerName'
Restart-Computer -ComputerName $Server -Force

It’s more tricky though to determine when a server is back online.
One option is using the Test-Connection cmdlet. In the following example we keep trying when we get no response back and wait for 15 seconds between the retries:

$Server='MyServerName'
while ((Test-Connection -ComputerName $Server -Count 1 -Quiet) -ne $true) {
     Start-Sleep -Seconds 15 
}

But….what if the server is still in the process of shutting down?

Or.. what if, the server responds to a ping, but is still installing updates AFTER the reboot:

Afbeeldingsresultaat voor installing updates


You can’t do anything on this server while this is happening. You won’t even see this screen when everything is done remotely. Your script continues, waits and will fail eventually.

Another option is to use WMI for this.

Windows updates the “Lastbootuptime” when the computer is fully restarted and “usable”. You can get this information by querying the win32_operatingsystem class:

In the following example, we query this information and compare it with the current timestamp. If there is a difference, then the server is fully rebooted:

$Server='MyServerName'
$CurrentTimeStamp = (GET-DATE)
[datetime]$LastRebootTime = Get-CimInstance -ClassName win32_operatingsystem -ComputerName $Server | select-object lastbootuptime -ExpandProperty Lastbootuptime
          
        Restart-Computer -ComputerName $Server -Force
    
        $Diff = $LastRebootTime - $CurrentTimeStamp
    
        while ($Diff.TotalSeconds -lt 0) {
            try {
                [datetime]$LastRebootTime = Get-CimInstance -ClassName win32_operatingsystem -ComputerName $Server -ErrorAction SilentlyContinue | select-object lastbootuptime -ExpandProperty Lastbootuptime
                $Diff = $LastRebootTime - $CurrentTimeStamp
                Start-Sleep 5
            }   
            Catch {
              #no catch code
            }
        }

A much more reliable way to check when a server is fully rebooted!

I hope my guide on Rebooting in an automated patch process 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.

Triggering SCCM Client using Powershell

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

If there is a Servicing Stack update, install this first!


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.