Is your database part of an Availability group?

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.