Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question about database mirroring with "high-safety mode without automatic failover"


Question about database mirroring with "high-safety mode without automatic failover"

Author
Message
cygne17_2 77208
cygne17_2 77208
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 92
Hi,
In database mirroring, for "high-safety mode without automatic failover" it is mention that :

"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."

And it is also mention that:

"When the partners are connected and the database is already synchronized, manual failover is supported. If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data). If the principal server is lost, the mirror is suspended, but service can be forced to the mirror server (with possible data loss). "

So, If the principal server is lost, why service can be forced to the mirror server WITH POSSIBLE DATA LOSS ?
Why with WITH POSSIBLE DATA LOSS ?

ref: http://msdn.microsoft.com/en-us/library/ms179344(d=printer,v=sql.105).aspx

regards,
cygne
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24283 Visits: 37990
Did you follow the link in that msdn article you referenced, http://msdn.microsoft.com/en-us/library/ms189977(v=sql.105).aspx? It does a good job of explaining what happens.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
cygne17_2 77208
cygne17_2 77208
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 92
Hi,
yes I read this article.
I understand the possible data loss with high-performance mode(asynchronous).

But I still don't understand why we can have possible data loss with high-safety mode?
In high-safety mode, transaction committed on the principal are also committed on the mirror.

Do you have an idea ?
best regards
David
Joie Andrew
Joie Andrew
SSC Eights!
SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)

Group: General Forum Members
Points: 978 Visits: 1921
"When the partners are connected and the database is already synchronized, manual failover is supported. If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data). If the principal server is lost, the mirror is suspended, but service can be forced to the mirror server (with possible data loss). "


The possible data loss is when the priciple fails while the mirror was also failed. In that scenario if the mirror is brought up and forced to be the new principle data loss may have occurred because transactions may have been on the principle that were not applied to the mirror yet.

Joie Andrew
"Since 1982"
cygne17_2 77208
cygne17_2 77208
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 92
Thanks for your answer.
It's clear now.
best regards
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24283 Visits: 37990
cygne17_2 77208 (1/14/2013)
Hi,
yes I read this article.
I understand the possible data loss with high-performance mode(asynchronous).

But I still don't understand why we can have possible data loss with high-safety mode?
In high-safety mode, transaction committed on the principal are also committed on the mirror.

Do you have an idea ?
best regards
David


It said it in the article:

If service is forced because the principal server has failed, potential data loss is depends on whether any transaction logs were not sent to the mirror server before the failure. Under high-safety mode, this is possible only until the mirror database becomes synchronized.

There is a possibility if the principal and mirror are synchronized, that when the principal fails that it may not have sent the most recent transactions to the mirror database. These would be transactions that had been queued but not yet sent, perhaps due to network latency or a high volume of transactions. At this point, the databases may have actually been in a synchronizing state but SSMS or Mirroring Monitor had not yet changed to reflect it.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
cygne17_2 77208
cygne17_2 77208
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 92
But I still not understand why ?
In safety-mode, principal do not applied change on datafile until it's write in transaction log of the mirror.

So, if principal crashed, the transaction not written to the mirror will be rollback on the old principal when it will come back.

So the data will not be lost because they will not be applied on old principal.


missing something ?

regards,
Joie Andrew
Joie Andrew
SSC Eights!
SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)

Group: General Forum Members
Points: 978 Visits: 1921
But I still not understand why ?
In safety-mode, principal do not applied change on datafile until it's write in transaction log of the mirror.

So, if principal crashed, the transaction not written to the mirror will be rollback on the old principal when it will come back.

So the data will not be lost because they will not be applied on old principal.


missing something ?

regards,


There are a couple of reasons why transactions would not have come over I can think of. One is if the mirror server was in a suspended state when the primary failed and then forced to become the primary. In that scenario there are commands that could have applied on the primary that did not apply on the mirror.

Another reason could be because db mirroring works in near real-time using queues. Depending on the amount of traffic and the latency between the primary and mirror servers I can see a scenario where commands are not yet received at the mirror before failover. I believe Paul Randal did an article on this for TechNet magazine.

Some time later...

Yep, he did.

http://technet.microsoft.com/en-us/magazine/hh334997.aspx

Joie Andrew
"Since 1982"
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8855 Visits: 16593
As Joie has stated and as Paul Randal's article explains even with safety on (synchronous mode), without a witness a mirror disconnection will allow the principal to stay online and the send queue to grow, this represents the data loss risk factor.

Also, in normal operation, if the principal fails before this send queue has been processed by the mirror, the mirror needs to be forced into service at the risk of data loss.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
cygne17_2 77208
cygne17_2 77208
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 92
Thanks all for you answers.
Have a nice day!
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