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? If it can't be setup in Monitor can it be scripted elsewhere?
In Azure, how can you configure an alert or notification when a SQL Server failover happened?
2k Views Asked by CKelly At
3
There are 3 best solutions below
0

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 alert
function sendEmailAlert
{
# 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 changed
function checkFailover
{
$list = Get-AzSqlDatabaseFailoverGroup -ResourceGroupName "<the resourcegroup>" -server "<SQl Databse server>"
if ( $list.ReplicationRole -ne 'Primary')
{
sendEmailAlert
}
}
checkFailover
This process may help others.
5

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.
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.