Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using synchronous database mirroring with a witness and am testing the various failure scenarios and the impact on the mirroring session, such as whether failover occurs.

Starting with the principal, mirror and witness all being connected I break the link between the principal and witness (using some hackery with the hosts file and tcpview)

           W
           | 
 P---------M

The DMVs correctly report this configuration - the principal says the mirror is connected but witness disconnected, mirror says both principal and witness are connected. The witness reports the session as being out of sync.

Next I kill the sqlservr.exe process on the principal, so the connection between P and M terminates:

           W
           | 
 P         M

I would now expect failover to the mirror server as both M and W agree that P is unavailable and there is witness-to-partner quorum. However what happens instead is the database on the mirror goes into the Mirror/Disconnected mode and there is no failover.

Can anyone explain this behaviour? I'm using SQL 2008 SP3 on WS 2008 R2 SP1.

share|improve this question

migrated from serverfault.com Feb 27 '13 at 16:55

This question came from our site for professional system and network administrators.

add comment

2 Answers

up vote 2 down vote accepted

The problem is that the principal became disconnected from the witness server before it went offline. In this case, the witness cannot agree that the principal is offline because it was disconnected while the principal was still connected. As far as the witness knows, the principal may still be up.

In this scenario, it would require that you force service on the mirror to bring it online using ALTER DATABASE ... SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS.

share|improve this answer
    
Just so I'm sure I understand - when the link between principal and witness is broken the witness knows (via the mirror) that the principal is still online. When the principal goes offline the witness does not know first hand (as the link is disconnected) so cannot agree with the mirror, hence no failover takes place. –  Rob Watkins Feb 22 '13 at 15:46
    
Basically, that's what's happening. –  Robert L Davis Feb 22 '13 at 15:48
add comment

The concept of quorum means that at least a majority of the nodes need to agree on the state of "Healthy" for the group to be considered healthy.

In your first example, the Witness can't talk to the Primary so it's vote is "Unhealthy". But the Primary and the Mirror both agree on "Healthy". That's 2/3 members agreeing on "Healthy" so the group is "Healthy".

Now you kill SQL on the Primary. Let's check on the votes:

  • Primary:
  • Mirror: Healthy
  • Witness: Unhealthy

That's a 1/3 vote for "Healthy". That is not a majority. Therefore, the group is "Unhealthy".

Your problem here appears to be that the Witness can't talk to the Primary.

share|improve this answer
    
Not to agree or disagree with you, but the witness has an option on how to make the situation healthy. That is if it voted that the mirror was able to take the load, the system would report as "healthy" again. Do you have corroborating evidence that it works the way you say it works? –  Ben Thul Feb 22 '13 at 15:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.