0

I get an error when I try to join the secondary replica to the Availability group thru Powershell while following the below Microsoft Documentation.

Join-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\<SecondaryComputer>\Instance" -Name "<AG Name>"

Error:

Failed to join local availability replica to availability group ''. 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.

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-an-availability-group-sql-server-powershell?view=sql-server-2017#ExampleConfigureGroup

I tried the T-SQL command thru SSMS as well.

USE MASTER
ALTER AVAILABILITY GROUP <AG Name> JOIN;

Same error.

3
  • @TonyHinkle, Firewall disabled on both nodes thru group policy. May 2, 2018 at 16:17
  • @LowlyDBA, Interestingly, I don't spot any relevant events in Application or System logs. Filtering Procmon logs for SSMS.exe or Powershell doesn't reveal much either.. May 2, 2018 at 16:20
  • Invoke-SqlCmd -ServerInstance localhost -Query “USE MASTER GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [NT AUTHORITY\SYSTEM];” Added.For both the accounts on both the nodes. Doesn't help May 2, 2018 at 18:25

2 Answers 2

3

The default database for my login was set to 'TempDB'. Changed that to 'Master'. And there was a DNS registration issue with the first node. So the secondary replica wasn't able to communicate with the primary by computername. Correcting these two resolved the issue.

Join-SqlAvailabilityGroup worked properly.

2

In my case the permission was set for both servers correctly. The issue i had was the TCP: 5022 port(Hadr mirror endpoint) was not allowed to communicate from one of servers. Opening the port 5022 fixed my issue.

To check if the port is open run the script on Powershell (use IP address of the other server):

$socket=new-object net.sockets.tcpclient
$socket.connect("IPaddress",port)
$socket.Connected

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.