1

I have a database mirroring session in high safety mode between Server1 (Primary) and Server2 (Secondary)

According to this, synchronous data mirroring means

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. Note the wait for this message increases the latency of the transaction

With this in mind, if I lose Server1 completely (and irrecoverably) and force service onto the secondary using

ALTER DATABASE [MyDatabase] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Would I only lose the transactions that were in-flight on Server1?

0

Correct, once the Primary loses connectivity with the Secondary (closes), all in-flight transactions will be rolled-back on the Primary and never make it to the secondary. The committed transactions are guaranteed to be written to disk on the mirror server in that mode.

Here is an excellent article with more detail on this subject - https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-130-in-flight-transactions-continue-after-a-failover/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.