Quick access to SQL Server Always On Availability cheatsheet

12 January,2021 by Jack Vamvas

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

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Quick access to SQL Server Always On Availability cheatsheet


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer