THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Mirroring: what happens if principal loses contact with both mirror and wittness?

Imagine a database mirroring setup where you have two LANs with a WAN link in between. Let's call them site A and site B. Say that principal is currently in site A, and both mirror and witness are in site B. I.e., we are running database mirroring with a witness, and assuming we are running safety FULL (synchronous), we have auto-fail over.

Now, what is really fail over when it comes to mirroring? the simple answer is that the mirror will finish the recovery process (UNDO) and make the database available. So, what if the mirror and witness becomes isolated from the principal? In out setup, this would happen is site A is disconnected from site B. Well, the fail-over protocol is as follows:

If the mirror cannot contact the principal, it asks the witness if it has contact with the principal. If the witness responds and says it doesn't have contact with the principal, then they consider this a quorum. They are in majority and fail over occurs. I.e., the mirror will make the database available (and essentially becomes the principal).

But, think about the poor principal now. It is isolated and knows nothing about what happens at the other end. The only logical thing to do is to make the database inaccessible. If that didn't happen, then we would have a case where both sites had the database available, and we wouldn't want that!

This is nothing new, and it is clearly stated in Ron Talmage's great whitepaper on mirroring: http://technet.microsoft.com/en-us/library/cc917680.aspx.

This is easy to test. Setup mirroring (you can even do it between three instances in the same OS). Make sure you are running safety full and have a wittness. Now you check what Windows process ID the principal has:

SELECT SERVERPROPERTY('ProcessID')

Based on above, you now use Task Manage to kill the *other* two SQL Servers. Make sure that SQL Server Agent isn't running for those two, else agent will start those SQL Servers again. Management Studio will now show the database as "In Recovery", and if you try to access it, for instance from a query window, you get below error:

Msg 955, Level 14, State 1, Line 1
Database Adventureworks2008 is enabled for Database Mirroring,
but the database lacks quorum: the database cannot be opened.
Check the partner and witness connections if configured.

Published Friday, April 16, 2010 8:46 AM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Robert L Davis said:

And this is exactly why it is not recommended to have a witness server when running in asynchronous mode. If witness and mirror both go down, then the principal will take itself offline. Without a witness, if the principal becomes disconnected from the mirror, the principal will remain up because it knows that the mirror will not come online automatically.

April 16, 2010 11:30 AM
 

James Luetkehoelter said:

This also good food for thought about where the witness is placed, near the primary, the the mirror or in between? Or is mirroring even a good choice over a WAN link? Good post!

April 17, 2010 6:39 PM
 

Ted Krueger (onpnt) said:

One of my favorite phrases in mirroring, "split brain".  

Good short look into some thoughts on what really needs to go into thinking about mirroing beyond, right click, start mirroring...

April 18, 2010 1:55 PM
 

Ranga said:

Interesting scenario in mirroring. Mirroring is still not very reliable, needs more tweaking and FAULT TOLERANCE!!

See my connect item on a different mirroring scenario: When the mirror server goes offline.

https://connect.microsoft.com/SQLServer/feedback/details/337691/auto-sync-in-mirroring

April 20, 2010 11:21 PM
 

Hugo Shebbeare said:

I agree with Master Davis, therefore it is necessary, I believe as a pre-requisite for auto-failover with mirror in asynch mode, to ensure there is a redundant internet connection between all of the quorum. We have achieved and tested that on our Database mirroring setups to ensure that we never get into that split-brain situation.

Thanks Tibor, nice post.

November 5, 2010 3:23 PM
 

chaitanya said:

How to make Data access to the users? When both servers are down in MIRRORING

WHAT ARE THE SOLUTIONS FOR THESE THREE CASES USING "MIRRORING CONCEPT"

Server A=PRINCIPLE OR PRIMARY,Server B=MIRROR,Server C=WITNESS

1)what if Server A and Server B are completely down,2)what if Server B and Server C are completely down,3)what if Server A and Server C are completely down

In all the cases we cannot Re-Initialize the Servers which are down

How to make Data access to the users?

plase give me the answer in both synchronous and Asynchronous modes

December 14, 2012 6:32 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement