Add Support for Monitoring Backups in Availability Groups (AG) and Distributed AG

[aachaemenes]

Currently, dbadash does not fully support monitoring backups in environments with Availability Groups (AG) and Distributed Availability Groups (DAG). In scenarios where backups are taken on secondary nodes, dbadash marks the backup on the primary database as missing, even though a valid backup exists on one of the secondaries.

Request:

Enhance dbadash to correctly identify and monitor backups taken on secondary replicas within an AG or DAG. Ensure that the monitoring logic checks all replicas to see where the last backup was taken and considers that as a valid backup for the AG/DAG, preventing false positives where the primary database is incorrectly flagged as missing a backup. This feature would be valuable for users managing complex SQL Server environments with AG and DAG setups, ensuring accurate backup monitoring across all nodes.

[DavidWiseman]

It should already work like this. If the backup is performed on another node in the AG, the Is Partner Backup column will have a check. I don't have a DAG to confirm if it works with DAGs.

[aachaemenes]

problem is, that column is always empty on primary and secondary nodes for regular AG and DAG. I have deployed it over 300 AGs in different envs and never shows anything.

[aachaemenes]

nice. it turns out that I have to double click on each cell to make that is partner backup checkbox visible. Is there a reset view to show everything? that would be difficult to go through all the servers.

[DavidWiseman]

The checkbox should always be visible. Can you paste a screenshot so I can see what's happening?

If the backup reporting isn't taking into account backups performed on each node in the AG: Ensure the following collections are enabled (they should be by default):

• DatabasesHADR
• Databases
• Backups You can check the Collection Dates tab in the checks node to see when the collections last ran. Also, the DBA Dash ErrorLog tab might show any issues related to collecting this data.

The LastBackup view uses this query in a CTE:

SELECT D.DatabaseID,
        partnr.DatabaseID BackupDatabaseID,
        partnr.DatabaseID PartnerDatabaseID
    FROM dbo.Databases D
    JOIN dbo.DatabasesHADR hadr ON D.DatabaseID = hadr.DatabaseID
    JOIN dbo.DatabasesHADR partnr ON hadr.group_database_id = partnr.group_database_id AND D.DatabaseID <> partnr.DatabaseID 
    WHERE hadr.is_local=1
    AND partnr.is_local=1
    UNION ALL
    SELECT D.DatabaseID,
        D.DatabaseID,
        NULL as PartnerDatabaseID
    FROM dbo.Databases D

The DatabaseHADR table is used to get the latest backup for all the databases with the same group_database_id. You could start with this view to troubleshoot the issue. This is used by a BackupStatus view which is used by the Backups_Get stored procedure that returns the data to the GUI.

Hope this helps.

[aachaemenes]

for distributed AG i have to create exception to works. it wont see it automatically as part of the partner. i guess lower priority issue.