Mirroring in High safety mode

Question

Post reply

Mirroring in High safety mode

Renuka__

Hall of Fame

Points: 3656
More actions
April 30, 2010 at 8:28 am

#140684

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]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MannySingh SSChampion Points: 12465 More actions · Answer 1

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

Renuka__ Hall of Fame Points: 3656 More actions · Answer 2

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]

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

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

TSQLLearner2014 SSC Veteran Points: 272 More actions · Answer 5

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

Vamsi Makineni Grasshopper Points: 12 More actions · Answer 6

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

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