Azure sql database failover alert

Question

Hello Folks,
I have created an Azure SQL Database and then have added a failover group as per these instructions from MS - auto-failover-group-configure

To test this the document says to select the "Failover" button and you will see the Primary and Secondary servers switch. Which works fine. The other item I would envisage happening is that I would get an alert/email to tell me that I am now working on the Secondary Server but can't see where to make this happen. Or does a notification go to the Administrators of the subscription somehow and they in turn would inform the users.
Else is there a powershell script that needs to be created or something similar.

Thanks

Answer 1

Hi anonymous user - With the instructions for creating alerts, your best bet to trigger a notification on a failover event is to use the Failed Connections signal name to along with the Advanced Options to train the alert to look for a certain pattern, which is the failover event taking place. In this way you are able to take advantage of the built-in email/SMS/Push Notification services. This is the simplest means to building an alert. I would also comment on and up vote the following User Voice entry pertaining to notifications of planned failover events. You could extend this to include specific alerting to include failover and Geo-Replication specific signal logic to trigger alert notifications upon. I understand the two are slightly different (one is proactive messaging from the Product Group and the add-on is to include specific signal logic to create a more concise (reactive) alert.

You could look in to XEvents for SQL Server and Extended events in Azure SQL Database as another option.

In my case I set up an alert on both databases in each region that are part of the same failover group:

Regards,
Mike