locked
Internal Working of Synchronous Database Mirroring RRS feed

  • Question

  • Hi All,

    In synchronous database mirroring, Principal database waits for the acknowledgement from mirror database. If changes has been made on the mirror and acknowledgement is sent but it could not reach to principal due to some network issue, transaction on principal will be rolled back as it could not get acknowledgement. Here I want to know; what will happen to data committed on the mirror database?

    Will mirroring session remain in sync or it will get disconnected or enter into some other mirroring state?

    Regards,
    Ravi Agarwal

     

    Tuesday, December 20, 2011 6:49 AM

Answers

  • That transaction will be rollbacked during the synchronization process - as logs continously streaming to mirroring server from principal and reply
    http://uk.linkedin.com/in/ramjaddu
    • Marked as answer by msdbapro Tuesday, December 20, 2011 12:30 PM
    Tuesday, December 20, 2011 11:44 AM

All replies

  • Hi Ravi,
    When principal and mirror are connected the database remains in synchronized state. If the mirror goes down (or disconnected due to network issue), the principal does not get affected and continues servicing client requests.
    In High Safety mode the principal makes sure that the transaction is sent to mirror before it is committed. In case, the mirror is not available the principal runs exposed. The mirror will be in synchronizing state when it comes back online as it has to catch-up on transactions on principal.

    - Vishal

    SqlAndMe.com


    Tuesday, December 20, 2011 8:12 AM
  • Hi Ravi,
    When principal and mirror are connected the database remains in synchronized state. If the mirror goes down (or disconnected due to network issue), the principal does not get affected and continues servicing client requests.
    In High Safety mode the principal makes sure that the transaction is sent to mirror before it is committed. In case, the mirror is not available the principal runs exposed. The mirror will be in synchronizing state when it comes back online as it has to catch-up on transactions on principal.

    - Vishal

    SqlAndMe.com



    But what will happen to committed data on the mirror for which principal could not receive acknowledgement?
    Tuesday, December 20, 2011 8:32 AM
  • It is allowed to complete on principal database.
    - Vishal

    SqlAndMe.com

    • Proposed as answer by RamJaddu Tuesday, December 20, 2011 9:15 AM
    • Unproposed as answer by msdbapro Tuesday, December 20, 2011 11:14 AM
    Tuesday, December 20, 2011 8:38 AM
  • When mirroring server is out of service then principal allows all connections with exposed status - once mirroring server back online its automatically start synchonizing data with principal after synchonization completed eventually status changed to synchonised from that point its ready for failover -


    http://uk.linkedin.com/in/ramjaddu
    Tuesday, December 20, 2011 9:15 AM
  • Let me frame my question again..In synchronous  database mirroring every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction's log to disk. (ref: http://msdn.microsoft.com/en-us/library/ms179344(v=sql.90).aspx)

    From above ref it is clear that transaction will be committed on principal only after acknowledgement from mirror that it has been hardened the transaction's log to disk on mirror.

    If due to any reason principal DB could not receive acknowledgement , transaction on principal would be rolled back. But what will happen to the transaction committed on mirror?

    Tuesday, December 20, 2011 11:36 AM
  • That transaction will be rollbacked during the synchronization process - as logs continously streaming to mirroring server from principal and reply
    http://uk.linkedin.com/in/ramjaddu
    • Marked as answer by msdbapro Tuesday, December 20, 2011 12:30 PM
    Tuesday, December 20, 2011 11:44 AM
  • Thank you very much Ram for clarifying this concept.!! 
    Tuesday, December 20, 2011 12:31 PM