locked
principal database unavailable RRS feed

  • Question

  • HI All,

    Statement: In High availability scenario if the mirror and the witenss server are not reachable(down) from principal server - the principal database will become un available.

    1. Is there a way to avoid principal database being un available to the users during the above scenario. ?
    2. Why is this behaviour?

    kindly help, we faced this in producion and it could have been better if the principal database available in a disconnected state and it can serve user/application connections and syn later when the Mirror databae come available.

    Monday, November 8, 2010 5:32 PM

Answers

  • Hi Sub!

    Easy to answer: Consider following scenario: The principle sees neither the witness nor the mirror, and asumes they are down. If they are down it would be wise to stay online perhaps and continue as normal. But... If the mirror and the witness still see each other and the principal just has some sort of network problem leaving the principle online would mean that you now have both sides online. (As failover is triggered automatically when the mirror looses connection to the principal but still can see the witness.) This would lead to a "Split brain" scenario, and inevitably lead to data inconsistency.

    If you need the principle to stay online even if it doesn't "see" mirror and witness anymore you could create a job that issues a force service command as soon as the DB becomes disconnected and offline. But this would defeat the purpose of high availability mirroring... And it would kill the mirror, as after a Force service there is no way back to sync.

    Lucifer

    • Marked as answer by SUB77 Wednesday, November 10, 2010 4:35 AM
    Tuesday, November 9, 2010 3:53 PM

All replies

  • It needs to maintain a quorum to work (failover). For the quorum you need at least two of the members of the mirroring setup. Have a look at the following article for more details...

    http://technet.microsoft.com/en-us/library/ms189902.aspx

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (OCA 11g, MCITP (2005), MCAD) Please click the Mark as Answer button if a post solves your problem
    Tuesday, November 9, 2010 3:23 AM
  • HI Moort,

    I am not asking about the failover condition, but talking abt the principal database not available scenario when it cannot reach mirror/winteness server

    Statement: In High availability scenario if the mirror and the witenss server are not reachable(down) from principal server - the principal database will become un available.

    1. Is there a way to avoid principal database being un available to the users during the above scenario. ?
    2. Why is this behaviour?

    Tuesday, November 9, 2010 5:32 AM
  • Hi Sub!

    Easy to answer: Consider following scenario: The principle sees neither the witness nor the mirror, and asumes they are down. If they are down it would be wise to stay online perhaps and continue as normal. But... If the mirror and the witness still see each other and the principal just has some sort of network problem leaving the principle online would mean that you now have both sides online. (As failover is triggered automatically when the mirror looses connection to the principal but still can see the witness.) This would lead to a "Split brain" scenario, and inevitably lead to data inconsistency.

    If you need the principle to stay online even if it doesn't "see" mirror and witness anymore you could create a job that issues a force service command as soon as the DB becomes disconnected and offline. But this would defeat the purpose of high availability mirroring... And it would kill the mirror, as after a Force service there is no way back to sync.

    Lucifer

    • Marked as answer by SUB77 Wednesday, November 10, 2010 4:35 AM
    Tuesday, November 9, 2010 3:53 PM