始終在儀表板上出現錯誤

我有一個 Windows Server 故障轉移群集設置，我正在使用 SQL Server 服務和一個 DC、一個主伺服器和一個輔助伺服器。

在使用 SQL Server Management Studio 期間，當我點擊“始終處於高可用性”下的顯示儀表板時，它顯示該錯誤，即

始終在儀表板上出現錯誤

如何解決這個問題？

簡而言之

該問題是由於儀表板執行的查詢引用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 列。

引用自：https://dba.stackexchange.com/questions/234876