Mirroring HIGH SAFETY failover using FORCE SERVICE

Question

Mirroring HIGH SAFETY failover using FORCE SERVICE

Gerard Silveira

Ten Centuries

Points: 1106
More actions
June 18, 2013 at 11:05 am

#278818

Can someone please explain if there would be data loss when using FORCE SERVICE for database mirroring failover, if the database mirroring session has been setup up in the HIGH SAFETY mode ?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MReedSQLBi SSC Rookie Points: 37 More actions · Answer 1

Presuming the definition of "loss" includes "data is unrecoverable on the Principal after the outage"....

The answer is the most hated expression "it depends". Given the conditions where the Force Service command can be used (Principal down, Witness OFF or connected to Mirror), the potential definitely exists for data loss. See the following link for more detail. http://msdn.microsoft.com/en-us/library/ms189270.aspx

The "potential" component relates to queued transaction log blocks that were being transmitted but not yet committed to disk on the Mirror when the Principal went down. If your system is particularly active the potential for loss is high...based on a collision of the failure and the transmit/commit cycle. If your system is not so active and the Mirror failure occurred when the Mirror was synchronized there would be no loss.

HTH

mreed

Pragmatic Works

Gerard Silveira Ten Centuries Points: 1106 More actions · Answer 2

Thanks for your reply ,

Maybe my understanding is wrong here

I was under the impression , that the transaction would be committed on the principal only after it has been applied to the mirror, when in HIGH SAFETY mode. So I cannot get my head around data loss occurring , or

does it mean the user work that is not yet committed to the principal would be lost ?

MReedSQLBi SSC Rookie Points: 37 More actions · Answer 3

You are indeed correct regarding the simultaneous commit...I had to go back to a document I prepared a couple years ago to find the answer.

In high safety mode the synchronization workflow consists of two major steps...

...Transmitting and committing the transaction log blocks to disk on both mirror participants

...Applying and committing the transactions to the database

I have an image of the workflow shows the issue pretty clearly. I will post as soon as I can...might take an hour or so...and I will update this thread with a link. The bottom line is there is window of opportunity for transaction loss between the time the transaction is logged on the Principal and the time the Principal sends a commit request to the Mirror.

Gerard Silveira Ten Centuries Points: 1106 More actions · Answer 4

Thank you for taking the time to clear my confusion 🙂

MReedSQLBi SSC Rookie Points: 37 More actions · Answer 5

I am still working on adding the image to the thread....

MReedSQLBi SSC Rookie Points: 37 More actions · Answer 6

MReedSQLBi

SSC Rookie

Points: 37

June 20, 2013 at 5:10 am

#1625816

HTH

MReedSQLBI

Pragmatic Works

Gerard Silveira Ten Centuries Points: 1106 More actions · Answer 7

So the potential for data loss, would exist anywhere between steps 1 and 5 as shown in your diagram.

Since the transaction is not committed on the PRINCIPAL, there is "real world" data loss and not a "database world" data loss as in case of HIGH Performance mode, that's was the root cause of all the confusion.

Thanks once again