In Azure, How Can You Configure An Alert Or Notification When A Sql Server Failover Happened?

In Azure, how can you configure an alert or notification when a SQL Server failover happened if you setup a SQL server with Failover groups and failover policy is set to automatic?

Solution 1:

Found a way to script this in Azure using Automation Accounts > Runbook > using Powershell. A simple script like this should do it. Just need to figure out the run as account and trigger it by schedule or alert.

function sendEmailAlert
{
    # Send email
}


function checkFailover
{
    $list = Get-AzSqlDatabaseFailoverGroup -ResourceGroupName "my-resourceGroup" -server "my-sql-server"if ( $list.ReplicationRole -ne 'Primary')
    { 
        sendEmailAlert
    }
}

checkFailover

Solution 2:

Azure SQL database only support these alert metrics:

We could not using the alert when SQL Server failure happened. You can get this from this document: Create alerts for Azure SQL Database and Data Warehouse using Azure portal.

Hope this helps.

Solution 3:

Thanks CKelly - gave me a good kick start to something that should be standard in Azure. I created an Azure Automation Account, added the Az.Account, Az.Automation and Az.Sql modules then added a bit more to your code. In Azure I created a SendGrid account.

#use the Azure Account Automation details to login to Azure$Conn = Get-AutomationConnection -Name AzureRunAsConnection
Connect-AzAccount -ServicePrincipal -Tenant $Conn.TenantID -ApplicationId $Conn.ApplicationID -CertificateThumbprint $Conn.CertificateThumbprint

#create email alertfunctionsendEmailAlert{
    # Send email$From = "<email from>"$To = "<email of stakeholders to receive this message>"$SMTPServer = "smtp.sendgrid.net"$SMTPPort = "587"$Username = "<sendgrid username>"$Password = "<sendgridpassword>"$subject = "<email subject>"$body = "<text to go in email body>"$smtp = New-Object System.Net.Mail.SmtpClient($SMTPServer, $SMTPPort)
$smtp.EnableSSL = $true$smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password)
$smtp.Send($From, $To, $subject, $body)
}

#create failover check and send if the primary server has changedfunctioncheckFailover{
    $list = Get-AzSqlDatabaseFailoverGroup -ResourceGroupName "<the resourcegroup>" -server "<SQl Databse server>"if ( $list.ReplicationRole -ne 'Primary')
    { 
        sendEmailAlert
    }
}

checkFailover

This process may help others.