One of my replica in the alwayson is down for several minutes. My question is, for how long the secondary replica can be down ? can I get it from dmv table or somewhere else ? 10X
The second replica can be done forever no issues. However on your primary replica the log file size will keep growing because the other replica failed to "harvest" the log.
As to monitor the status of replicas, you can use this script and create alert jobs based on the result:
SELECT
c.database_name,
g.name AS ag_name,
s.is_local,
s.is_primary_replica,
s.synchronization_state_desc,
s.is_commit_participant,
s.synchronization_health_desc,
s.recovery_lsn,
s.truncation_lsn,
s.last_sent_lsn,
s.last_sent_time,
s.last_received_lsn,
s.last_received_time,
s.last_hardened_lsn,
s.last_hardened_time,
s.last_redone_lsn,
s.last_redone_time,
s.log_send_queue_size,
s.log_send_rate,
s.redo_queue_size,
s.redo_rate,
s.filestream_send_rate,
s.end_of_log_lsn,
s.last_commit_lsn,
s.last_commit_time,
r.replica_server_name
FROM sys.dm_hadr_database_replica_states AS s
INNER JOIN sys.availability_databases_cluster AS c
ON s.group_id = c.group_id AND
s.group_database_id = c.group_database_id
INNER JOIN sys.availability_groups AS g
ON g.group_id = s.group_id
INNER JOIN sys.availability_replicas AS r
ON s.group_id = r.group_id AND
s.replica_id = r.replica_id
ORDER BY
g.name,
r.replica_server_name,
c.database_name;