We are working on configuring SQL Availability Group Health Monitoring using a PowerShell Script Monitor in SAM.
Initially, the solution found here on THWACK was sufficient. However, we ran into an issue where we have multiple AGs running on a single instance, and each node is primary on a different AG.
We need to be able to first identify the AG we are targeting on that instance, then run the code (below) to identify which node is primary for that AG so it can provide the health status. We will do the opposite for the other AG in a separate component on the application template.
We've since modified the original script from the link above for this, and have the following:
$server = $args[0]
$agnum = $args[1]
$creds = Get-Credential
###Block For: If primary then proceed, else unknown
$SQLPRIMARY= invoke-command -Computer $server -Credential $creds -Scriptblock {
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $server;
$svr.ConnectionContext.StatementTimeout = 0;
$svr.AvailabilityGroups
}
$agInstance = $SQLPRIMARY | Where-Object { $_.Name -like "*${agnum}"}
$SQLPRIMARY = $agInstance.PrimaryReplicaServerName
If ($SQLPRIMARY -eq $server)
{
###Block for: If healthy then proceed else fail
$SQLAGS= invoke-command -Computer $server -Credential $creds -Scriptblock {Get-ChildItem "SQLSERVER:\Sql\$using:server\DEFAULT\AvailabilityGroups"| Test-SqlAvailabilityGroup}
$SQLAGSOBJ = [PSCustomObject]@{}
$SQLAGS | ForEach-Object {
If ($_.Name -like "*${agnum}") {
$SQLAGSOBJ | Add-Member -MemberType NoteProperty -Name 'Name' -Value $_.Name
$SQLAGSOBJ | Add-Member -MemberType NoteProperty -Name 'HealthState' -Value $_.HealthState
}
}
$results = IF(
($SQLAGSOBJ -eq $Null) -xor
($SQLAGSOBJ | where -Property HealthState -NE Healthy)
)
{$false}
else
{$true}
if($SQLAGSOBJ -eq $null)
{
write-host "Statistic: 0"
write-host "Message: An error has occurred $error"
exit 1
}
ELSE
{
if ($results -eq $false)
{
$message = $SQLAGSOBJ | where -Property HealthState -ne Healthy
$statistic = $SQLAGSOBJ | where -Property HealthState -ne Healthy
write-host "Statistic: $($statistic.name.count)"
write-host "Message: The Following are not healthy: $($message.Name)"
exit 3
}
ELSE
{
write-host "Statistic: 0"
write-host "Message: This Server is Primary and All Databases are Healthy"
exit 0
}
}
}
ELSE
{
### This path is for when the server is secondary
$SQLAGS= invoke-command -Computer $SQLPRIMARY -Credential $creds -Scriptblock {Get-ChildItem "SQLSERVER:\Sql\$using:SQLPRIMARY\DEFAULT\AvailabilityGroups"| Test-SqlAvailabilityGroup}
$SQLAGSOBJ = [PSCustomObject]@{}
$SQLAGS | ForEach-Object {
If ($_.Name -like "*${agnum}") {
$SQLAGSOBJ | Add-Member -MemberType NoteProperty -Name 'Name' -Value $_.Name
$SQLAGSOBJ | Add-Member -MemberType NoteProperty -Name 'HealthState' -Value $_.HealthState
}
}
$results = IF(
($SQLAGSOBJ -eq $Null) -xor
($SQLAGSOBJ| where -Property HealthState -NE Healthy)
)
{$false}
else
{$true}
if($SQLAGSOBJ -eq $null)
{
write-host "Statistic: 0"
write-host "Message: An error has occurred $error"
exit 1
}
ELSE
{
if ($results -eq $false)
{
$message = $SQLAGSOBJ | where -Property HealthState -ne Healthy
$statistic = $SQLAGSOBJ | where -Property HealthState -ne Healthy
write-host "Statistic: $($statistic.name.count)"
write-host "Message: This Server is Secondary, the following are not healthy on primary ($SQLPRIMARY): $($message.Name)"
exit 3
}
ELSE
{
write-host "Statistic: 0"
write-host "Message: This Server is Secondary and All Databases are Healthy"
exit 0
}
}
}
Unfortunately, this is not working as intended. Our theory to this point is that there has to be a way to force this to run in an elevated state.
