Database stuck in restoring mode setting up AlwaysOn

  • I'm trying to do some proof of concept work with AlwaysOn in SQL Server 2012.

    I've got 2 Windows 2012 systems

    PFDB-Node1

    PFDB-Node2

    On the Node1 I have 2 databases which will be a part of my Availability Group

    I've got a shared folder on Node1 for the backups

    My database's are APF_OLTP and APF_OLTPIME

    During the wizard I get down to Joining 'APF_OLTP' to Availability Group 'PFDB-MAIN-AG' on 'PFDB-NODE2'

    check out attachment of screen shot

    In the PFDB-NODE1 sql log I find the following

    Database Mirroring login attempt by user 'RD\PFDB-NODE2$.' failed with error: 'Connection handshake failed. The login 'RD\PFDB-NODE2$' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.106.28.151]

    In the PFDB-NODE2 sql log I find the following

    A connection timeout has occurred while attempting to establish a connection to availability replica 'PFDB-NODE1' with id [6B9C4AF2-3A64-4786-AAFA-F5F0AA56740C]. Either a networking or firewall issue exists<c/> or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.

    Any help would be appreciated.

  • can you provide a little more detail on your setup, did you get any other erros during the earlier steps of the wizard.

    What account type are you using for the SQL Server services?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Actually I was able to resolve the problem.

    I created a server login on my PFDB-NODE1 for RD\PFDB-NODE2$

    I created a server login on my PFDB-NODE2 for RD\PFDB-NODE1$

    I then did grant connect on each

    GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [RD\PFDB-NODE1$];

    GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [RD\PFDB-NODE2$];

    I removed the database's on PFDB-NODE2

    I removed the AG

    Re-ran AG Wizard, and it was successful.

  • How to fix databases stuck in restoring mode on the secondary sql server:

    Restore the database on sql2 using the restore wizard in the gui

    Remove the database from the availability group on sql1 by right clicking on the db and removing it

    Add back the database to the avail group on sql1 by right clicking on the avail group and selecting the checkbox for the db

    Check status on both servers is correct

    Done!

  • I have had success using the wizard with only one database to add to the group. Having 2 databases ticked to add to the availability group gets stuck at restoring the second database on node 2. So, my advice is to do only one database at a time.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply