Monitoring MSSQL Always On Availability Groups

This is the yearly question about MSSQL Always On AG's and the lack of ability to monitor them sufficiently.

How can they be monitored in SAM? I end up with 51 unknown applications.

I have 150 SQL Db servers with a mix of standalone, clustered and Always On. I have AG Db's with dozens of availability groups that I am not going to use the listeners to monitor as this will duplicate monitoring of the base SQL server and be too time consuming when setting up. The AG's are often spread across both SQL nodes (not to mention the dbs not in AG's at all on these servers) so when you monitor with SAM it cant tell if either nodes status is up - just unknown. Using the cluster name results in the same problem as the cluster has no control over the AG's.

I can monitor through DPA which is better but I have limited licenses and then the integration for alarms back to Orion is severely lacking. As with alarms from traps, there is no event discriminator so there can be only 1 alarm in orion for each alert at 1 time. How hard would this be to fix?

Is there a plan from Solarwinds to address these issues or should I just separate standalone servers to use SAM and AG's to use DPA?

thanks

Matt

Find more posts tagged with

sam

availability group

dpa

appinsight for sql

Accepted answers

All comments

Seashore

Would love to have AppInsight for SQL "AG aware". As you say, most databases that are not primary on that node goes "unknown" now. Now I use custom templates to monitor and alert on the services and AG replication status. If you want monitoring on specific databases you have to set up a new node in orion pointing on the listener, so you can assign you templates to that node.

tyao

I do the same - custom templates to monitor AG node replication status. I used to have monitoring templates set up for all the databases but turns out that I only care about a few mission-critical ones.

adam.beedell

Hi Matt, I agree this is an oversight not being OOTB.

Got a monitor or two for this sort of thing:

$server = $args[0]

##$server = "x"

###Block For: If primary then proceed, else unknown


$SQLPRIMARY= invoke-command -Computer $server -Scriptblock  {


[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$svr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Server;
$svr.ConnectionContext.StatementTimeout = 0;

$svr.AvailabilityGroups.PrimaryReplicaServerName | select -First 1


}

If ($SQLPRIMARY -eq $server)
{
###Block for: If healthy then proceed else fail

$SQLAGS= invoke-command -Computer $server -Scriptblock  {Get-ChildItem "SQLSERVER:\Sql\$using:server\DEFAULT\AvailabilityGroups"| Test-SqlAvailabilityGroup}
$results = IF(
($SQLAGS -eq $Null) -xor
($SQLAGS | where -Property Healthstate -NE Healthy)
)

{$false}

else

{$true}

if($SQLAGS -eq $null)
{
    write-host "Statistic: 0"
    write-host "Message: An error has occurred $error"
    exit 1
}
ELSE
{

    if ($results -eq $false)
    {
        $message = $SQLAGS | where -Property HealthState -ne Healthy
        $statistic =  $SQLAGS | 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 -Scriptblock  {Get-ChildItem "SQLSERVER:\Sql\$using:SQLPRIMARY\DEFAULT\AvailabilityGroups"| Test-SqlAvailabilityGroup}
            $results = IF(
            ($SQLAGS -eq $Null) -xor
            ($SQLAGS | where -Property Healthstate -NE Healthy)
            )

            {$false}

            else

            {$true}

            if($SQLAGS -eq $null)
            {
                write-host "Statistic: 0"
                write-host "Message: An error has occurred $error"
                exit 1
            }
            ELSE
            {

                if ($results -eq $false)
                {
                    $message = $SQLAGS | where -Property HealthState -ne Healthy
                    $statistic =  $SQLAGS | 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
                }
}

}

Seashore

I use a normal SQL component in SAM to run below query to get AG sync status:

select 
	g.name
INTO #AGStatus
from sys.dm_hadr_availability_group_states S
inner join sys.availability_groups G on s.group_id=G.group_id
Where synchronization_health<2
AND s.primary_recovery_health=1

IF (Select COUNT(name) from #AGStatus)>0 
BEGIN
		Select 
		count(*) as "Rowcount", 
		t.FailingAG 
		from(
			Select 
				substring(
				(
					Select ', '+ags.name  AS [text()]
					From #AGStatus ags
					For XML PATH ('')
				)
				, 2, 1000) [FailingAG]
	From #AGStatus ags) 
	as t
	group by t.FailingAG 

END
ELSE
Select 0 AS "RowCount"

Drop table #AGStatus

KITGuy

Its not just the databases that go unknown, its the Instance as well that goes unknown. How then do you alert on the availability status of the instance? The Instance status should ignore unknown Db's like DPA ignores unknown AG statuses of secondary replicas.

The Dba's are considering giving read access to the monitoring account to the secondary replicas - would this be advisable?

Seashore

As far as I know you can't with permissions give just the monitoring account read access to secondary replicas. It's a setting in the AG setup. If you set the secondary replicas to "readable" then Orion can read those databases as normal and they will not be unknown anymore. If that is a situation you want is up to you and your dba's.