Monitoring Always On SQL instances

Hi,

I am sure this has been asked before, but I would like to monitor Always on SQL instances with App Insights for SQL. What is the correct way of monitoring the nodes and the listeners?

Since the system level databases should be monitored, I added the secondary nodes with AppInsights to monitor those. However, adding the secondary node marks the node in an "Unknown" state.

Thank you for your time.

Find more posts tagged with

ag group availability group alwayson

Accepted answers

All comments

This is not exact solution for your question but just wanted to share it! We are monitoring Always On instances using custom SQLs provided by our DBA :-(

Thank you for your response. Just trying to see if there is an "out of the box" approach to monitoring Always On, since they have been around since Windows 2008 R2.

Hello Team, I am also curious to know how to monitor the Always on database instances for primary and secondary, can you please share the created alert snapshot?

Can you share your custom SQL scripts for monitoring Always On instances?

I would love to use out of the box solution too, if there is one. Please let us know if you find something.

This is what I am using:

USE [master];

DECLARE @DBCount int

SET @DBCount = 0;

DECLARE @Version int

SET @Version = SUBSTRING(

CONVERT(varchar, SERVERPROPERTY('ProductVersion'))

, 0

, CHARINDEX('.', CONVERT(varchar,SERVERPROPERTY('ProductVersion')))

);

DECLARE @InstanceName nvarchar(128)

SET @SERVERNAME;

DECLARE @AvailabilityGroupOn int

set @AvailabilityGroupOn = 0;

DECLARE @AvailabilityPrimary int

set @AvailabilityPrimary = 0;

DECLARE @SQLStatement nvarchar(max)

SET @SQLStatement = '';

SELECT

@DBCount = COUNT(database_id)

FROM [master].[sys].[databases]

WHERE

DATABASEPROPERTYEX(name,'Updateability') = 'READ_WRITE'

AND

state_desc = 'ONLINE'

AND

[name] NOT IN ('master', 'tempdb')

;

IF

@VERSION) LIKE 'Microsoft SQL Server 201[2-9]%'

BEGIN

IF (

SELECT COUNT(name)

FROM sys.availability_groups

) > 0

BEGIN

SET @AvailabilityGroupOn = 1;

END;

END;

-- Check Synchronization Health

IF @AvailabilityGroupOn = 1

BEGIN

select top 1 t.* from

(SELECT CASE

WHEN ars.synchronization_health_desc = 'HEALTHY' THEN 1

ELSE 0

END as health_desc

,CASE

WHEN ars.synchronization_health_desc = 'HEALTHY' THEN @InstanceName + ' is HEALTHY'

ELSE @InstanceName + ' is NOT HEALTHY'

END as err_message

FROM sys.dm_hadr_availability_replica_states as ars

LEFT JOIN sys.availability_groups AS ag

ON ag.group_id = ars.group_id

WHERE ars.is_local = 1) t

END;

ELSE

SELECT 1 AS [health_desc], @InstanceName + ' does not have AlwaysOn enabled' AS message_part

Thank you for sharing this!
Much appreciate it!
Cheryl

Quick Links