Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Mirroring HIGH SAFETY failover using FORCE SERVICE Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 11:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 6, 2014 9:55 AM
Points: 73, Visits: 182
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
Post #1464794
Posted Wednesday, June 19, 2013 7:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 17, 2014 12:26 PM
Points: 5, Visits: 41
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
Post #1465159
Posted Wednesday, June 19, 2013 7:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 6, 2014 9:55 AM
Points: 73, Visits: 182
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 ?
Post #1465165
Posted Wednesday, June 19, 2013 9:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 17, 2014 12:26 PM
Points: 5, Visits: 41
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.

Post #1465241
Posted Wednesday, June 19, 2013 1:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 6, 2014 9:55 AM
Points: 73, Visits: 182
Thank you for taking the time to clear my confusion
Post #1465365
Posted Wednesday, June 19, 2013 3:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 17, 2014 12:26 PM
Points: 5, Visits: 41
I am still working on adding the image to the thread....
Post #1465397
Posted Thursday, June 20, 2013 5:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 17, 2014 12:26 PM
Points: 5, Visits: 41


HTH
MReedSQLBI
Pragmatic Works
Post #1465587
Posted Thursday, June 20, 2013 7:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 6, 2014 9:55 AM
Points: 73, Visits: 182
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
Post #1465713
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse