locked
Error when joining instance to availability group

    Question

  • Hi,

    I created an AG using SQL 2012 RC. Within the AG under Availability Replicas, there are 2 SQL Servers listed - SQL2012Primary (Primary) and SQL2012Sec (Secondary) where the secondary has a red down arrow (screenshot)

     

    When I right click on SQL2012SEC and select to Join to Availability Group, I'm able connect to SQL2012SEC. But when I try to join the server to the availability group, I get an error - Failed to join local availabilty replica to availability group ' ---'. The operation encountered SQL Server error 41106 and has been rolled back. etc ... Screenshot below

    Has anyone seen this before? I've configured a Windows Failover Cluster, added both SQL2012Primary and SQL2012Sec as nodes and then enabled AG on both SQL Servers. I've also confirmed the firewall for both SQL Servers has been turned off since its just a test environment and the user has been assigned the SQL Server sysadmin role. Any help would be greatly appreciated.

    Thanks,

    Jeff


    • Edited by Jeff Thai Wednesday, January 04, 2012 5:28 PM
    Wednesday, January 04, 2012 5:27 PM

Answers

All replies

  • can you share what's in the server error log for the root cause?
    Wednesday, January 04, 2012 8:57 PM
  • Hi Goden,

    Thanks for your quick reply. I did some troubleshooting/research and found that the account running SQL Server Service did not have sufficient priviledges. I've since

    • Changed the account
    • Deleted the AG
    • Deleted the DB Mirroring Endpoints
    • Deleted the restored DB on the Replica

    Then I was able to reconfigured the AG successfully after the account change and restart of SQL.


    Please Mark Answered if my reply solves your problem. Thanks!
    • Proposed as answer by PrinceLuciferMVP Thursday, January 05, 2012 7:59 AM
    • Marked as answer by Jeff Thai Thursday, January 05, 2012 6:54 PM
    Wednesday, January 04, 2012 9:15 PM
    • Proposed as answer by Parikshit Savjani Tuesday, January 31, 2012 10:22 AM
    • Marked as answer by Jeff Thai Monday, February 06, 2012 3:41 PM
    Tuesday, January 31, 2012 10:22 AM
  • thanks. Yes, you need to create separate login and grant permission to HADR endpoint if these are not domain accounts.

    Parikshit's blog below actually is a good reference.

    I'm glad you solve the problem now.


    Tuesday, January 31, 2012 7:21 PM
  • Thanks for blogging about it, Parikshit!


    Please Mark Answered if my reply solves your problem. Thanks!
    Monday, February 06, 2012 3:41 PM