AlwaysOn Monitoring

• doodlingdba

  Hall of Fame

  Points: 3164

  More actions

  June 19, 2018 at 8:06 am

  #352040

  Hi all

  We have recently set up an asynchronous AlwaysOn AGs in our production environment (our first and only, though soon to be more). I am now looking at ways to monitor and alert on issues, mainly high latency. We don't have SCOM so nothing out of the box unfortunately. I have been reading some articles but would like to get some ideas from you guys on how you prefer to monitor/alert for AGs.

  I am currently experimenting with Policy Based Management and SQL server alerts, but i do find PBM a little fiddly.

  Any tips would be appreciated!

  Thanks
  Doodles

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  June 21, 2018 at 6:58 pm

  #1994931

  doodlingdba - Tuesday, June 19, 2018 8:06 AM

  Hi all

  We have recently set up an asynchronous AlwaysOn AGs in our production environment (our first and only, though soon to be more). I am now looking at ways to monitor and alert on issues, mainly high latency. We don't have SCOM so nothing out of the box unfortunately. I have been reading some articles but would like to get some ideas from you guys on how you prefer to monitor/alert for AGs.

  I am currently experimenting with Policy Based Management and SQL server alerts, but i do find PBM a little fiddly.

  Any tips would be appreciated!

  Thanks
  Doodles

  The Dashboard has a lot of decent information. This article has a list of some of the information available and includes some of the queries used by the  dashboard:
  Answering Questions with the AlwaysOn Dashboard

  This lists what is likely the most common alerts you want to set up for AOAG:
  Configure SQL Server Alerts and Notifications for AlwaysOn Availability Groups

  For some of the other things you mention, take a look at the DMV sys.dm_hadr_database_replica_states. I think that has the other information you want - write whatever queries meet your needs, write a job, schedule it for however often, etc.

  There are also some Perf Mon counters available that you can schedule in the alerts or you can query those in sys.dm_os_performance_counters.

  Sue

• owen_zeng

  Old Hand

  Points: 360

  More actions

  June 21, 2018 at 11:19 pm

  #1994935

  doodlingdba - Tuesday, June 19, 2018 8:06 AM

  Hi all

  We have recently set up an asynchronous AlwaysOn AGs in our production environment (our first and only, though soon to be more). I am now looking at ways to monitor and alert on issues, mainly high latency. We don't have SCOM so nothing out of the box unfortunately. I have been reading some articles but would like to get some ideas from you guys on how you prefer to monitor/alert for AGs.

  I am currently experimenting with Policy Based Management and SQL server alerts, but i do find PBM a little fiddly.

  Any tips would be appreciated!

  Thanks
  Doodles

  have you ever used Zabbix ?you can use it to monitor sql server  and always on.

• doodlingdba

  Hall of Fame

  Points: 3164

  More actions

  June 29, 2018 at 9:52 am

  #1995838

  Thanks Sue. Yes i'm aware of all those things. Just hoping for something a little more off the shelf 🙂 I've managed to configure some PBM conditions which alert on 'AG - Estimated Data Loss' and 'AG - Recovery Time". You can do others such as fail-overs. You can then configure alerts to pick up on the PBM faile evaluations. Works well!

  Owen - I haven't heard Zabbix, I will have a look - thanks!