In Azure SQL DB is possible to configure alert to receive a notification when failover happens in failover group or geo replica
Basically you need to configure activity log to send data to some destination (mandatory) in this case workspace will be used
Azure Monitor activity log
Configure activity log to send results to a workspace (this configuration will be applied to all resources not only current resource)
In activity log select export activity logs
Select Add diagnostic setting
Provide diagnostic setting name , in this demo all categories will be used to be able to see all records at the beginning and save (for this type of alert only administrative category is needed)
Alert for failover group
Query data using kusto in log analytics
Go to workspace and run query below without filters to see results , sometimes time is needed to see data arriving
AzureActivity
In this demo some data is used but there is more information that can be used to create more complex filters
Now is possible filter data looking for failovergroup ...
AzureActivity
| where OperationNameValue contains("Microsoft.Sql/servers/failoverGroups/failover/action")
Finally is possible to filter for specific activity status to get more specific records
AzureActivity
| where OperationNameValue contains("Microsoft.Sql/servers/failoverGroups/failover/action") and ActivityStatusValue =="Start"
Creating alert ...
Based on the fact that is possible to create alert based on results of the query next step is to select option to create new alert rule
AzureActivity
| where OperationNameValue contains("Microsoft.Sql/servers/failoverGroups/failover/action") and ActivityStatusValue =="Start"
Now is possible to see that query is part of the alert (In this demo use basic configuration)
Alert logic configure that alert is fired when results are greater than 0
Next step is select action group ...
Action groups
https://learn.microsoft.com/en-us/azure/azure-monitor/alerts/action-groups
Select action group
Create alert rule
and finally create alert ...
Make failover in failover group
Wait for failover ...
In some minutes email with alert will be received in email accounts defined in action group be patience because is not immediately ...
Finally email received ...
You can go to azure monitor and see alert sent ...
Sending activity log to workspace there are more actions or parameters that is possible to monitor now we can use same process to get alert when geodr failover is done only changing query
Alert for Geo replica
Change kusto query to look for different type of operation name value
AzureActivity
| where OperationNameValue contains("MICROSOFT.SQL/SERVERS/DATABASES/REPLICATIONLINKS/FAILOVER/ACTION") and ActivityStatusValue =="Start"
Create alert based on the result
Now is created then go to Azure SQL DB and make failover
Now wait for email with alert remember that will take time is not live data when is sent to workspace
Email received ...
