Tag Archives: Events

Know When SQL Server Availability Group Fails Over by Using Powershell

We recently started using HAG (High Availability Groups) in SQL Server and had several unplanned failovers. The failovers occurred for various reason which this article does not present. What was relevant to us was the failovers occurred without our knowledge and in one case, several days passed before we were aware a group had failed over to a secondary instance.

The purpose of this article is to show what was done to make us immediately aware of a failover as it occurs. This is accomplished by identifying an event occurrence associated with a failover and associating with a Windows Event Task. The event task would  then send an email containing the event message to a responsible group or person.

The event which I identified as being most relevant is Event 19406 from the SQL Instance (in this case the default instance of ‘MSSQLSERVER’) as presented here

I already knew how to attach a Windows Task to an event but what I did not know was how the data from the event is passed to the task. I wanted the task to be able to provide the event data in an email. In researching this I came across a great article on this very subject. The article presented all the information I needed including Powershell source code. I had to tweak the source code ever so slightly for my own use. The full article can be found at this link http://blogs.technet.com/b/wincat/archive/2011/08/25/trigger-a-powershell-script-from-a-windows-event.aspx and you should follow it. There is essential information you will need to know to edit and add to the Windows Task. Kudos to OttoHelweg2 for this fine article.

And, here is the source code I used based on the above mentioned article


$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 AvailabilityMonitor@myorganization.com -Subject "An SQL Failover is in Progress" `
    -To SQLServerGroup@myorganization.com -Body $message -SmtpServer mailserver.myorganization.com

The $eventRecordId and $eventChannel are passed to the Windows task from the triggering event and are used by Powershell to obtain the actual event record. The “message” data is then used as the Body of a Send-MailMessage cmdlet.

As mentioned, we are trapping the 19406 event. When reviewing the Event list, you will notice the event will fire several times in response to a failover. However, because the event is generated by the same task, the task associated with the event only fires once.

Powershell SQL Backup with Event Handler

A persistent problem I’ve had is capturing the output messages when executing SQL procedures. That is, until I recently learned how to capture those messages for output using an event handler.

The example function, provided below, executes an SQL Backup, uses an event handler to capture the output messages, and passes the information back to the calling routine.

Function Backup-SqlDatabase
    Backs up an sql database to local or remote disk.

    This script will perform a full back up of the specified database to a local or network disk.

    Specifies the sqlserver or instance name you wish to connect to and restore.

    Specifies the Database name you wish to connect to and backup.

    Specifies the path to the back up file.

    Run-SqlBackup -Server "Server\MySql" -Database "MyDb" `
		-Path "\\server\D$\Backups\MySql.bak"
    Backs up database "MyDb" on SQL Server instance "Server\MySql" to a file on
	a network share.
	When backing up to a network share, permissions must be set appropriately 
	for the SQL Service Account.
	$output = ""
	$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
		param($sender, $event) 
	   	$output += $event.Message;
    $SQLConn = New-Object System.Data.SQLClient.SQLConnection
    $SQLConn.ConnectionString = "Server=$Server; Trusted_Connection=True"
		$SQLConn.FireInfoMessageEventOnUserErrors = $false;
		Throw $_

    Write-Verbose "Connected to server $server"
    $SQLCmd = New-Object System.Data.SQLClient.SQLCommand
    $SQLcmd = $SQLconn.CreateCommand()
    $SQLcmd.CommandText="BACKUP DATABASE [$database] 
		TO  DISK = N'$path' 
		WITH  DESCRIPTION = N'$database Full Backup', 
		NAME = N'$database-Full Database Backup', 
        $SQLcmd.Executenonquery() | Out-Null
		Throw $_

    $backup = new-object PSObject -Property @{
        Server = $Server;
        Database = $Database;
		BackupFile = $path;
		Messages = $output}
	Return $backup

Calling the Function

$path = "\\MyServer\G$\SQL Backups\MyDB.bak"
Backup-SqlDatabase -Server ThisServer -Database "MyDB" -Verbose -Path $path |
	Format-List Server,Database,Path,Messages

The output

Event Messages Captured

EventMessages Captured