Instant Always on Availability Group Alert- part 2

Welcome to part 2 of this alert series.

In part-1 we have seen how to schedule an alert using SQL Agent which has its drawbacks.

In this we would see how to capture the alert by identifying an event occurrence associated with a failover and associating with a Windows Event Task.

We are relying on alert number 19406 which would get triggered when state of the replica changes.
But from here how to get an email is the real test & we will be making use of powershell for the same.

Here are the steps

1) Launch "Event Viewer" & filter on Application Logs for 19406 event & click on "Attach a task to this event"

2)once clicking on that use the defaults for the first couple screens of the wizard. Kindly save the below powershell script in any one of your directories with .ps1 extension


$xpath = "<QueryList><Query Id='0' Path='$eventChannel'><Select Path='$eventChannel'>*[System[(EventRecordID=$eventRecordID)]]</Select></Query></QueryList>"
$event = Get-WinEvent -LogName $eventChannel -FilterXPath $xpath
$message = $event.Message

Send-MailMessage -From -Subject "An SQL Failover is in Progress" `-To -Body $message -SmtpServer

3) on the 3rd screen click on start program

4)  pass the below parameters & then click on Finish

Program/script: PowerShell.exe
Add arguments: .\TriggerScript.ps1 -eventRecordID $(eventRecordID) -eventChannel $(eventChannel)

5)once that is done you will find an event under task scheduler like below (although mine is empty)

6) click on that task & select export so that it would get saved in XML format. And edit that file
to add the Event parameters you which to pass along to your task. The event parameters below are the most useful for event identification. Notice the entire node <ValueQueries> and its children need to be added to the EventTrigger branch.

        <Value name="eventChannel">Event/System/Channel</Value>
        <Value name="eventRecordID">Event/System/EventRecordID</Value>
        <Value name="eventSeverity">Event/System/Level</Value>

Kindly find the below image for reference.

7) once  done save that XML file & delete that entry under event viewer task

8)Go to task scheduler on the right hand side we will find import click on import & then select the saved XML file

9) kindly make the below changes under General tab by clicking on properties

10) under Action tab kindly pass the below as argument

-NoProfile -WindowStyle Hidden -Command "D:\DBA\trigger.ps1" -eventRecordID $(eventRecordID) -eventChannel $(eventChannel)

That's it we are done so whenever An always failover happens we would get alerted automatically as there is no need for us to rely on Agent. Below is the alert what I received.