locked
Synchronous SQL Mirrroing RRS feed

  • Question

  • HI Team,

    We have configured the SQL Mirroring with SAFETY=ON, and i also read in several document that the principal commits the transaction only after the transaction hardend in the mirror.

    what does this mean?

    does this mean there happends a Two Phase Commit for all transactions between the Principal and the Mirror?

    What it mean b hardening the transaction on the mirror? is it a commit on the mirror first and then commit on the principal?

    Friday, August 27, 2010 5:00 AM

Answers

  • Yes, the record will be committed to the transaction log. At that point the data can be recovered on the partner server (should the principal go offline for whatever reason). AFTER the commit has been confirmed back to the principal the mirror will then update the appropriate pages in the data files.
    • Marked as answer by SUB77 Friday, August 27, 2010 2:38 PM
    Friday, August 27, 2010 12:20 PM

All replies

  • When SAFETY=ON the principal only issues a commit after the partner has saved the log record to the transaction log on disk.
    Friday, August 27, 2010 6:22 AM
  • As Stephen said, while a database mirroring session is operating in High Availability Mode, every transaction is saved on the Mirror database log before the Principal database commits it. Thus, it provides maximum availablity of the transactions as both Principal and Mirror are complete sync all the time.
    SKG: Please Marked as Answered, if it resolves your issue.
    Friday, August 27, 2010 7:00 AM
  • HI Stephen,

    What you meant by "Partner has saved the log record to the transaction log on disk".

    does this mean there will a 'COMMIT' recorded on the mirror servers transaction log? and not flushed to the data file on the mirror?

    when this transacation will flushed to the data file?

    Friday, August 27, 2010 11:37 AM
  • Yes, the record will be committed to the transaction log. At that point the data can be recovered on the partner server (should the principal go offline for whatever reason). AFTER the commit has been confirmed back to the principal the mirror will then update the appropriate pages in the data files.
    • Marked as answer by SUB77 Friday, August 27, 2010 2:38 PM
    Friday, August 27, 2010 12:20 PM