Setting Up Alerts for SQL Always On Availability Group Failovers

A user asked for help with setting up an alert for a customer’s SQL Always On Availability Group. Suggestions were made to use a PowerShell script or a scheduled trigger using the Edge DX agent. It was eventually resolved with the use of a PowerShell script that runs every 5 minutes and checks event logs for specific event IDs. No incidents are generated due to the potential scale of scheduled triggers.

Read the entire ‘Setting Up Alerts for SQL Always On Availability Group Failovers’ thread below:

Looking for help on a specific alert that I need to get for a customer. They have a SQL always on availability group and we need to get alerted when there is any kind of failover between nodes. Has anyone made a script or trigger that can get this information?

Assuming you are referring to ControlUp for VDI (aka Real-Time DX), and assuming such a node failover generates a warning on error event in Event Viewer, a simple event trigger should work here

yeah thats the path Im going down, but there is also something that I am looking into like a powershell script that returns a specific value ie: in progress, failed, secondary, primary ,things like that

so event log monitoring wont work for this one as the customer wants to know when a node changes from 1 to 2 or 2 to 1 and its an informational event log

I see… this will require more custom work unfortunately. Either a local scheduled task that will kick in when this info even occurred and will create a warning event (so CU agent will pick this up). Or we can use the CU4D agent, if you guys have the relevant licenses (as CU4D aka Edge DX agent can be configured to detec my info events )

oh thats interesting. Yeah I could try to use EdgeDX agent to detect

how long would you say it takes for an event to show up here? I added the event id and triggered an event.

I decided to try a scheduled trigger in VDI but its not firing

@member anything I am missing with Scheduled Triggers?

Is there a filter on the next step?

there was but i removed it for testing

only targeting one folder

im looking in incidents to see if it fired, but maybe that doesnt show in this view?

correct. Scheduled triggers do not generate incidents

ohh

that may explain it. whats the best place to check?

reviewing the monitor table?

is a script attached to it?

yes

Script executions are logged in:

• The monitor that initiated the script action

• The agent that actually executed the action

• The audit log

Oh sorry. The monitor and agent log it in the windows event log. Application log to be specific

I think it is event ID 5000

yes I can see it running in the event log on the monitor. thank you! silly mistake

I was checking incidents trying to see it there but nowhere to be found

They don’t generate incidents because of scale. Scheduled triggers might generate a lot of incidents. Like your test would fire on every computer for example, every 5 minutes

yes makes sense

event ids 5000 and 5005 are the ones to look for

thanks guys. I was able to do this with a powershell script that runs with a scheduled trigger every 5 minutes on this specific device, checks event logs and posts a webhook to my alerting system. output is nice and is working well!

Continue reading and comment on the thread ‘Setting Up Alerts for SQL Always On Availability Group Failovers’.  Not a member? Join Here!