List of SQL Server Databases in an Availability Group

After migrating some databases to SQL Server 2012 and creating Availability Groups for some databases, we have noticed that some of our maintenance scripts were failing with the following error:

The target database (‘MyDB1’) is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

Databases that are part of an Availability group and play the secondary role can be read-only replica and therefore are not writable.

Those databases cannot be part of some of the out-of-the-box maintenance plans or other coded maintenance procedures.

For the out-of-the-box Maintenance Plans, there is an option in the Plan to choose the preferred replica. (Link)

Any code that requires writes in the database will not work.

How do we get the list of those databases so that we can exclude them/include them in our code?

So first,

How do we know that this instance is part of Availability Group(s):

SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC

Results:

Name	PrimaryReplicaServerName	LocalReplicaRole (1=primary,2=secondary,3=none)
AvGroup_1	MyServer01	2
AvGroup_2	MyServer02	1

Secondly,

How do we get some information about the databases in the Availability Group:

SELECT
AG.name AS [AvailabilityGroupName],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole],
dbcs.database_name AS [DatabaseName],
ISNULL(dbrs.synchronization_state, 0) AS [SynchronizationState],
ISNULL(dbrs.is_suspended, 0) AS [IsSuspended],
ISNULL(dbcs.is_database_joined, 0) AS [IsJoined]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
ORDER BY AG.name ASC, dbcs.database_name

Results:

AvailabilityGroupName	PrimaryReplicaServerName	LocalReplicaRole	DatabaseName	SynchronizationState	IsSuspended	IsJoined
AvGroup_1	MyServer01	2	MyDB1	2	0	1
AvGroup_1	MyServer01	2	MyDB2	2	0	1
AvGroup_1	MyServer01	2	MyDB3	2	0	1

So, for example,

If we would like to get the databases that are secondary in the Availability Group,  to be excluded when writes are required:

SELECT DISTINCT
dbcs.database_name AS [DatabaseName]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
ON arstates.replica_id = dbcs.replica_id
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id
WHERE ISNULL(arstates.role, 3) = 2 AND ISNULL(dbcs.is_database_joined, 0) = 1
ORDER BY  dbcs.database_name

Results:

DatabaseName

MyDB1

MyDB2

MyDB3

Related Links:

Monitor Availability Groups (Transact-SQL)

AlwaysOn Availability Groups Dynamic Management Views and Functions (Transact-SQL)

AlwaysOn Availability Groups Connectivity Cheat Sheet (describing types of AG replicas and if connection or reads are allowed on secondary DB)

Backups on secondary replicas – Always ON Availability Groups