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:
- The well known sys.databases table
- The sys.dm_hadr_name_id_map DMV which gives the name to internal ID translation for each availability group
- The sys.dm_hadr_database_replica_states which gives the status of databases in the availability groups an instance is part of
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- 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 |
Very useful.
one question. why we do not get all DBs listed if we connect to a listener and execute sp_helpdb ? where as select name ofmr sys.databases give a full list. I were trying to get a DB disk space sizing report with these
If you look at the source code of sp_helpdb, you’ll see that the databases where has_dbaccess() returns something else than 1 (“user can access”) are removed from the results.
I found I could achieve the same result without the need to use “disctinct” (which I always dislike)
by simply doing the right join from sys.database (it has the replica_id of the “local” DB) in this way:
select
sd.name,
coalesce(grp.ag_name,'N/A'),
case
when reps.is_primary_replica = 1 then 'PRIMARY'
when reps.is_primary_replica = 0 then 'SECONDARY'
else 'NOT REPLICATED'
end as AGType
from sys.databases as sd
left outer join sys.dm_hadr_database_replica_states as reps
on reps.database_id = sd.database_id
and reps.replica_id = sd.replica_id
left outer join sys.dm_hadr_name_id_map as grp
on grp.ag_id = reps.group_id
order by name
It’s indeed another way to do it whose readability is good :-). Under which SQL Server version(s) did you create the query?
Pingback: How to run a script on all databases that are either AG primary or standalone in SQL instance - WordsOnTech