December 5, 2016 at 10:35 am
I am working for a small company and have been placed in the roll of a DBA. I am a systems engineer with SQL knowledge. I know enough to be really dangerous. 🙂
I am not the original writer of this query. But I need to figure out how to make it work for my current need.
When I remove the where clause. I get the DB count from both servers. When I leave it, I only get the Groups that have databases. My end result is to list the group and count. whether a DB is present or not. see below
AAG Name Database count
AAG-test-01 1
AAG-test-02 0
select ag.Name, ag.Group_Id, agl.dns_name as 'ListenerName', COUNT(db.database_id) 'DatabaseCount'Â
from master.sys.availability_groups agÂ
inner join master.sys.availability_group_listeners agl on ag.group_id = agl.group_idÂ
left outer join sys.dm_hadr_database_replica_states db on db.group_id = ag.group_idÂ
where db.is_primary_replica = 1Â
group by ag.name, ag.group_id, agl.dns_name
I thank you kindly for any assistance and or explanation on how to accomplish this.
December 5, 2016 at 10:44 am
Something like this?
select
ag.Name, AGReplica.replica_server_name, ag.Group_Id, agl.dns_name as 'ListenerName', COUNT(db.database_id) 'DatabaseCount'
from
master.sys.availability_groups ag
left join
master.sys.availability_group_listeners agl
on ag.group_id = agl.group_id
left outer join
sys.dm_hadr_database_replica_states db
on db.group_id = ag.group_id
left join
sys.availability_replicas AGReplica
on AGReplica.group_id = ag.Group_Id
where db.is_primary_replica = 1
group by ag.name, ag.group_id, agl.dns_name, AGReplica.replica_server_name
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
December 5, 2016 at 11:07 am
It sounds like the is_primary_replica condition should be part of the JOIN criteria instead of in the WHERE clause.
select ag.Name, ag.Group_Id, agl.dns_name as 'ListenerName', COUNT(db.database_id) 'DatabaseCount'
from master.sys.availability_groups ag
inner join master.sys.availability_group_listeners agl on ag.group_id = agl.group_id
left outer join sys.dm_hadr_database_replica_states db on db.group_id = ag.group_id
AND db.is_primary_replica = 1 -- moved from WHERE clause
group by ag.name, ag.group_id, agl.dns_name
What is happening is that you have criteria on the outer table of your join, which essentially converts your OUTER JOIN to an INNER JOIN. By moving the criteria from the WHERE clause to the ON clause, you are preserving your OUTER JOIN
Drew
Edited to add an explanation.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 5, 2016 at 11:13 am
That was the answer! I cant thank you enough for the assist. I have been reading and beating me head on the desk. Never surprises me that the answer ends up being something so simple.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply