始终在仪表板上出现错误

简而言之

该问题是由于仪表板执行的查询引用required_copies_to_commit了master.sys.availability_groupsdmv 中已更改为required_synchronized_secondaries_to_commit.

解决方案是升级 SSMS（在 17.4 版本上进行测试）。

SSMS 17.0

在安装了 SSMS 17.0 的机器上重试时

出现同样的错误：

无效的列名required_copies_to_commit

这是由于在 17.0 上打开仪表板时查询不同：

      select * into #tmpag_availability_groups from master.sys.availability_groups
    ...
    SELECT
    CAST(
            serverproperty(N'Servername')
           AS sysname) AS [Server_Name],
    AG.name AS [Name],
    ISNULL(AG.automated_backup_preference, 4) AS [AutomatedBackupPreference],
    ISNULL(AG.failure_condition_level, 6) AS [FailureConditionLevel],
    ISNULL(AG.health_check_timeout, -1) AS [HealthCheckTimeout],
    AR2.replica_metadata_id AS [ID],
    ISNULL(arstates2.role, 3) AS [LocalReplicaRole],
    ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
    AG.group_id AS [UniqueId],
    ISNULL(AG.basic_features, 0) AS [BasicAvailabilityGroup],
    ISNULL(AG.db_failover, 0) AS [DatabaseHealthTrigger],
    ISNULL(AG.dtc_support, 0) AS [DtcSupportEnabled],
    ISNULL(AG.is_distributed, 1) AS [IsDistributedAvailabilityGroup],
    ISNULL(AG.cluster_type, 0) AS [ClusterType],
    ISNULL(AG.required_copies_to_commit, 0) AS [RequiredCopiesToCommit]
    FROM
    #tmpag_availability_groups AS AG
    LEFT OUTER JOIN #tmpag_availability_group_states as agstates ON AG.group_id = agstates.group_id
    INNER JOIN #tmpag_availability_replicas AS AR2 ON AG.group_id = AR2.group_id
    INNER JOIN #tmpag_availability_replica_states AS arstates2 ON AR2.replica_id = arstates2.replica_id AND arstates2.is_local = 1
    ORDER BY
    [Name] ASC
   ...

使用了不再存在的列

ISNULL(AG.required_copies_to_commit, 0) AS [RequiredCopiesToCommit]

而不是较新版本的 SSMS 上的正确列：

ISNULL(AG.required_synchronized_secondaries_to_commit, 0) AS [RequiredSynchronizedSecondariesToCommit]

SSMS 17.4

免责声明：版本 17.1、17.2 和 17.3 也可能显示正确的仪表板。我还没有测试这些

在仪表板正确打开时跟踪由 ssms（17.4 或更高版本）运行的查询时，此查询运行

select * into #tmpag_availability_groups from master.sys.availability_groups
        ...    
SELECT
    CAST(
            serverproperty(N'Servername')
           AS sysname) AS [Server_Name],
    AG.name AS [Name],
    ISNULL(AG.automated_backup_preference, 4) AS [AutomatedBackupPreference],
    ISNULL(AG.failure_condition_level, 6) AS [FailureConditionLevel],
    ISNULL(AG.health_check_timeout, -1) AS [HealthCheckTimeout],
    AR2.replica_metadata_id AS [ID],
    ISNULL(arstates2.role, 3) AS [LocalReplicaRole],
    ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
    AG.group_id AS [UniqueId],
    CAST(ISNULL(AG.basic_features, 0) AS bit) AS [BasicAvailabilityGroup],
    CAST(ISNULL(AG.db_failover, 0) AS bit) AS [DatabaseHealthTrigger],
    CAST(ISNULL(AG.dtc_support, 0) AS bit) AS [DtcSupportEnabled],
    CAST(ISNULL(AG.is_distributed, 0) AS bit) AS [IsDistributedAvailabilityGroup],
    ISNULL(AG.cluster_type, 0) AS [ClusterType],
    ISNULL(AG.required_synchronized_secondaries_to_commit, 0) AS [RequiredSynchronizedSecondariesToCommit]
    FROM
    #tmpag_availability_groups AS AG
    LEFT OUTER JOIN #tmpag_availability_group_states as agstates ON AG.group_id = agstates.group_id
    INNER JOIN #tmpag_availability_replicas AS AR2 ON AG.group_id = AR2.group_id
    INNER JOIN #tmpag_availability_replica_states AS arstates2 ON AR2.replica_id = arstates2.replica_id AND arstates2.is_local = 1
    ORDER BY
    [Name] ASC
...


 select * into #tmpag_availability_groups from master.sys.availability_groups

SSMS 版本

(17.4)

作为旁注，其中一些列尚未添加到sys.availability_groups dmv 参考

最后，仪表板正确打开。

由于使用了正确的列名

required_synchronized_secondaries_to_commit 

解决方案

您必须升级到 SSMS 的更高版本（17.4为我工作）才能在查询中获取正确和现有的 DMV 列。