When you configure SQL Server always on available group from management studio it may fail with below error while joining secondary replica to the availability group.
Error 1
{
Joining database on secondary replica resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)
——————————
ADDITIONAL INFORMATION:
Failed to join the database ‘AG’ to the availability group ‘AG1’ on the availability replica ‘NODE2’. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
The connection to the primary replica is not active. The command cannot be processed. (Microsoft SQL Server, Error: 35250)
}
Error 2
{
TITLE: Microsoft SQL Server Management Studio
——————————
Failed to join the instance ‘NODE2’ to the availability group ‘AG1’. (Microsoft.SqlServer.Management.SDK.TaskForms)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476
——————————
ADDITIONAL INFORMATION:
Failed to join local availability replica to availability group ‘AG1’. The operation encountered SQL Server error 41106 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command. (Microsoft SQL Server, Error: 41158)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=41158&LinkId=20476
}
You may get below error when you configure AG availability group using alter database command mentioned below or synchronization might fail with 35250 error mentioned below.
ALTER DATABASE [AG] SET HADR AVAILABILITY GROUP = [Group name];
Error 1
Msg 35250, Level 16, State 7, Line 1
The connection to the primary replica is not active. The command cannot be processed.
To resolve above errors
1. Ensure always on endpoint ([Hadr_endpoint]) are not blocked by firewall (Default port 5022).
2. Make sure startup account of primary server is added to all secondary server’s and Startup accounts of all secondary servers are added to primary servers.(Startup account of each replica to be added to other replica’s)
3. If log on account of SQL Server is “Nt service\” or local system account then ensure system account (Domainname\systemname$) of each replica is added to other replicas.
{
CREATE LOGIN [MSSQLWIKI\node2$] FROM WINDOWS
}
4. Grant connect on always on endpoints created on each replicas for startup account of other replica servers (Grant connect on endpoints even if startup account of other replicas are added as sysadmins).
{
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MSSQLWIKI\node1$]
}
5. Make sure SQL Server name (select @@servername) matches with hostname.
6. Make sure cluster service startup account is part of SQL Server logins (More details in This link).
Thank you,
Karthick P.K |My Facebook Page |My Site| Blog space| Twitter
Disclaimer:
The views expressed on this website/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
MSSQLWIKI 