Quick access to SQL Server Always On Availability cheatsheet

These are queries to manage SQL Server AlwaysOn Availability Groups  

Using sys.fn_hadr_is_primary_replica to identify if the database is primary replica

SELECT sys.fn_hadr_is_primary_replica ('my_ag_db');  

Display All availability groups visible to this server when the  Server is the Primary replica

SELECT Groups.[Name] AS AGname
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
WHERE primary_replica = @@Servername;

Display All availability groups visible to this server when this Server is a Secondary replica

SELECT Groups.[Name] AS AGname
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
WHERE primary_replica != @@Servername;

Display All Databases Across all Availability groups within the Cluster 

SELECT  Groups.[name] AS AGName ,
Databaselist.[database_name] AS DatabaseName
FROM    sys.availability_databases_cluster Databaselist
INNER JOIN sys.availability_groups_cluster Groups ON Databaselist.group_id = Groups.group_id
ORDER BY
AGName ,
DatabaseName;

How to Check which replicas have read only config in place to allow them to be readable when set within an Availability Group

SELECT
PrimaryServer.replica_server_name AS PrimaryServer,
Groups.name AS AGname,
ReadOnlyReplica.replica_server_name AS ReadOnlyReplica,
ReadOnlyReplica.read_only_routing_url AS RoutingURL,
RoutingList.routing_priority AS RoutingPriority
FROM sys.availability_read_only_routing_lists RoutingList
INNER JOIN sys.availability_replicas PrimaryServer ON RoutingList.replica_id = PrimaryServer.replica_id
INNER JOIN sys.availability_replicas ReadOnlyReplica ON RoutingList.read_only_replica_id = ReadOnlyReplica.replica_id
INNER JOIN sys.availability_groups Groups ON Groups.group_id = PrimaryServer.group_id
WHERE PrimaryServer.replica_server_name != ReadOnlyReplica.replica_server_name
ORDER BY
PrimaryServer ASC,
AGname ASC

Availability Group Backup preference 

SELECT
name AS AGname,
automated_backup_preference_desc
FROM sys.availability_groups;

How to check if Availability Groups is  synchronized

--check AG group is synchronised
SELECT 
	ar.replica_server_name, 
	adc.database_name, 
	ag.name AS ag_name, 
	drs.is_local, 
	drs.is_primary_replica, 
	drs.synchronization_state_desc, 
	drs.is_commit_participant, 
	drs.synchronization_health_desc, 
	drs.recovery_lsn, 
	drs.truncation_lsn, 
	drs.last_sent_lsn, 
	drs.last_sent_time, 
	drs.last_received_lsn, 
	drs.last_received_time, 
	drs.last_hardened_lsn, 
	drs.last_hardened_time, 
	drs.last_redone_lsn, 
	drs.last_redone_time, 
	drs.log_send_queue_size, 
	drs.log_send_rate, 
	drs.redo_queue_size, 
	drs.redo_rate, 
	drs.filestream_send_rate, 
	drs.end_of_log_lsn, 
	drs.last_commit_lsn, 
	drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc 
	ON drs.group_id = adc.group_id AND 
	drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
	ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar 
	ON drs.group_id = ar.group_id AND 
	drs.replica_id = ar.replica_id
ORDER BY 
	ag.name, 
	ar.replica_server_name, 
	adc.database_name;

How to To manually failover - on target secondary replica

ALTER AVAILABILITY GROUP SQL_AG1 FAILOVER