SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mirroring HIGH SAFETY failover using FORCE SERVICE


Mirroring HIGH SAFETY failover using FORCE SERVICE

Author
Message
Gerard Silveira
Gerard Silveira
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 226
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
MReedSQLBi
MReedSQLBi
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 48
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
Gerard Silveira
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 226
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
MReedSQLBi
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 48
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
Gerard Silveira
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 226
Thank you for taking the time to clear my confusion :-)
MReedSQLBi
MReedSQLBi
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 48
I am still working on adding the image to the thread....
MReedSQLBi
MReedSQLBi
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 48


HTH
MReedSQLBI
Pragmatic Works
Gerard Silveira
Gerard Silveira
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 226
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search