Sql-server – Join duplicating the data – SQL Server

availability-groupsdmvsql server

I am working with SQL Server Always On and I am querying the data as follows:

SELECT
name as AGname,
replica_server_name,
CASE WHEN  (primary_replica  = replica_server_name) THEN  1
ELSE  '0' END AS IsPrimaryServer
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
ORDER BY name

This gives me the output I need, however, I also need the DB name so I am trying the following:

SELECT
name as AGname,
DB_NAME(AG_DB.database_id) AS DatabaseName,
replica_server_name,
CASE WHEN  (primary_replica  = replica_server_name) THEN  1
ELSE  '0' END AS IsPrimaryServer
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
INNER JOIN master.sys.dm_hadr_database_replica_states AG_DB ON AG_DB.group_id = Groups.group_id
ORDER BY name

However, this is giving me issues as it is duplicating the information.

I also tried to fix this issue by doing the following:

SELECT
name as AGname,
(SELECT DISTINCT DB_NAME(AG_DB.database_id) FROM master.sys.dm_hadr_database_replica_states AG_DB WHERE AG_DB.group_id = Groups.group_id) AS DatabaseName,
replica_server_name,
CASE WHEN  (primary_replica  = replica_server_name) THEN  1
ELSE  '0' END AS IsPrimaryServer
FROM master.sys.availability_groups Groups
INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
ORDER BY name

The latter is giving me "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression" error.

Any suggestions how I can get the required output without the duplicate entries?

Best Answer

Read the definition of sys.dm_hadr_database_replica_states at https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql?view=sql-server-ver15 - you’ll notice that it says “On the primary replica, this view returns a row for each primary database and an additional row for the corresponding secondary database.”

I recommend you debug this by adding AG_DB.* to your select clause, to see what is in the rows you don’t want.

Related Question