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://docs.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.

  • 4
    What did the error log have to say? – LowlyDBA May 2 '18 at 15:37
  • Is firewall blocking port 5022? This will bite you even in the GUI setup as it isn't checked by validation. – Tony Hinkle May 2 '18 at 15:50
  • does this link help ? – Kin Shah May 2 '18 at 16:04
  • @TonyHinkle, Firewall disabled on both nodes thru group policy. – Ayan Mullick May 2 '18 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.. – Ayan Mullick May 2 '18 at 16:20
1

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.

1

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, privacy policy and cookie policy

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