Dimitri's Wanderings

Is your database part of an Availability group?

 
  • Your Name
  • Jul 08, 2016

Today’s post is about answering a simple question I had to document a SQL Server instance. Is your database part of an Availability group? Or is it purely local to your instance?

Microsoft has of course put some Dynamic Management Views (DMV) into SQL Server to list the databases in a group. However there is no simple DMV to take the problem the reverse way. In addition, as we’ll see some Availability group related DMV list databases more than once!

We will use the following DMVs:

It is worthwhile to note that if a database isn’t replicated using an Availability group, it won’t appear in any *hadr* DMV. Therefore we will use an OUTER JOIN based on the contents of sys.databases to be sure not to forget any database. A NULL value will then indicate that there is nothing found, so that this database isn’t part of any group.

Here’s our first attempt:

select  sd.name, 
(
case 
 when
  hdrs.is_primary_replica IS NULL then  'NOT REPLICATED'
 else
    'REPLICATED'
 end
) as  AGType
 from sys.databases as sd
 left outer join sys.dm_hadr_database_replica_states  as hdrs on hdrs.database_id = sd.database_id

Let’s add the group name:

select sd.name, 
(
case 
 when
  hdrs.is_primary_replica IS NULL then  'NOT REPLICATED'
 else
    'REPLICATED'
 end
) as  AGType,
COALESCE(grp.ag_name,'N/A') as AGName
 from sys.databases as sd
 left outer join sys.dm_hadr_database_replica_states  as hdrs on hdrs.database_id = sd.database_id
 left outer join sys.dm_hadr_name_id_map as grp on grp.ag_id = hdrs.group_id

The last part is to get to know how sys.dm_hadr_database_replica_states work. The DMV doesn’t simply return you a row per database outling the status. It does return one row when you’re on a secondary. However on a primary, it returns one row for the primary, and a row per secondary flagging with is_local to 0 or is_primary_replica to 0. So we will filter by knowing if for each database there is a is_primary_replica to 1 row and eliminating duplicates with a DISTINCT clause.

Here’s the final code:

-- Get for a each database a row with:
-- name, AGType, AGName
-- name: name of the database
-- AGType: NOT REPLICATED/PRIMARY/SECONDARY
-- AGName: N/A if NOT REPLICATED, otherwise name of the AG Group the database is part of

select DISTINCT sd.name, 
(
case 
 when
  hdrs.is_primary_replica IS NULL then  'NOT REPLICATED'
 when exists ( select * from sys.dm_hadr_database_replica_states as irs where sd.database_id = irs.database_id and is_primary_replica = 1 ) then
	'PRIMARY'
 else
    'SECONDARY'
 end
) as  AGType,
COALESCE(grp.ag_name,'N/A') as AGName
 from sys.databases as sd
 left outer join sys.dm_hadr_database_replica_states  as hdrs on hdrs.database_id = sd.database_id
 left outer join sys.dm_hadr_name_id_map as grp on grp.ag_id = hdrs.group_id
Search