Post Updated: Replaced Query 3 with transactions/sec query.

When we first published 7 ways to Query Always On Availability Groups using SQL we had no idea it would so popular! So here is a quick post with 7 more ways to query Always on availability groups using TSQL,  its always handy to have a few little snippets like these stashed away for when you need them!

Check which replicas have read only config in place to allow them to be readable when set within an AG/s:

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

Is this server a primary server for any availability group?

SELECT [Groups].[name]
FROM sys.dm_hadr_availability_group_states States
INNER JOIN sys.availability_groups Groups ON States.group_id = Groups.group_id
WHERE primary_replica = @@Servername

Total Transactions/sec and Write transactions/sec per Availability group in a 15 second snapshot.

SET NOCOUNT ON;

IF OBJECT_ID('tempdb.dbo.#performance_counters') IS NOT NULL
DROP TABLE #performance_counters;

CREATE TABLE #performance_counters (
DatetimeChecked DATETIME,
instance_name NVARCHAR(128),
counter_name NVARCHAR(128),
cntr_value BIGINT
);

INSERT INTO #performance_counters (DatetimeChecked,instance_name,counter_name,cntr_value)
SELECT
GETDATE() AS DatetimeChecked,
instance_name,
counter_name,
cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Write Transactions/sec','Transactions/sec')
AND instance_name != '_Total'

--Wait for 15 seconds then get the deltas
WAITFOR DELAY '00:00:15';

SELECT
Groups.name AS AGname,
PerSecondDeltas.counter_name,
SUM(cntr_delta_per_second) AS Total_per_second
FROM
(
SELECT
PerfmonNow.instance_name,
PerfmonNow.counter_name,
PerfmonNow.cntr_value
,CAST((PerfmonNow.cntr_value - PerfmonSnapShot.cntr_value) * 1.0 / DATEDIFF(SECOND, PerfmonSnapShot.DatetimeChecked, GETDATE()) AS MONEY) AS cntr_delta_per_second
FROM sys.dm_os_performance_counters PerfmonNow
INNER JOIN #performance_counters PerfmonSnapShot ON PerfmonNow.instance_name = PerfmonSnapShot.instance_name
AND PerfmonNow.counter_name = PerfmonSnapShot.counter_name
WHERE PerfmonNow.counter_name IN ('Write Transactions/sec','Transactions/sec')
AND PerfmonNow.instance_name != '_Total'
) PerSecondDeltas
INNER JOIN sys.availability_databases_cluster AGDatabases ON PerSecondDeltas.instance_name = AGDatabases.database_name
INNER JOIN sys.availability_groups Groups ON AGDatabases.group_id = Groups.group_id
GROUP BY Groups.name,counter_name
ORDER BY
Groups.name ASC,
counter_name ASC

How many databases are there in each availability group on this server?

SELECT
Groups.name,
COUNT([AGDatabases].[database_name]) AS DatabasesInAG
FROM master.sys.availability_groups Groups
INNER JOIN Sys.availability_databases_cluster AGDatabases ON Groups.group_id = AGDatabases.group_id
GROUP BY Groups.name
ORDER BY Groups.name ASC

Total Database size in each availability group on this server?

SELECT
Groups.name,
SUM(CAST((CAST([master_files]. AS BIGINT )*8) AS MONEY)/1024/1024) AS TotalDBSize_GB
FROM master.sys.availability_groups Groups
INNER JOIN Sys.availability_databases_cluster AGDatabases ON Groups.group_id = AGDatabases.group_id
INNER JOIN sys.databases ON AGDatabases.database_name = databases.name
INNER JOIN sys.master_files ON databases.database_id = master_files.database_id
GROUP BY Groups.name
ORDER BY Groups.name ASC

Check Availability group health and whether a database is suspended.

SELECT DISTINCT
Groups.name AS AGname,
Replicas.replica_server_name,
States.role_desc,
States.synchronization_health_desc,
ISNULL(ReplicaStates.suspend_reason_desc,'N/A') AS suspend_reason_desc
FROM sys.availability_groups Groups
INNER JOIN sys.dm_hadr_availability_replica_states as States ON States.group_id = Groups.group_id
INNER JOIN sys.availability_replicas as Replicas ON States.replica_id = Replicas.replica_id
INNER JOIN sys.dm_hadr_database_replica_states as ReplicaStates ON Replicas.replica_id = ReplicaStates.replica_id

Set Availability group backup preference.

USE [master];

--Set Backup preference to Primary replica only
ALTER AVAILABILITY GROUP [AG name here] SET(AUTOMATED_BACKUP_PREFERENCE = PRIMARY);

--Set Backup preference to Secondary only
ALTER AVAILABILITY GROUP [AG name here] SET(AUTOMATED_BACKUP_PREFERENCE = SECONDARY_ONLY);

--Set Backup preference to Prefer secondary
ALTER AVAILABILITY GROUP [AG name here] SET(AUTOMATED_BACKUP_PREFERENCE = SECONDARY);

--Set Backup preference to Any replica (no preference)
ALTER AVAILABILITY GROUP [AG name here] SET(AUTOMATED_BACKUP_PREFERENCE = NONE);

--Backup preference via TSQL can be found here
SELECT
name AS AGname,
automated_backup_preference_desc
FROM sys.availability_groups;

Thanks for reading.