1

After several trials I keep getting this error message when setting new availablity group, please help me out.

Create failed for Availability Group 'SQLAVG'. (Microsoft.SqlServer.Management.HadrModel) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17177.0+((SSMS_Rel).170803-0429)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+AvailabilityGroup&LinkId=20476

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

Cannot bring the Windows Server Failover Clustering (WSFC) resource (ID 'fd6eecaa-fa25-47d3-8e44-4a143a82411e') 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 'SQLAVG' 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 'SQLAVG'. 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=13.00.4206&EvtSrc=MSSQLServer&EvtID=41066&LinkId=20476

sql log

HichemSeeSharp
  • 3,240
  • 2
  • 22
  • 44
  • 1
    Open up Windows Failover Cluster Manager and check the status of your cluster. That's where i'd start. – S3S Jul 24 '18 at 14:13
  • @scsimon the status is up everything is green – HichemSeeSharp Jul 24 '18 at 14:35
  • What did the cluster log on the windows server say? Sounds like you may have an listener issue. – S3S Jul 24 '18 at 14:45
  • I am not at listener stage I am just creating the availability group only using the wizard and ticked not create listener now, and it is not getting created. – HichemSeeSharp Jul 24 '18 at 14:48
  • It's hard to tell without checking your error logs, but everything points to a configuration issue in your cluster. – S3S Jul 24 '18 at 14:50
  • I attached screenshot of sql log and I will add also cluster and event viewer log – HichemSeeSharp Jul 24 '18 at 14:54
  • You highlighted the problem in the attachment. You lost quorum in the cluster meaning the cluster doesn't know who should be the primary and secondary. You have a split brain problem. You should research quorum configuration and fix this. Generally speaking, you want an odd number of voters. This is where disk quorum and file shares come in... – S3S Jul 24 '18 at 14:58
  • If you can point few elements to check, that would be helpful – HichemSeeSharp Jul 24 '18 at 15:00
  • https://learn.microsoft.com/en-us/previous-versions/windows/desktop/mscs/quorum-resource – S3S Jul 24 '18 at 15:23

2 Answers2

0

https://support.microsoft.com/en-us/help/2847723/cannot-create-a-high-availability-group-in-microsoft-sql-server-2012

To resolve this issue, use one of the following methods.

Method 1: Use manual steps 1.Create a logon in SQL Server for the [NT AUTHORITY\SYSTEM] account on each SQL Server computer that hosts a replica in your availability group. 2.Grant the [NT AUTHORITY\SYSTEM] account the following server-level permissions:•Alter Any Availability Group •Connect SQL •View server state

Note Make sure that no other permissions are granted to the account. Method 2: Use script1.To create the [NT AUTHORITY\SYSTEM] account, run the following in a query window:

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

2.To grant the permissions to the [NT AUTHORITY\SYSTEM] account, run the following in a query window:

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

More Information

The [NT AUTHORITY\SYSTEM] account is used by SQL Server AlwaysOn health detection to connect to the SQL Server computer and to monitor health. When you create an availability group, health detection is initiated when the primary replica in the availability group comes online. If the [NT AUTHORITY\SYSTEM] account does not exist or does not have sufficient permissions, health detection cannot be initiated, and the availability group cannot come online during the creation process.

Make sure that these permissions exist on each SQL Server computer that could host the primary replica of the availability group.

Note The Resource Host Monitor Service process (RHS.exe) that hosts SQL Resource.dll can be run only under a System account.

For more information, see Troubleshooting automatic failover problems in SQL Server 2012 AlwaysOn environments .

user10340643
  • 1
0

The exact same error occurs if you accidentally leave the Network Mode in DHCP (instead of Static IP) if you have a Fixed IP address already set for the listener name. It's a mistake that is easy to make, so be aware of this.

ecm
  • 2,583
  • 4
  • 21
  • 29
S.E.
  • 1