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.
Best Answer
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.