Is your database part of an Availability group? 5


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:

Let’s add the group name:

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:

 


Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

5 thoughts on “Is your database part of an Availability group?

  • DVP Rao

    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

    • Dimitri Post author

      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.

  • Matt P.

    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

    • Dimitri Post author

      It’s indeed another way to do it whose readability is good :-). Under which SQL Server version(s) did you create the query?