2

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 ?

enter image description here

  • Hi and welcome to dba.se, What version of SSMS are you using? – Randi Vertongen Apr 16 at 8:14
  • 1
    @RandiVertongen 17.0 – Mubashar Iftikhar Apr 16 at 9:08
  • @RandiVertongen Dear i am stuck here from last 3 days. kindly share your views if you have. – Mubashar Iftikhar Apr 16 at 10:31
  • I would try updating ssms, I have been looking for info on the column in the error but can only find a cmdlet that was added in 17.0. I looked through the commands fired when opening the dashboard on my version (17.9) but to no avail – Randi Vertongen Apr 16 at 10:44
  • @RandiVertongen Hi Dear did you find any solution ? – Mubashar Iftikhar Apr 17 at 9:40
1

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

enter image description here

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

enter image description here

(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.

  • Dear Thanks for your effort . I have installed 18.0 version & working fine now Cheers :) – Mubashar Iftikhar Apr 17 at 11:41
  • @MubasharIftikhar happy to help! If my answer helped you solve the question could you mark it as solved? Thanks! – Randi Vertongen Apr 17 at 12:01
  • Sure Dear. I am unable to find the option "mark as solved" but at the top of your answer where i can see '1' when i click on that for vote it says "Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score" . – Mubashar Iftikhar Apr 18 at 4:31
  • i am not a continuous user of StackExchange thats why i can't able to vote and i have no 15 repetitions. but again thanks for your effort :) you are great :) – Mubashar Iftikhar Apr 18 at 4:33
  • 1
    Read the Documentation , Done :) – Mubashar Iftikhar Apr 18 at 6:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.