0

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?

CKelly
  • 71
  • 5

3 Answers3

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
CKelly
  • 71
  • 5
0

Azure SQL database only support these alert metrics:

enter image description here

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.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • Yes, am aware of the list. You can set it when creating alert rule for a database. I'm looking for server alert when failover happen and not on individual database (resource) level. Thanks. – CKelly May 16 '19 at 06:24
  • Server alert only support Azure managed instance. And Azure SQL doesn't have the feature like Server alert now. – Leon Yue May 16 '19 at 06:44
  • So, is there a way to script it in azure if it’s not sql managed instance? Thanks. – CKelly May 16 '19 at 10:33
  • As I know, there isn't way you can script it in Azure. Hope my answer can helps you. – Leon Yue May 17 '19 at 01:36
  • Hi @CKay, if my answer can be helps, could you please accept it? Thanks and have a good day. – Leon Yue May 20 '19 at 08:37
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.

binway
  • 88
  • 1
  • 11