DataGinger.com

I have been noticing one very common error that occurs while trying to failover an Availability Group in SQL Server 2012 AlwaysON setup. The Availability Group fails to come online during a fail over process.

Here is the snippet of the error message.

Failed to bring availability group ‘availability_group‘ 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.

This error is usually cause due to the lack of permission for [NT AUTHORITY\SYSTEM] account to create the high-availability group. Running the below SQL script in all the replica secondaries fixed this issue.

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

According to Microsoft, 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.

The below snippet shows that the NT AUTHORITY\SYSTEM runs sp_server_diagnostics which is a new internal procedure that runs on a continuous basis. This captures diagnostic data and health information about SQL Server to detect potential failures just like we had default trace (MSSQL\LOG\*.TRC) in the past

Hope this article helped you to understand the importance of the NT AUTHORITY\SYSTEM in AlwaysOn configured SQL 2012 servers.

via Failed to bring availability group ‘[availability group name]’ online.

Read Full Post »