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

Question about database mirroring with "high-safety mode without automatic failover" Expand / Collapse
Author
Message
Posted Friday, January 11, 2013 2:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:45 PM
Points: 29, Visits: 76
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
Post #1406256
Posted Saturday, January 12, 2013 1:42 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:21 PM
Points: 20,676, Visits: 32,263
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.



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)
Post #1406321
Posted Monday, January 14, 2013 6:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:45 PM
Points: 29, Visits: 76
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
Post #1406691
Posted Monday, January 14, 2013 6:51 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
"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"
Post #1406692
Posted Monday, January 14, 2013 7:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:45 PM
Points: 29, Visits: 76
Thanks for your answer.
It's clear now.
best regards
Post #1406700
Posted Monday, January 14, 2013 7:08 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:21 PM
Points: 20,676, Visits: 32,263
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.




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)
Post #1406705
Posted Monday, January 14, 2013 11:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:45 PM
Points: 29, Visits: 76
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,

Post #1406883
Posted Monday, January 14, 2013 1:22 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
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"
Post #1406929
Posted Tuesday, January 15, 2013 4:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:56 PM
Points: 6,365, Visits: 13,695
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"
Post #1407152
Posted Tuesday, January 15, 2013 9:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 1:45 PM
Points: 29, Visits: 76
Thanks all for you answers.
Have a nice day!
Post #1407326
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse