Database mirroring - link failure between principal and witness

Question

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.

Answer 1

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.

Answer 2

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.