Standard

Posted by

Posted on

March 3, 2022

Posted under

AlwaysON, AlwaysOn + Log Shipping

Comments

Leave a comment

Failed to bring availability group ‘AG’ online. The operation timed out. If this is a Windows Server Failover Clustering (WSFC) availability group, verify that the local WSFC node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again. (Microsoft SQL Server, Error: 41131)

Environment

-> JBSAG1 and JBSAG2 are Azure IaaS virtual machines provisioned on East US region. Azure IaaS virtual machine JBSAG3 is provisioned on West US region.

-> Azure virtual machine JBSAG1, JBSAG2 and JBSAG3 are part of Windows cluster JBSWikiClust without shared storage. Azure virtual machine JBSAG1 and JBSAG2 will be configured using Synchronous commit with automatic failover. JBSAG3 will be configured using Asynchronous commit with Manual failover.

-> I was testing the failover of above setup, changed the commit mode to JBSAG3 to Synchronous and tried a failover from JBSAG1 to JBSAG3. It failed with below error,

TITLE: Microsoft SQL Server Management Studio
Manual Failover failed (Microsoft.SqlServer.Management.HadrTasks)
ADDITIONAL INFORMATION:

Failed to perform a manual failover of the availability group ‘JBSAG’ to server instance ‘JBSAG3’. (Microsoft.SqlServer.Management.HadrModel)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.46521.71+(SMO-master-A)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Failed to bring availability group ‘JBSAG’ online. The operation timed out. If this is a Windows Server Failover Clustering (WSFC) availability group, verify that the local WSFC node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again. (Microsoft SQL Server, Error: 41131)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-41131-database-engine-error

-> This issue occurs when [NT AUTHORITY\SYSTEM] account is not available or if the account lacks the necessary permissions on the SQL Server Instance.

-> If [NT AUTHORITY\SYSTEM] account is not available. Create it using below command,

USE [master]
GO
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

-> Provide appropriate permission to [NT AUTHORITY\SYSTEM] using below command,

GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]
GO

-> Once above permission was provided, I was able to failover to JBSAG3 without any issues.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Leave a Reply