SQL Server 2017可用性组场景下，如何限制sys.databases仅返回可连接数据库？

嘿，在SQL Server可用性组（AG）架构下遇到这个问题太正常了——毕竟副本节点上的sys.databases会列出AG里所有数据库的副本，其中不少是处于不可连接或者未就绪状态的，完全不符合你的需求对吧？下面给你一套精准的解决方案：

核心过滤查询 #

这个查询会帮你筛选出真正可连接、处于可用状态的数据库，不管是主副本还是允许访问的只读副本：

SELECT 
    name, 
    state,
    state_desc,
    is_read_only
FROM sys.databases
LEFT JOIN sys.dm_hadr_database_replica_states drs 
    ON sys.databases.database_id = drs.database_id
WHERE 
    -- 可选：排除master、model、msdb、tempdb这四个系统库，按需调整
    database_id > 4
    -- 只保留在线状态的数据库，排除还原、恢复、离线等不可连接状态
    AND state = 0 
    AND (
        -- 非AG架构的普通数据库直接保留
        drs.replica_id IS NULL
        -- AG数据库：区分主副本和可访问的副本节点
        OR (
            drs.replica_id IS NOT NULL 
            AND (
                -- 主副本上的数据库，支持读写连接
                drs.role_desc = 'PRIMARY'
                -- 副本节点：允许外部连接，且数据库处于只读可用状态
                OR (
                    drs.role_desc = 'SECONDARY' 
                    AND sys.databases.is_read_only = 1 
                    AND drs.secondary_role_allow_connections_desc IN ('ALL', 'READ_ONLY')
                )
            )
        )
    )

关键条件解释 #

• database_id > 4：如果你不需要显示系统库，可以保留这个条件；如果要包含系统库，直接删掉就行。
• state = 0：对应state_desc = 'ONLINE'，确保数据库处于可连接的在线状态。
• drs.replica_id IS NULL：照顾那些不在AG里的普通数据库，保证原有非AG场景的兼容性。
• 针对AG数据库的逻辑：
  • 主副本的数据库天然支持读写连接，直接保留。
  • 副本节点需要满足两个条件：一是副本配置允许外部连接（secondary_role_allow_connections_desc设为ALL或READ_ONLY），二是数据库本身处于只读可用状态（is_read_only = 1）。

进阶优化（可选） #

如果你的环境有异步提交模式的副本，建议加上同步状态过滤，避免连接到未完全同步的数据库：

SELECT 
    name, 
    state,
    state_desc,
    is_read_only,
    synchronization_state_desc
FROM sys.databases
LEFT JOIN sys.dm_hadr_database_replica_states drs 
    ON sys.databases.database_id = drs.database_id
WHERE 
    database_id > 4
    AND state = 0 
    AND (
        drs.replica_id IS NULL
        OR (
            drs.role_desc = 'PRIMARY'
            OR (
                drs.role_desc = 'SECONDARY' 
                AND sys.databases.is_read_only = 1 
                AND drs.secondary_role_allow_connections_desc IN ('ALL', 'READ_ONLY')
                -- 确保副本数据已同步完成
                AND drs.synchronization_state_desc = 'SYNCHRONIZED'
            )
        )
    )

这样调整后，返回的结果就是所有用户可以正常连接并执行操作（读写或只读，取决于副本角色）的数据库了。

内容的提问来源于stack exchange，提问作者Stephan Kallnik