主页 / dba / 问题 / 210252
Accepted
Peter Vandivier
Asked: 2018-06-22 05:02:56 +0800 CST

列出主要副本和非 HA 数据库 - 不包括转发器副本

  • 772

当询问在任何给定服务器上哪些数据库是可写的时，我一直在使用下面的代码片段来列出PRIMARY副本数据库和不参与 AG 的数据库。

既然我已经部署了分布式可用性组，它就无法正常工作了！由于本地转发器在 DMV 上报告为PRIMARY副本，所以sys.dm_hadr_availability_replica_states我的旧查询告诉我数据库是可写的，而实际上是不可写的。

如何更改此查询以排除本地转发器副本中的数据库？

select 
    ars.role_desc
   ,ag_name = ag.[name]
   ,adc.[database_name]
from sys.availability_groups ag 
join sys.dm_hadr_availability_replica_states ars on ars.group_id = ag.group_id
join sys.availability_databases_cluster adc on adc.group_id = ag.group_id
where ars.is_local = 1
    and ars.role_desc = 'PRIMARY'
union all 
select 
    'LOCAL_ONLY'
   ,@@servername
   ,[name]
from sys.databases d 
where not exists (
    select 1 
    from sys.availability_databases_cluster adc
    where adc.[database_name] = d.[name]
);

……或者更简单的……

如何列出所有本地转发器数据库？

sql-server availability-groups

1 个回答

  1. Best Answer

    列出当前服务器上转发器副本中的所有数据库...

    select 
     dag.group_id
    ,dag.[name]
    ,dag.is_distributed
    ,fwd.replica_id
    ,fwd.replica_server_name
    ,adc.[database_name]
from sys.availability_groups dag 
join sys.availability_replicas fwd on fwd.group_id = dag.group_id
join sys.availability_groups ag on ag.name = fwd.replica_server_name
join sys.availability_databases_cluster adc on adc.group_id = ag.group_id
where dag.is_distributed = 1;

    列出所有数据库PRIMARY或仅本地数据库并排除转发器数据库...

    select 
    ars.role_desc
   ,ag_name = ag.[name]
   ,adc.[database_name]
from sys.availability_groups ag 
join sys.dm_hadr_availability_replica_states ars on ars.group_id = ag.group_id
join sys.availability_databases_cluster adc on adc.group_id = ag.group_id
where ars.is_local = 1
    and ars.role_desc = 'PRIMARY'
    and not exists (
        select 1
        from sys.availability_groups dag 
        join sys.availability_replicas fwd on fwd.group_id = dag.group_id
        join sys.availability_groups ag2 on ag2.name = fwd.replica_server_name
        join sys.availability_databases_cluster db on db.group_id = ag2.group_id
        where dag.is_distributed = 1
            and db.[database_name] = adc.[database_name]
)
union all 
select 
    'LOCAL_ONLY'
   ,@@servername
   ,[name]
from sys.databases d 
where not exists (
    select 1 
    from sys.availability_databases_cluster adc
    where adc.[database_name] = d.[name]
);
    • 3

相关问题