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)


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:

 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.

2 Answers 2


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.

  • 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. Feb 22, 2013 at 15:46
  • Basically, that's what's happening. Feb 22, 2013 at 15:48

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.

  • 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, 2013 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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