Always on (AGL) Status Report

-----This script will show the status of the Alway On replication status

SELECT DISTINCT
primary_replica as 'Primary Server',
[endpoint_url] as 'End Point URL',
primary_recovery_health_desc as 'Primary Server Health Status',
secondary_recovery_health_desc as 'Secondary Server Health Status',
operational_state_desc as 'Operational State',
connected_state_desc as 'Connection State',
recovery_health_desc as 'Recovery Health',
synchronization_state_desc as 'Synchronization State',
database_state_desc as 'Database State',
JOIN_state_desc as 'Join State',
suspend_reason_desc as 'Suspended Reason',
availability_mode_desc as 'Availability Mode',
failover_mode_desc as 'Failover Mode',
primary_role_allow_connections_desc as 'Primary Connections Allowed',
secondary_role_allow_connections_desc as 'Secondary Connections Allowed',
create_date as 'Date Created',
modify_date as 'Date Modified',
[backup_priority] as 'Backup Priority',
role_desc as 'Role Type',
last_connect_error_description as 'Last Connection Error',
last_connect_error_timestamp as 'Last Connection Error Time',
last_sent_time as 'Last Data Send Time',
last_received_time as 'Last Data Recieved TIme',
last_hardened_time as 'Last Hardened Time',
last_redone_time as 'Last Redone Time',
log_send_queue_size as 'Log Send Queue Size',
log_send_rate as 'Log Send Rate',
redo_queue_size as 'Redo Queue Size',
redo_rate as 'Rate of Redo',
filestream_send_rate as 'Filestream Send Rate',
last_commit_time as ' Last Commit Time',
low_water_mark_for_ghosts as 'Low Water Mark for Ghosts'
FROM sys.dm_hadr_availability_group_states
JOIN sys.availability_replicas
ON sys.dm_hadr_availability_group_states.group_id = sys.availability_replicas.group_id
JOIN sys.dm_hadr_availability_replica_cluster_states
ON sys.dm_hadr_availability_group_states.group_id = sys.dm_hadr_availability_replica_cluster_states.group_id
JOIN sys.dm_hadr_availability_replica_states
ON sys.dm_hadr_availability_group_states.group_id = sys.dm_hadr_availability_replica_states.group_id
JOIN sys.dm_hadr_database_replica_states
ON sys.dm_hadr_availability_group_states.group_id = sys.dm_hadr_database_replica_states.group_id
WHERE operational_state_desc IS NOT NULL
AND database_state_desc IS NOT NULL
ORDER BY [endpoint_url] DESC

— Testing section
/*
— Suspend replication from primary
ALTER DATABASE [AdventureWorksLT2008] SET HADR SUSPEND
— Resume replication from secondary
ALTER DATABASE [AdventureWorksLT2008] SET HADR RESUME
— Force a manual failover of replication with data loss. MUST EXECUTE ON SECONDARY SQL SERVER
ALTER AVAILABILITY GROUP [AG-AdventureWorksLT2008] FORCE_FAILOVER_ALLOW_DATA_LOSS;
— Force a manual failover of replication with NO data loss MUST EXECUTE ON SECONDARY SQL SERVER
— YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
–Connect XXXXXX
ALTER AVAILABILITY GROUP [AG-AdventureWorksLT2008] FAILOVER;
GO
— YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
–:Connect XXXXXX
ALTER AVAILABILITY GROUP [AG-AdventureWorksLT2008] FAILOVER;
GO
*/