<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 High Availability
»
Question about database mirroring with...
Question about database mirroring with "high-safety mode without automatic failover"
Rate Topic
Display Mode
Topic Options
Author
Message
cygne17_2 77208
cygne17_2 77208
Posted Friday, January 11, 2013 2:07 PM
Grasshopper
Group: General Forum Members
Last Login: Friday, May 17, 2013 8:54 AM
Points: 17,
Visits: 52
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
Lynn Pettis
Lynn Pettis
Posted Saturday, January 12, 2013 1:42 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 21,596,
Visits: 27,408
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
cygne17_2 77208
cygne17_2 77208
Posted Monday, January 14, 2013 6:44 AM
Grasshopper
Group: General Forum Members
Last Login: Friday, May 17, 2013 8:54 AM
Points: 17,
Visits: 52
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
Joie Andrew
Joie Andrew
Posted Monday, January 14, 2013 6:51 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Monday, May 06, 2013 5:58 AM
Points: 535,
Visits: 1,010
"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
cygne17_2 77208
cygne17_2 77208
Posted Monday, January 14, 2013 7:01 AM
Grasshopper
Group: General Forum Members
Last Login: Friday, May 17, 2013 8:54 AM
Points: 17,
Visits: 52
Thanks for your answer.
It's clear now.
best regards
Post #1406700
Lynn Pettis
Lynn Pettis
Posted Monday, January 14, 2013 7:08 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 21,596,
Visits: 27,408
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
cygne17_2 77208
cygne17_2 77208
Posted Monday, January 14, 2013 11:42 AM
Grasshopper
Group: General Forum Members
Last Login: Friday, May 17, 2013 8:54 AM
Points: 17,
Visits: 52
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
Joie Andrew
Joie Andrew
Posted Monday, January 14, 2013 1:22 PM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Monday, May 06, 2013 5:58 AM
Points: 535,
Visits: 1,010
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
Perry Whittle
Perry Whittle
Posted Tuesday, January 15, 2013 4:20 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,201,
Visits: 11,151
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
cygne17_2 77208
cygne17_2 77208
Posted Tuesday, January 15, 2013 9:12 AM
Grasshopper
Group: General Forum Members
Last Login: Friday, May 17, 2013 8:54 AM
Points: 17,
Visits: 52
Thanks all for you answers.
Have a nice day!
Post #1407326
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
