dbi Blog

By one of our Customers, I have to use Qualys to patch their production SQL Server instances. And to be more precise the Qualys Patch Management application which provides a solution to manage vulnerabilities and deploy patches to secure and keep assets up-to-date.
I will use the job functionality of Qualys Patch Management to automate the patching based on a specific schedule.

My customer has the following context, a two nodes Always On active-active cluster with 4 instances where each instance owns between five and seven Availability Groups spread on the two cluster nodes.

The first step is to connect to the Qualys subscription of my customer:

And to navigate to the Patch Management application:

Once in the Patch Management, I go in the job menu to create the first job which will patch the first node of my Always On cluster.
As this job will be executed first it will have to execute the following tasks:

Save the Availability group configuration for all instances in order to be able to re-dispatch the Availability Groups on both nodes once the patching will be finished
Fail-over all Availability Groups from the node server1 to the node server2
Patch the instances on node server1 with the available patches
Reboot the server
Fail-over all Availability Groups from the node server2 to the node server1

In terms of Qualys job I have to create a new job with the following information:

A name to identify the job, here SQLServer_server1_M_3rd_Saturday_2200 as this job will be executed the third Saturday of each month at 10PM

Select the asset (here the server) where the patches will be executed: server1

Pre-actions: this step provides some actions which can be executed before to patch the assets. It can be: to run a script, install a software, change a registry key, uninstall a software or a system reboot.
I will use it to execute my PowerShell script to:
1. Save the Availability Groups configuration in a JSON file (you can find here an example of this file below)
2. fail-over the Availability Groups which are primary on node server1 to server2. The PowerShell script is also logging in a file

###Logging functions
Function Out-Log() {
param(
[ValidateSet('INFO','WARNING','ERROR')]
[String] $Type = 'INFO',
[String] $Message
)

    '['+(Get-Date -f 'yyyy-MM-dd HH:mm:ss') +'] ' + ' - [' + $Type + '] - ' + $Message | Out-File -FilePath $LogFile -Append;
}

Function Add-Warning() {
param(
[String] $Warning
)
    If ($Warning) {
    Out-Log -Type WARNING -Message $Warning;
    }
}

Function Add-Error() {
param(
[String] $Message
)
    Out-Log -Type ERROR -Message $Message;
}

###AGs functions
#Function to save Availability Group configuration in a JSON file
Function AGsSaveConfiguration(){
param (
[Array] $instances,
[String] $ConfigFile
)
    Out-Log -Type INFO "The following configurations have been saved:"
    $initialState = @()
    foreach ($instance in $instances) {
        $ags = Get-DbaAvailabilityGroup -SqlInstance $instance
        foreach ($ag in $ags) {
            $initialState += [PSCustomObject]@{
                Instance = $instance
                AGName   = $ag.Name
                Primary  = $ag.PrimaryReplica
            }
            $output = "Instance = $instance, AGName = $($ag.Name), Primary  = $($ag.PrimaryReplica)"
            Out-Log -Type INFO "$output" 
        }
    }
    #Add configuration to a JSON file
    $initialState | ConvertTo-Json | Out-File $ConfigFile
    $output = $initialState | Format-Table
}

#Function to save Availability Group configuration in a JSON file
Function AGsRestoreConfiguration(){
param (
[Array] $instances,
[String] $ConfigFile
)
    if (!(Test-Path -Path $ConfigFile)) {
        Add-Error "Configuration file is missing"
        Add-Error "Exit without having restore the AG configurations"
        Return
    }

    $initialState = Get-Content $ConfigFile | ConvertFrom-Json

    foreach ($entry in $initialState) {
        write-host $entry
        $ag = Get-DbaAvailabilityGroup -SqlInstance $entry.Instance -AvailabilityGroup $entry.AGName
        if (($ag.PrimaryReplica -ne $entry.Primary) -and ($ag.LocalReplicaRole -eq "Secondary")) {
            Write-Host "Failover of Availability Group $($entry.AGName) to $($entry.Primary)"
            Out-Log -Type INFO "Failover of Availability Group $($entry.AGName) to $($entry.Primary)"
            Try {
                Invoke-DbaAgFailover -SqlInstance $entry.Instance -AvailabilityGroup $entry.AGName -Confirm:$false
            }
            Catch {
                Write-Host "Error during failover of Availability Group $($entry.AGName) to $($entry.Primary)"
                Add-Error "Error during failover of Availability Group $($entry.AGName) to $($entry.Primary)"                
            }
        }
    }
}

#Function to failover a list of instances to a specific host
Function AGsFailoverTo(){
param (
[Array] $instances,
[String] $TargetNode
)
    foreach ($instance in $instances) {
        $ags = Get-DbaAvailabilityGroup -SqlInstance $instance
        foreach ($ag in $ags) {
            if (($ag.ComputerName -eq $TargetNode) -and ($ag.LocalReplicaRole -eq "Secondary")) {
                Write-Host "Failover of $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                Out-Log -Type INFO "Failover of $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                Try {
                    Invoke-DbaAgFailover -SqlInstance $instance -AvailabilityGroup $ag.Name -Confirm:$false
                }
                Catch {
                    Write-Host "Error during failover of Availability Group $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                    Add-Error "Error during failover of Availability Group $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                }
            }
        }
    }
    Out-Log -Type INFO "All Availability Groups have been failover to node $TargetNode"

}

#LOG file path and name
$LogFileName = "AGsFailoverForPatching.txt"
$LogFilePath = "\\ShareFolder\LOG"
$LogFile = "$LogFilePath\$LogFileName"

#Configuration file path and name
$AGsConfigFileName = "initial_state.json"
$AGsConfigFilePath = "\\ShareFolder\LOG"
$AGsConfigFile = "$AGsConfigFilePath\$AGsConfigFileName"

###################################################################
#Save Availability Group configuration for all instances
Out-Log -Type INFO "******************** START PROCESS ********************"
Out-Log -Type INFO "START TO SAVE AVAILABILITY GROUPS CONFIGURATION"
Out-Log -Type INFO "Configuraton will be saved in file $AGsConfigFile"

#Find instances by list
$instances = @('server1\Instance1','server1\Instance2','server1\Instance3','server1\Instance4','server2\Instance1','server2\Instance2','server2\Instance3','server2\Instance4')
Out-Log -Type INFO "Instances available: $instances"

#Call the function AGsSaveConfiguration
AGsSaveConfiguration -instances $instances -ConfigFile $AGsConfigFile

Out-Log -Type INFO "Configurations saved successfully"

###################################################################
#Failover all Availability group to a specify node
Out-Log -Type INFO "START TO FAILOVER ALL AVAILABILITY GROUPS"
#Target node
$targetNode = "server2"
Out-Log -Type INFO "Target node: $targetNode"

#Call the function AGsFailoverTo
AGsFailoverTo -instances $instances -TargetNode $targetNode

Select the patches to apply to the assets, here we will select then automatically based on the filter patch.appFamily: SQL Server

Qualys automatically detects that SQL Server 2019 instances are installed on this server as well as SQL Server Management Studio and provides the available patches.
Once patched have been applied, the server will automatically reboot.

Post-actions: the possible post actions are: run a script, install a software, change a registry key or uninstall a software. As the instances have been patched and the server rebooted, we need to fail-over all Availability Groups which are primary on node server2 to server1, as the node server2 will be patched by the second job. A PowerShell script is then executed to do that:

###Logging functions
Function Out-Log() {
param(
[ValidateSet('INFO','WARNING','ERROR')]
[String] $Type = 'INFO',
[String] $Message
)
    '['+(Get-Date -f 'yyyy-MM-dd HH:mm:ss') +'] ' + ' - [' + $Type + '] - ' + $Message | Out-File -FilePath $LogFile -Append;
}

Function Add-Warning() {
param(
[String] $Warning
)
    If ($Warning) {
    Out-Log -Type WARNING -Message $Warning;
    }
}

Function Add-Error() {
param(
[String] $Message
)
    Out-Log -Type ERROR -Message $Message;
}

###AGs functions
#Function to save Availability Group configuration in a JSON file
Function AGsSaveConfiguration(){
param (
[Array] $instances,
[String] $ConfigFile
)
    Out-Log -Type INFO "The following configurations have been saved:"
    $initialState = @()
    foreach ($instance in $instances) {
        $ags = Get-DbaAvailabilityGroup -SqlInstance $instance
        foreach ($ag in $ags) {
            $initialState += [PSCustomObject]@{
                Instance = $instance
                AGName   = $ag.Name
                Primary  = $ag.PrimaryReplica
            }
            $output = "Instance = $instance, AGName = $($ag.Name), Primary  = $($ag.PrimaryReplica)"
            Out-Log -Type INFO "$output" 
        }
    }
    #Add configuration to a JSON file
    $initialState | ConvertTo-Json | Out-File $ConfigFile
    $output = $initialState | Format-Table
}

#Function to save Availability Group configuration in a JSON file
Function AGsRestoreConfiguration(){
param (
[Array] $instances,
[String] $ConfigFile
)
    if (!(Test-Path -Path $ConfigFile)) {
        Add-Error "Configuration file is missing"
        Add-Error "Exit without having restore the AG configurations"
        Return
    }

    $initialState = Get-Content $ConfigFile | ConvertFrom-Json

    foreach ($entry in $initialState) {
        write-host $entry
        $ag = Get-DbaAvailabilityGroup -SqlInstance $entry.Instance -AvailabilityGroup $entry.AGName
        if (($ag.PrimaryReplica -ne $entry.Primary) -and ($ag.LocalReplicaRole -eq "Secondary")) {
            Write-Host "Failover of Availability Group $($entry.AGName) to $($entry.Primary)"
            Out-Log -Type INFO "Failover of Availability Group $($entry.AGName) to $($entry.Primary)"
            Try {
                Invoke-DbaAgFailover -SqlInstance $entry.Instance -AvailabilityGroup $entry.AGName -Confirm:$false
            }
            Catch {
                Write-Host "Error during failover of Availability Group $($entry.AGName) to $($entry.Primary)"
                Add-Error "Error during failover of Availability Group $($entry.AGName) to $($entry.Primary)"                
            }
        }
    }
}

#Function to failover a list of instances to a specific host
Function AGsFailoverTo(){
param (
[Array] $instances,
[String] $TargetNode
)
    foreach ($instance in $instances) {
        $ags = Get-DbaAvailabilityGroup -SqlInstance $instance
        foreach ($ag in $ags) {
            if (($ag.ComputerName -eq $TargetNode) -and ($ag.LocalReplicaRole -eq "Secondary")) {
                Write-Host "Failover of $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                Out-Log -Type INFO "Failover of $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                Try {
                    Invoke-DbaAgFailover -SqlInstance $instance -AvailabilityGroup $ag.Name -Confirm:$false
                }
                Catch {
                    Write-Host "Error during failover of Availability Group $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                    Add-Error "Error during failover of Availability Group $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                }
            }
        }
    }
    Out-Log -Type INFO "All Availability Groups have been failover to node $TargetNode"

}

#LOG file path and name
$LogFileName = "AGsFailoverForPatching.txt"
$LogFilePath = "\\ShareFolder\LOG"
$LogFile = "$LogFilePath\$LogFileName"

#Configuration file path and name
$AGsConfigFileName = "initial_state.json"
$AGsConfigFilePath = "\\ShareFolder\LOG"
$AGsConfigFile = "$AGsConfigFilePath\$AGsConfigFileName"

###################################################################
#Failover all Availability group to a specify node
Out-Log -Type INFO "START TO FAILOVER ALL AVAILABILITY GROUPS"
#Target node
$targetNode = "server1"
Out-Log -Type INFO "Target node: $targetNode"

#Find all instances
$instances = @('server1\Instance1','server1\Instance2','server1\Instance3','server1\Instance4','server2\Instance1','server2\Instance2','server2\Instance3','server2\Instance4')
Out-Log -Type INFO "Instances available: $instances"

#Call the function AGsFailoverTo
AGsFailoverTo -instances $instances -TargetNode $targetNode

Schedule is the third Saturday of each month at 10PM with a duration of 2 hours

There are also some possibles options that can be enabled or disabled for the job.
I select the Reboot Countdown to send a message to the users of the server before the reboot, a message is also sent to a distribution list at the execution start and after completion and the patches will try to be downloaded by the Qualys agent before the job schedule.

It’s finish for my first job, I need to create now the second one.
Qualys gives the possibility to clone job, so I’m using this feacture and change the needed information for my second job.
This job will patch my second server and will have to:

Failover all Availability Groups from the node server2 to the node server1 (already done in the first job as post-action, but I do it again)
Patch the instances on node server2 with the available patches
Reboot the server
Redistribute the Availability Groups based on the JSON file created in the pre-action of the first job

In terms of Qualys, this job has the following information:

A name to identify the job, here SQLServer_server2_M_3rd_Sunday_0001 as this job will be executed the third Sunday of each month at 00:01AM, after the completion of the first one
Select the asset (here the server) where the patches will be executed: server2
Pre-actions: it will be a PowerShell script which will:
o Failover the Availability Groups from node server2 to server1 with the following PowerShell script (the script is also logging in a file)

###Logging functions
Function Out-Log() {
param(
[ValidateSet('INFO','WARNING','ERROR')]
[String] $Type = 'INFO',
[String] $Message
)
    '['+(Get-Date -f 'yyyy-MM-dd HH:mm:ss') +'] ' + ' - [' + $Type + '] - ' + $Message | Out-File -FilePath $LogFile -Append;
}

Function Add-Warning() {
param(
[String] $Warning
)
    If ($Warning) {
    Out-Log -Type WARNING -Message $Warning;
    }
}

Function Add-Error() {
param(
[String] $Message
)
    Out-Log -Type ERROR -Message $Message;
}

###AGs functions
#Function to save Availability Group configuration in a JSON file
Function AGsSaveConfiguration(){
param (
[Array] $instances,
[String] $ConfigFile
)
    Out-Log -Type INFO "The following configurations have been saved:"
    $initialState = @()
    foreach ($instance in $instances) {
        $ags = Get-DbaAvailabilityGroup -SqlInstance $instance
        foreach ($ag in $ags) {
            $initialState += [PSCustomObject]@{
                Instance = $instance
                AGName   = $ag.Name
                Primary  = $ag.PrimaryReplica
            }
            $output = "Instance = $instance, AGName = $($ag.Name), Primary  = $($ag.PrimaryReplica)"
            Out-Log -Type INFO "$output" 
        }
    }
    #Add configuration to a JSON file
    $initialState | ConvertTo-Json | Out-File $ConfigFile
    $output = $initialState | Format-Table
}

#Function to save Availability Group configuration in a JSON file
Function AGsRestoreConfiguration(){
param (
[Array] $instances,
[String] $ConfigFile
)
    if (!(Test-Path -Path $ConfigFile)) {
        Add-Error "Configuration file is missing"
        Add-Error "Exit without having restore the AG configurations"
        Return
    }

    $initialState = Get-Content $ConfigFile | ConvertFrom-Json

    foreach ($entry in $initialState) {
        write-host $entry
        $ag = Get-DbaAvailabilityGroup -SqlInstance $entry.Instance -AvailabilityGroup $entry.AGName
        if (($ag.PrimaryReplica -ne $entry.Primary) -and ($ag.LocalReplicaRole -eq "Secondary")) {
            Write-Host "Failover of Availability Group $($entry.AGName) to $($entry.Primary)"
            Out-Log -Type INFO "Failover of Availability Group $($entry.AGName) to $($entry.Primary)"
            Try {
                Invoke-DbaAgFailover -SqlInstance $entry.Instance -AvailabilityGroup $entry.AGName -Confirm:$false
            }
            Catch {
                Write-Host "Error during failover of Availability Group $($entry.AGName) to $($entry.Primary)"
                Add-Error "Error during failover of Availability Group $($entry.AGName) to $($entry.Primary)"                
            }
        }
    }
}

#Function to failover a list of instances to a specific host
Function AGsFailoverTo(){
param (
[Array] $instances,
[String] $TargetNode
)
    foreach ($instance in $instances) {
        $ags = Get-DbaAvailabilityGroup -SqlInstance $instance
        foreach ($ag in $ags) {
            if (($ag.ComputerName -eq $TargetNode) -and ($ag.LocalReplicaRole -eq "Secondary")) {
                Write-Host "Failover of $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                Out-Log -Type INFO "Failover of $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                Try {
                    Invoke-DbaAgFailover -SqlInstance $instance -AvailabilityGroup $ag.Name -Confirm:$false
                }
                Catch {
                    Write-Host "Error during failover of Availability Group $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                    Add-Error "Error during failover of Availability Group $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                }
            }
        }
    }
    Out-Log -Type INFO "All Availability Groups have been failover to node $TargetNode"
}

#LOG file path and name
$LogFileName = "AGsFailoverForPatching.txt"
$LogFilePath = "\\ShareFolder\LOG"
$LogFile = "$LogFilePath\$LogFileName"

#Configuration file path and name
$AGsConfigFileName = "initial_state.json"
$AGsConfigFilePath = "\\ShareFolder\LOG"
$AGsConfigFile = "$AGsConfigFilePath\$AGsConfigFileName"

###################################################################
#Failover all Availability group to a specify node
Out-Log -Type INFO "START TO FAILOVER ALL AVAILABILITY GROUPS"
#Target node
$targetNode = "server1"
Out-Log -Type INFO "Target node: $targetNode"

#Find all instances
$instances = @('server1\Instance1','server1\Instance2','server1\Instance3','server1\Instance4','server2\Instance1','server2\Instance2','server2\Instance3','server2\Instance4')
Out-Log -Type INFO "Instances available: $instances"

#Call the function AGsFailoverTo
AGsFailoverTo -instances $instances -TargetNode $targetNode

Select the patches to apply to the assets, here we will select then automatically based on the filter patch.appFamily: SQL Server (like for the first job)
Post-actions: once the instances have been patched and the server rebooted, we need to redistribute the Availability Groups over the 2 nodes based on the JSON file created at the beginning of this patch process. For that we execute the following PowerShell script:

###Logging functions
Function Out-Log() {
param(
[ValidateSet('INFO','WARNING','ERROR')]
[String] $Type = 'INFO',
[String] $Message
)
    '['+(Get-Date -f 'yyyy-MM-dd HH:mm:ss') +'] ' + ' - [' + $Type + '] - ' + $Message | Out-File -FilePath $LogFile -Append;
}

Function Add-Warning() {
param(
[String] $Warning
)
    If ($Warning) {
    Out-Log -Type WARNING -Message $Warning;
    }
}

Function Add-Error() {
param(
[String] $Message
)
    Out-Log -Type ERROR -Message $Message;
}

###AGs functions
#Function to save Availability Group configuration in a JSON file
Function AGsSaveConfiguration(){
param (
[Array] $instances,
[String] $ConfigFile
)
    Out-Log -Type INFO "The following configurations have been saved:"
    $initialState = @()
    foreach ($instance in $instances) {
        $ags = Get-DbaAvailabilityGroup -SqlInstance $instance
        foreach ($ag in $ags) {
            $initialState += [PSCustomObject]@{
                Instance = $instance
                AGName   = $ag.Name
                Primary  = $ag.PrimaryReplica
            }
            $output = "Instance = $instance, AGName = $($ag.Name), Primary  = $($ag.PrimaryReplica)"
            Out-Log -Type INFO "$output" 
        }
    }
    #Add configuration to a JSON file
    $initialState | ConvertTo-Json | Out-File $ConfigFile
    $output = $initialState | Format-Table
}

#Function to save Availability Group configuration in a JSON file
Function AGsRestoreConfiguration(){
param (
[Array] $instances,
[String] $ConfigFile
)
    if (!(Test-Path -Path $ConfigFile)) {
        Add-Error "Configuration file is missing"
        Add-Error "Exit without having restore the AG configurations"
        Return
    }

    $initialState = Get-Content $ConfigFile | ConvertFrom-Json

    foreach ($entry in $initialState) {
        write-host $entry
        $ag = Get-DbaAvailabilityGroup -SqlInstance $entry.Instance -AvailabilityGroup $entry.AGName
        if (($ag.PrimaryReplica -ne $entry.Primary) -and ($ag.LocalReplicaRole -eq "Secondary")) {
            Write-Host "Failover of Availability Group $($entry.AGName) to $($entry.Primary)"
            Out-Log -Type INFO "Failover of Availability Group $($entry.AGName) to $($entry.Primary)"
            Try {
                Invoke-DbaAgFailover -SqlInstance $entry.Instance -AvailabilityGroup $entry.AGName -Confirm:$false
            }
            Catch {
                Write-Host "Error during failover of Availability Group $($entry.AGName) to $($entry.Primary)"
                Add-Error "Error during failover of Availability Group $($entry.AGName) to $($entry.Primary)"                
            }
        }
    }
}

#Function to failover a list of instances to a specific host
Function AGsFailoverTo(){
param (
[Array] $instances,
[String] $TargetNode
)
    foreach ($instance in $instances) {
        $ags = Get-DbaAvailabilityGroup -SqlInstance $instance
        foreach ($ag in $ags) {
            if (($ag.ComputerName -eq $TargetNode) -and ($ag.LocalReplicaRole -eq "Secondary")) {
                Write-Host "Failover of $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                Out-Log -Type INFO "Failover of $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                Try {
                    Invoke-DbaAgFailover -SqlInstance $instance -AvailabilityGroup $ag.Name -Confirm:$false
                }
                Catch {
                    Write-Host "Error during failover of Availability Group $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                    Add-Error "Error during failover of Availability Group $($ag.Name) to $TargetNode from $($ag.PrimaryReplica)"
                }
            }
        }
    }
    Out-Log -Type INFO "All Availability Groups have been failover to node $TargetNode"

}

#LOG file path and name
$LogFileName = "AGsFailoverForPatching.txt"
$LogFilePath = "\\ShareFolderLOG"
$LogFile = "$LogFilePath\$LogFileName"

#Configuration file path and name
$AGsConfigFileName = "initial_state.json"
$AGsConfigFilePath = "\\ShareFolder\LOG"
$AGsConfigFile = "$AGsConfigFilePath\$AGsConfigFileName"

###################################################################
#Restore Availability Group configuration for all instances
Out-Log -Type INFO "START TO RESTORE AVAILABILITY GROUPS CONFIGURATION"

#Call the function AGsRestoreConfiguration
$instances = @('server1\Instance1','server1\Instance2','server1\Instance3','server1\Instance4','server2\Instance1','server2\Instance2','server2\Instance3','server2\Instance4')
AGsRestoreConfiguration -instances $instances -ConfigFile $AGsConfigFile

Out-Log -Type INFO "Availability Group configuratons restored successfully"
Out-Log -Type INFO "******************** END PROCESS ********************"

Schedule is the third Sunday of each month at 00:01AM with a duration of 2 hours. With this schedule this job will be executed after the end of the first one. In Qualys there is for the moment no possibility to start a job when another one is completed…
For options, I select the Reboot Countdown to send a message to the users of the server before the reboot, a message is also sent to a distribution list at the execution start and after completion to admin and the patches will try to be downloaded by the Qualys agent before the job schedule.

Both jobs are now created. After their execution the two Always On cluster nodes will have been patched and the Availability Groups redistributed as it was before to start.

Qualys Patch Management is relatively easy to use. All patches based on your selection are availables and can be applied easily.
The pre and post actions give the possibility to prepare the patching: here fail-over, save the inital configuration, redistribute the AGs at the end.
It was a good experience and I hope it can help 😉

Post Views: 32

How to patch SQL Server instances with Qualys Patch Management

Stéphane Savorgnano

Leave a Reply:

Related blog articles

Stéphane Savorgnano

Leave a Reply:

Related blog articles

Cannot drop a tablespace?

Unit Testing PostgreSQL with pgTAP

SQL-Server 2025: Vector Indexes & Semantic Search Performance

SQL-Server 2025: Generate Data Embeddings for Semantic Search