At a discussion with a customer they asked me what if the mirroring witness database fails. Does that mean the mirroring also fails as part of this? What about automatic failover? Is there a way to automatically remedy the same?
It was an interesting question and I felt it is worth to write a mini-post around the same.
- If Witness goes down, mirroring will not fail.
- It will lose the automatic failover capability.
But there are some special case scenarios when the witness behavior can become interesting.
When both Witness and Mirror goes down, Principal loses quorum and becomes “isolated”. It can’t serve the applications at this point. If the Mirror or the Witness can’t be brought back online quickly, the only way out is to break the mirroring session.
ALTER DATABASE <DATABASENAME> SET PARTNER OFF
Re-establish database mirroring session when the mirror becomes available.
In the sys.database_mirroring catalog view there is a column – mirroring_witness_state. The witness server has three states in the sys.database_mirroring catalog view, CONNECTED, DISCONNECTED, and UNKNOWN.
Witness server unavailable
Principal unable to initialize the mirror
Since the state of the witness server is actually recorded in the partner servers, and not on the witness server, these states are set from the vantage point of the partners. So seeing a DISCONNECTED status for the witness server implies that the partners are disconnected from the witness. When database mirroring starts up, if the mirror cannot initialize with the principal, the witness will enter an UNKNOWN state. So, you could write some event handler to watch this state through the DMVs.
Besides, Witness could be on a failover cluster for higher availability.
For more on the states of Database Mirroring can be got from the BOL.