dutyDBA.com

The built-in Always On dashboard in SQL Server Management Studio (SSMS) only shows the synchronisation status of normal Always On Availability Groups (AG). This dashboard does not show the synchronisation and health status of the Distributed Always On Availability Groups.

What is a Distributed Always On Availability Group?

In short, it is an advanced SQL Server availability group configuration that links two separate availability groups together. The participating availability groups can be located anywhere—on-premises, in the cloud, physical or virtual environments, across different domains, and even across different operating systems (such as Windows and Linux). As long as the two availability groups can communicate with each other, they can be configured as a distributed availability group.

How to get the health and synchronisation status for a Distributed Always On Availability Group?

As SSMS Always On dashboard does not show information about the Distributed Always On Availability Groups, how do you go about accessing this information? You have to query the Always On related Dynamic Management Views (DMVs) to get this information.

The distributed AG health information is required to check whether your distributed availability group is fully in sych or not. This is especially required when performing Disaster Recovery tests, DR failovers, OS patching, SQL Server service pack or cummulative updates, power up/power down activities etc.

Which DMVs to query to get this information?

In this post I am going to present a query, that shows detailed information about your Distributed Always On Availability Grouo setup. This query is based on information from the below Dynamic Management Views (DMVs):

What permissions are required?

Users using SQL Server 2019 (15.x) and earlier versions require VIEW SERVER STATE permission. SQL Server 2022 (16.x) and later versions require VIEW SERVER PERFORMANCE STATE permission.

Now show me the query!

Here’ the query.

To get all the details about your Distributed Always On Availability Group, you have to run the below query on your Global Primary replica – that is, the primary replica of the primary avaiability group. If you run this query on the Forwarder or any other secondary replica, you won’t get the full picture.

SELECT
    DB_NAME(dhdrs.database_id) AS DatabaseName,
    ag.name AS AvailabilityGroupName,
    ar.replica_server_name AS ReplicaName,
    dhars.role_desc AS ReplicaRole,
    dhars.connected_state_desc AS ReplicaConnectionState,
    dhars.synchronization_health_desc AS ReplicaSynchHealthState,
    ar.availability_mode_desc AS CommitMode,
    dhdrs.synchronization_state_desc AS DBSynchState,
    dhdrs.synchronization_health_desc AS DBSynchHealthState,
    dhdrs.last_hardened_lsn AS LastHardenedLSN,
    dhdrs.last_hardened_time AS LastHardenedTime
FROM
    sys.dm_hadr_database_replica_states AS dhdrs
    INNER JOIN
    sys.dm_hadr_availability_replica_states AS dhars
    ON  dhdrs.replica_id = dhars.replica_id
        AND dhdrs.group_id = dhars.group_id
    INNER JOIN
    sys.availability_groups AS ag
    ON  dhdrs.group_id = ag.group_id
    INNER JOIN
    sys.availability_replicas AS ar
    ON  ar.replica_id = dhdrs.replica_id
ORDER BY
    DatabaseName,
    dhars.role_desc,
    ag.name,
    ar.replica_server_name

SELECT
    DB_NAME(dhdrs.database_id) AS DatabaseName,
    ag.name AS AvailabilityGroupName,
    ar.replica_server_name AS ReplicaName,
    dhars.role_desc AS ReplicaRole,
    dhars.connected_state_desc AS ReplicaConnectionState,
    dhars.synchronization_health_desc AS ReplicaSynchHealthState,
    ar.availability_mode_desc AS CommitMode,
    dhdrs.synchronization_state_desc AS DBSynchState,
    dhdrs.synchronization_health_desc AS DBSynchHealthState,
    dhdrs.last_hardened_lsn AS LastHardenedLSN,
    dhdrs.last_hardened_time AS LastHardenedTime
FROM
    sys.dm_hadr_database_replica_states AS dhdrs
    INNER JOIN
    sys.dm_hadr_availability_replica_states AS dhars
    ON  dhdrs.replica_id = dhars.replica_id
        AND dhdrs.group_id = dhars.group_id
    INNER JOIN
    sys.availability_groups AS ag
    ON  dhdrs.group_id = ag.group_id
    INNER JOIN
    sys.availability_replicas AS ar
    ON  ar.replica_id = dhdrs.replica_id
ORDER BY
    DatabaseName,
    dhars.role_desc,
    ag.name,
    ar.replica_server_name

This is how the output looks like:

What to look out for in the query output?

The above query returns the following columns:

Hope you find this useful. Feel free to share your thoughts by leaivng a comment.