none
HOw Promote secondary node in AAG

    Question

  • Hi

    I'm new SQL Server DBA. Currently i installed SQL Server 2012 with always on(4nodes). e.g Node01, NOde02, node03 and node04. Node01 and Node02 configure with SQL cluster (Cluster name: SQLCL) and all other nodes03, node04 along with cluster node in AAG (always on group). Listern group name is SQLLSN.  Primary is SQLCL and Node3, node04 is secondary servers where database is in read only mode (for reporting point) which is replica from primary means SQLCL.

    Now, problem is whenever SQLCL goes down, my database which is on node03/node04 is not accessible means i can't read.

    So, please let me know , how to promote my database in read-only mode on node03/node04 so that database can be accissible on secondary nodes. I don't want my database come on read-write mode on node03/node04.

    Please help

    Regards

    Saturday, June 22, 2013 5:36 PM

Answers

  • >So, in my case node03/node04 can't be automatic failover if cluster instance down, right?

    Correct if you have a FCI in an AG, only the FCI can be configured for automatic failover.

    >How i do maual failover AGG on node03 and node04 during time of cluster instance down?

    Connect a secondary replica and issue:

    ALTER AVAILABILITY GROUP [YourAGName] FAILOVER

    ALTER AVAILABILITY GROUP

    >tried to promote AGG group from node03/node04 give error "the local node is not part of quorum.."

    The AG is a cluster resource, and can so can only be online on a cluster node that is part of the cluster quorum.  If you took Node01 and Node02 down, and don't have a File Share Witness configured, then Node03 and Node04 do not have enough votes to establish quorum.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, June 24, 2013 4:37 AM
  • The SQL Errorlog normally only holds two or three lines when you try to bring the node online. (Check the log before issuing the "ALTER AVAILABILITY GROUP" command and then again afterwards. ) Cluster log is not so easy, but let's see the SQL log first, this normally gives a good indication.

    If you can't find a clear line in that log either please let me know then I will provide you my email address.

    Wednesday, June 26, 2013 1:36 PM

All replies

  • Hello,

    Since that are read-only secondary's you won't be able to use them as failover partners.

    I would also like to point out that using a clustered instance of SQL Server within an AG means that there will be no automatic failover, it would have to be done manually.

    In either case you'll need at least one synchronous failover partner setup instead of having both as read-only secondary's.

    -Sean


    Sean Gallardy | Blog | Twitter

    Saturday, June 22, 2013 8:06 PM
  • thanks for reply. So, in my case node03/node04 can't be automatic failover if cluster instance down, right?.How i do maual failover AGG on node03 and node04 during time of cluster instance down?. when my AGG group went into resolving mode, i tried to promote AGG group from node03/node04 give error "the local node is not part of quorum..".and, why i'm not able to access my database on node03/node04 once my SQL Cluster instance down,
    is i did configuration wrongly or its normal behaviour of secondary replica nodes.i appreciate ,
    please clear my doubt.

    Sunday, June 23, 2013 3:23 AM
  • Hi Sean

    Any update, please

    Monday, June 24, 2013 3:06 AM
  • >So, in my case node03/node04 can't be automatic failover if cluster instance down, right?

    Correct if you have a FCI in an AG, only the FCI can be configured for automatic failover.

    >How i do maual failover AGG on node03 and node04 during time of cluster instance down?

    Connect a secondary replica and issue:

    ALTER AVAILABILITY GROUP [YourAGName] FAILOVER

    ALTER AVAILABILITY GROUP

    >tried to promote AGG group from node03/node04 give error "the local node is not part of quorum.."

    The AG is a cluster resource, and can so can only be online on a cluster node that is part of the cluster quorum.  If you took Node01 and Node02 down, and don't have a File Share Witness configured, then Node03 and Node04 do not have enough votes to establish quorum.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, June 24, 2013 4:37 AM
  • Thanks David for swift response.

    As i have 2 secondary replica servers, so do i need to fire ALTER AVAILABILITY GROUP [YourAGName] FAILOVER command on node03 and node04 will auto replica from node03, right?),

    In my case cluster resource was offline means SQL Cluster instance and Listener but node01 and node02 was up.My all nodes configure in file share witness.  But when i tried to failover AG  on Node03 i get error. Also my all node

    Monday, June 24, 2013 7:28 AM
  • Just want to share.

    We did one testing. And we shutdown node1 and node02, after that we tried to force cluster start on node03 and after that we try to run command "alter availablity group <> " but it was failed and my AG still showing in resolving state. I have one question, if my cluster instance down(node1/node02) , why i'm unable to access my database from node03 and node04?. Need all your suggestion.

    Tuesday, June 25, 2013 1:19 PM
  • Can you send us the full error message you get? (Including the entries it writes to the SQL Error Log and the Cluster.log)

    Regarding your Node3/4 question: The "Readable Secondary" Feature in Availability Groups only works as Long as the secondary does have an active Connection to the Primary, this is by design. So in your Scenario, when the Primary goes down (and no new Primary is promoted) your secondaries will no longer be accessible.

    Tuesday, June 25, 2013 2:05 PM
  • Thanks for your reply. I will share the SQL error log and cluster.log. Please let me know , how i can share the logfiles it would be too big? Is there anyway i can upload that files. When our node01/node02 failed. Our AG went into resolving state and when i tried to failover to Node03 using SSMS, it was giving error "the local node is not part of quorum and unable to initiate failover...could be one cause. local node not able to communicate with WSFC cluster or no quorum set exist accross the WSFC cluster"

    Wednesday, June 26, 2013 3:54 AM
  • The SQL Errorlog normally only holds two or three lines when you try to bring the node online. (Check the log before issuing the "ALTER AVAILABILITY GROUP" command and then again afterwards. ) Cluster log is not so easy, but let's see the SQL log first, this normally gives a good indication.

    If you can't find a clear line in that log either please let me know then I will provide you my email address.

    Wednesday, June 26, 2013 1:36 PM