Primary and secondary placed on same host

In case of a host- or connection failure, it is not advised to have both the primary- and secondary server in an availability group on the same physical host.

To query the Performance Store database for servers where the primary and secondary are placed on the same host, use:

select s.*
from
(
    select distinct d1.server_name, d1.availability_group_name, d1.availability_group_id
    , count(*) over (partition by d1.group_database_id) groups
    from dbo.v_databases d1
	inner join dbo.v_servers s on s.server_name = d1.server_name
    where d1.availability_group_id in
    (
        select d.availability_group_id
        from dbo.v_servers s
        inner join dbo.v_databases d on d.server_name = s.server_name
        where d.availability_group_id is not null
        and s.is_production = 1
        and s.host_name is not null
        group by d.availability_group_id
        having count(distinct s.host_name) = 1
    )
	and s.host_name is not null
) s
where s.groups > 1

The alert will be visible on the Latest stats dashboard.

To exclude specific servers from this alert, see how to use the dbo.team_alert_exclude table in Alerting.