April 30, 2010 at 8:28 am
Hi,
We have a SQL 2005 server with mirroring configured in High Safety (Synchronous) mode. In case if the Mirror database is down, how does that affect principal database. I read that in synchronous mirroring, principal database waits to commit the transaction until it hears back from mirror that it is committed there.
In that case, if mirros database is down, the transaction at the principal will be waiting to be committed and there is no guarantee that users will see the current\correct data.
Is it correct? or am I missing something in understanding the concept?
Thank you in advance for your responses.
[font="Verdana"]Renuka__[/font]
April 30, 2010 at 10:55 am
Without a Witness --------
In this case, nothing happens to the Principal server and it keep on running.
But if the Principal server goes down the mirroring is suspended and requires manual intervention with possible data loss.
Maninder
www.dbanation.com
April 30, 2010 at 11:39 am
Thank you for your reply Maninder.
My main doubt is when MIRROR goes down in Synchronised mode mirroring, as the transactions at principal side will be waiting for the acknowledgement from mirror (which is down), will that transaction be committed or not at the principal. Will the users be able to see the committed/up-to-date data?
I tried on test environment pausing the mirroring and bringing the mirror database down. It works fine and users are able to see the committed data. But I am unable to test this completely making the mirror server down (because of our environment constraints).
Hope I am not confusing.
Thank you
[font="Verdana"]Renuka__[/font]
May 3, 2010 at 10:06 am
Renuka,
As per http://msdn.microsoft.com/en-us/library/ms179344(SQL.90).aspx
The loss of a partner has the following effect:
3. If the only mirror server becomes unavailable, the principal and witness continue.
Hope this answer your question!
Manu
May 3, 2010 at 10:23 am
MANU-J. (5/3/2010)
Renuka,As per http://msdn.microsoft.com/en-us/library/ms179344(SQL.90).aspx
The loss of a partner has the following effect:
3. If the only mirror server becomes unavailable, the principal and witness continue.
Hope this answer your question!
Manu
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 4, 2010 at 1:56 am
Hi Renuka,
I believe when the mirror is down in a database mirroring session which is in synchronous mode, the users will not receive a confirmation of a commit or roll back for their transactions; which means that the users will see a latency until the log is hardened on the mirror server.
Thanks
May 4, 2010 at 9:45 am
In High Safety mode with out witness server when mirror server instance goes down, the principal server instance is unaffected (that is without mirroring the data). From the forum I am sure that Principal will not wait for acknowledgement from Mirror.
But when there is witness server the answer Vague in the forum but I believe that it will be same as principal will not wait for acknowledgement from Mirror instance as Witness will not do anything except checking whether it can reach the database or not...
I hope my answer is Correct. Please let me know if anything was wrong...
Here is the blog http://msdn.microsoft.com/en-us/library/ms179344.aspx
--Vamsi
May 4, 2010 at 10:19 am
Mirroring is not designed to hold up the clients.
In high safety mode, with no witness, if the mirror goes down, the principal continues to work. Clients can add data.
In high safety mode, with a witness, if the mirror goes down, the principal continues to work. That's in the link above. There's also a note in this link (http://blog.rolpdog.com/2010/04/sql-server-database-mirroring-woes.html) that says: Most importantly, unlike a distributed transaction, failures to commit on the mirror will not cause a transaction rollback on the principal.
The issue is that without a witness, how does the principal know the mirror is down? My guess is that if it sends the next transaction and hasn't received a response from the mirror in some time, it considers the mirror down and starts to pick up speed again. You'd have to test this somehow and see what the tolerance is.
A blog here with some issues: http://blog.rolpdog.com/2010/04/sql-server-database-mirroring-woes.html
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply