List of SQL Server Databases in an Availability Group

May 23, 2014 / By Michelle Gutzait

Tags:

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

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>