SQL query to fetch Availability group replicas failure readiness & estimated data loss from Solarwinds DPA database.
Is there any specific table I can look for to fetch the availability group replicas details.
Unfortunately, the data loss metric for the replica is queried as the page loads/refreshes and is not persisted in the database currently. You could setup a custom metric that queries the data from the sys.dm_hadr_database_replica_states using the last_sent_time (last time data was sent from primary) - last_received_time (last time the replica received data).
There are several tables that store AG information, and they all start with "CON_AG". The data loss metric you mention in stored in the CON_AG_DATABASE table in the EST_DATA_LOSS column. There are also columns like SYNC_STATUS, HEALTH, and IS_FAILOVER_READY that can give some of the other information as well. This table joins to the CON_AG_DATABASE and CON_AG_REPLICA table to provide the names of databases and replicas.
@darichar Thanks for these details. I already checked the CON_AG_DATABASE table, but it gives me estimate data loss for the databases. Is there a way to check the estimate data loss for replicas?
Is it the same thing, i.e. this metric is the possible data loss from the primary to secondary replicas? If you know of another number that's available, where does it come from?
I think as the table name suggests and from the data in CON_AG_DATABASE table it seems that it provides estimated data loss for the databases. Attached screenshot for reference.
Is there a way to check the estimate data loss for replicas?
