Manual failover Primary and Secondary replica clustreless Availability group fails in SQL Server 2017.

Question

Manual failover Primary and Secondary replica clustreless Availability group fails in SQL Server 2017.

When trying to execute a Manual failover from Primary to Secondary replica in Clustreless AG in SQL Server 2017 using SSMS it fails with the error:

"Failover of the availability group '<...>' to the local replica failed because the availability group resource did not come online due to a previous error. To identify that error, check the SQL Server error log, cluster logs and system event logs. For information about how to view events and logs for a Windows Server Failover Clustering (WSFC) cluster, see Windows Server documentation. (.Net SqlClient Data Provider)

Error Number: 41104
Severity: 16
State: 5
Line Number: 1

When running failover commands manually it fails on "ALTER AVAILABILITY GROUP <...> SET (ROLE = SECONDARY);".

Questions:

1. SET (ROLE = SECONDARY); is not referenced anywhere in https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql?view=sql-server-ver15. Why? Is it an undocumented option? What should I do with it? Is there a workaround?
2. Issue looks identical to https://support.microsoft.com/en-us/help/4492604/kb4492604-fix-manual-failover-between-forwarder-and-secondary-replica. As per article it was resolved in CU 15 for SQL Server 2017. We are on CU 22. Is it a different issue? Was it reintroduced after CU 15? Was it fixed correctly in CU15?

Thank you,
Michael

Answer 1

What is the version of SSMS? I remember submitting this to SSMS dev team few month ago and they fixed it.

https://learn.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-ver15#bug-fixes-in-187
Refer # 4

Always On Availability Group | Fixed an issue where Read Scale Availability Group Failover was not working.

If you are seeing it in latest SSMS, please let me know.