Problem description
You might get below error when configuring Always on availability group If the cluster services startup account is not part of SQL Server logins.
Error:
TITLE: Microsoft SQL Server Management Studio
——————————
Creating availability group resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)
——————————
ADDITIONAL INFORMATION:
Create failed for Availability Group ‘KK_AG’. (Microsoft.SqlServer.Smo)
——————————
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Cannot bring the Windows Server Failover Clustering (WSFC) resource (ID ’74eaaa74-4e7d-470a-96b4-29459cb4516e’) online (Error code 5018). The WSFC service may not be running or may not be accessible in its current state, or the WSFC resource may not be in a state that could accept the request. For information about this error code, see “System Error Codes” in the Windows Development documentation.
Failed to designate the local availability replica of availability group ‘KK_AG’ as the primary replica. The operation encountered SQL Server error 41066 and has been terminated. Check the preceding error and the SQL Server error log for more details about the error and corrective actions.
Failed to create availability group ‘KK_AG’. The operation encountered SQL Server error 41160 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command. (Microsoft SQL Server, Error: 41066)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3373&EvtSrc=MSSQLServer&EvtID=41066&LinkId=20476
You would also see errors similar to one below when you configure always on availability group using scripts.
Msg 41131, Level 16, State 0, Line 1
Failed to bring availability group ‘TDE_AG’ online. The operation timed out. Verify that the local Windows Server Failover Clustering (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.
Msg 41152, Level 16, State 2, Line 1
Failed to create availability group ‘TDE_AG’. The operation encountered SQL Server error 41131 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command.
Msg 41131, Level 16, State 0, Line 1
Failed to bring availability group ‘TDE_AG’ online. The operation timed out. Verify that the local Windows Server Failover Clustering (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.
Msg 41152, Level 16, State 2, Line 1
Failed to create availability group ‘TDE_AG’. The operation encountered SQL Server error 41131 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command.
Resolution:
Add start up account of cluster service to SQL Server login and grant sysadmin role (Start up account of cluster service will be nt authority\system by default).
SQLServerScribbles.COM 