casinos

Database Mirroring–Witness Failure May 28th, 2011

Vinod Kumar

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.

  1. If Witness goes down, mirroring will not fail. 
  2. 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

DISCONNECTED

Principal unable to initialize the mirror

UNKNOWN

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.

Share this article

Tags: , , , , ,

This entry was posted on Saturday, May 28th, 2011 at 22:31 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


One Response to “Database Mirroring–Witness Failure”

  1. DeWayne says:

    Why not just pause the mirroring instead of breaking the breakng the mirror:

    –Create scripts to pause mirroring
    USE MASTER
    GO

    SELECT ‘Alter database ‘ + DB_name(database_id) + ‘ set partner Suspend’
    FROM sys.database_mirroring
    WHERE mirroring_role_desc IN (‘PRINCIPAL’,’MIRROR’)
    AND mirroring_state_desc IN (‘SYNCHRONIZED’,’SYNCHRONIZING’)

    –Copy output and run to pause/suspend mirroring

    Then resume the mirrors once the database(s) / Server is back up?

    –Create scripts to resume mirroring
    USE MASTER
    GO

    SELECT ‘Alter database ‘ + DB_name(database_id) + ‘ set partner Resume’
    FROM sys.database_mirroring
    WHERE mirroring_role_desc IN (‘PRINCIPAL’,’MIRROR’)
    AND mirroring_state_desc IN (‘SUSPENDED’,’SYNCHRONIZING’)

    –Copy output and run to resume mirroring

Leave a Reply



 

Email
Print