An error occurred on the always on dashboard I have a setup of Windows Server Failover Cluster and i am using SQL Server Service with one DC, One Primary Server and One Secondary Server. 

During Using SQL Server Management Studio, when i click at Show Dashboard Under "Always On High Availability", It shows that error i.e.

An error occurred on the always on dashboard

How to resolve this issue ?
In short

The issue is due to the dashboard executing queries referencing the required_copies_to_commit column from the master.sys.availability_groups dmv that was changed to required_synchronized_secondaries_to_commit. 

The solution is to upgrade SSMS (testing done on the 17.4 version).

SSMS 17.0

When retrying this on a machine with SSMS 17.0 installed

The same error occurs:


  invalid column name required_copies_to_commit




This is due to the query being different when opening the dashboard on 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
   ...


A column that no longer exists is used

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


Instead of the correct column on newer versions of SSMS:

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


SSMS 17.4

Disclaimer: versions 17.1, 17.2 and 17.3 might also show the correct dashboard. I did not test these yet

When tracing the queries being run by ssms (17.4 or higher) when the dashboard correctly opens, this query runs

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 version



(17.4)

As a side note, some of these columns have not been added to the sys.availability_groups dmv reference 

In the end, the dashboard opens correctly.

Due to the correct columnname being used

required_synchronized_secondaries_to_commit 




The solution

You have to upgrade to a higher version (17.4 worked for me) of SSMS to get the correct and existing DMV columns in the query.