SQL Server Performance

Transaction Safety?

Discussion in 'SQL Server 2005 Database Mirroring' started by sachinsamuel, Nov 28, 2007.

  1. sachinsamuel New Member

    Hi All,
    We are evaluating DB mirroring for our production box. But we have some queries with the operating mode available for mirroring.
    1) I set the database mirroring operation mode to full safety mode which is with synchronous. And Then executed a command to insert data in bulk in principal, the changes were committed to principal even when they are still getting mirrored to mirror server.

    But as per the mirroring documentation from Microsoft says this
    "If transaction safety (or just 'safety') is set to FULL, the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens its principal database log records to disk, it also sends them to the mirror. The principal then waits for a response from the mirror server. The mirror responds when it has hardened those same log records to the mirror's log disk."
    Is this a bug or I am doing anything wrong.
    2) Mirroring documentation also says that when the transaction is on full safety mode, and if mirror and witness goes down then principal goes to read only mode. But again I saw deviation from the documentation because when I turned mirror and witness off, principal went to "In recovery" mode. Please help incase anybody have faced this before or can help me if I am interpreting it wrong.
    ThanksSachin
  2. satya Moderator

    Have you monitored the database mirroring using DBM Monitor, see under sql server error log for more information on that status of principal database when the mirror is off.

Share This Page