LLI Wiki — My Digital Knowledge Base

Monitoring Azure SQL failover groups with KQL and T-SQL

Replication lag, role per database, databases missing from the group and failover alerts with KQL and T-SQL queries.

A failover group that "exists" does not prove it is healthy. I want to answer four questions in seconds: what is the replication lag? What is the role of each database? Which databases are in no group? And who failed over, and when? Here are my queries, written for a Log Analytics workspace and for T-SQL.

flowchart LR
    A["Azure SQL databases"] -- "diagnostic metrics" --> B["Log Analytics"]
    A -- "activity log" --> B
    B --> C["KQL queries"]
    C --> D["Dashboard"]
    C --> E["Alert rules"]
    F["T-SQL on each server"] --> G["Role and link per database"]

1. Replication lag (KQL)

The metric is only emitted by the primary and carries no dimension telling you the partner. If you send database metrics to Log Analytics, the table is AzureMetrics. First check the exact metric name in your workspace:

AzureMetrics
| where TimeGenerated > ago(1d) and ResourceProvider =~ "MICROSOFT.SQL"
| distinct MetricName

Then, with the lag metric (in seconds in my workspace):

let thresholdSec = 30;
AzureMetrics
| where TimeGenerated > ago(15m)
| where ResourceProvider =~ "MICROSOFT.SQL" and MetricName == "replication_lag_seconds"
| extend rid = tolower(_ResourceId)
| extend Server = extract(@"servers/([^/]+)", 1, rid),
         Db     = extract(@"databases/([^/]+)", 1, rid)
| summarize Lag = max(Maximum) by Server, Db
| where Lag > thresholdSec
| order by Lag desc

To spot an hourly pattern (a batch window inflating lag), I group by UTC hour:

AzureMetrics
| where TimeGenerated > ago(7d) and MetricName == "replication_lag_seconds"
| summarize Peak = max(Maximum) by Hour = hourofday(TimeGenerated)
| order by Hour asc

On a large estate, a peak that returns at the same hour is a work window, not a broken replication.

In master of the logical server, one query returns every link of the server with the local role:

SELECT d.name AS database_name, l.role_desc, l.partner_server,
       l.replication_state_desc
FROM sys.geo_replication_links AS l
JOIN sys.databases AS d ON d.database_id = l.database_id
ORDER BY d.name;

A database shown as SECONDARY on this server is not writable here. I collect this server by server and store the result in an inventory table refreshed often (see the pitfalls article: a lagging inventory is dangerous).

3. Databases missing from the group (T-SQL + PowerShell)

A database created after the group is not added to it automatically. In T-SQL, find databases without a link:

SELECT d.name
FROM sys.databases AS d
LEFT JOIN sys.geo_replication_links AS l ON l.database_id = d.database_id
WHERE d.database_id > 4 AND l.database_id IS NULL;

In PowerShell, compare the server's databases with the group's:

$inGroup = (Get-AzSqlDatabaseFailoverGroup -ResourceGroupName rg-dba-prd `
    -ServerName sql-contoso-prd-a -FailoverGroupName fog-contoso-app-01).DatabaseNames
Get-AzSqlDatabase -ResourceGroupName rg-dba-prd -ServerName sql-contoso-prd-a |
    Where-Object { $_.DatabaseName -ne 'master' -and $_.DatabaseName -notin $inGroup } |
    Select-Object DatabaseName

4. Alerting on a failover (KQL)

The activity log keeps a trace of failover operations. The operation names below are the ones I use; check them in your own log before turning this into a rule:

AzureActivity
| where TimeGenerated > ago(1h)
| where OperationNameValue has "FAILOVERGROUPS"
| where OperationNameValue has_any ("FAILOVER/ACTION", "FORCEFAILOVERALLOWDATALOSS/ACTION")
| project TimeGenerated, OperationNameValue, ActivityStatusValue, Caller, ResourceGroup, _ResourceId

An alert rule on this result (count > 0) warns me of a failover, wanted or not; a caller shown as "-" indicates a Microsoft-initiated action.

Thresholds

Band Lag Action
Normal < 5 s nothing
Warning 5 to 30 s watch
Degraded 30 s or more, sustained investigate (load, pool size)

Lessons learned

Mastodon LLIMastodon SocialFosstodon