none
SQL Alwayson Question RRS feed

  • Question

  • Hello,

    i have setup an SQL 2016 AlwaysOn.My databases are in sync and when i manually move to secondary node or even i do a reboot my databases are online to the secondary,working as expected.

    My main question is if i unplug the Ethernet cable for testing from the primary node should the databases get online to the secondary?Because when i shut the network i get a resolving error for the listener and my databases are offline to the primary.The secondary node does not change to primary.

    Is the Ethernet problem an acceptable test for the SQL and if it is how i can resolve my problem?

    Thank you!


    • Edited by Panos83_ Tuesday, July 9, 2019 6:19 AM
    Tuesday, July 9, 2019 6:16 AM

All replies


  • My main question is if i unplug the Ethernet cable for testing from the primary node should the databases get online to the secondary?Because when i shut the network i get a resolving error for the listener and my databases are offline to the primary.The secondary node does not change to primary.

    Is the Ethernet problem an acceptable test for the SQL and if it is how i can resolve my problem?

    Thank you!


    That is expected  I also believe there is no network redundancy or NIC teaming. The reason is the failover is done by WSFC and if network is down none of the Nodes (quorum voting members) would be able to register their IP address and network name and see other nodes(voting members). This can lead to quorum loss and WSFC does not understand what to do. 

    With AG you have to have a network redundancy or you are making network a single point of failure for AG. NIC teaming is the most preferred way to bring in network redundancy


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Tuesday, July 9, 2019 6:44 AM
    Moderator
  • Hello,

    i have setup an SQL 2016 AlwaysOn.My databases are in sync and when i manually move to secondary node or even i do a reboot my databases are online to the secondary,working as expected.

    My main question is if i unplug the Ethernet cable for testing from the primary node should the databases get online to the secondary?Because when i shut the network i get a resolving error for the listener and my databases are offline to the primary.The secondary node does not change to primary.

    Is the Ethernet problem an acceptable test for the SQL and if it is how i can resolve my problem?

    Thank you!


    IF you configure Private network for Heartbeat this problem would not be resolved..

    Regards,

    Raj

     
    Tuesday, July 9, 2019 7:55 AM
  • So the only supported test is by reboot/shutdown the server?
    Tuesday, July 9, 2019 7:59 AM
  • So the only supported test is by reboot/shutdown the server?
    Yes reboot windows server, forcefully kill SQL Server power outage any other such scenarios which do not require complete network being down

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, July 9, 2019 5:13 PM
    Moderator
  • So the only supported test is by reboot/shutdown the server?

    I'd say yes. When you stop the SQL instance on the primary replica, it should trigger an automatic failover. Remember, an automatic failover of AG is supported only when the following conditions are met.

    - The replicas are running in sync-commit and you set failover to automatic

    - The secondary databases (all DBs within the AG) is synchronized with the primary

    - the windows cluster (WSFC) maintains quorum. (You didn't indicate whether or not you have a witness. If you only have 2 votes (from 2 replicas), and one of them dies, that means the WSFC doesn't have quorum, as such, it won't be online)

    - Flexible failover policy's condition has been met. The default condition level is 3 (among other checks, this condition checks if the replica is online, hence shutting down the primary instance meets this level). 

    Not all failures automatically result in an automatic failover. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Tuesday, July 9, 2019 5:24 PM