Database Mirroring Witness

  • Article

To support automatic failover, a database mirroring session must be configured in high-safety mode and also possess a third server instance, known as the witness. The witness is an optional instance of SQL Server that enables the mirror server in a high-safety mode session to recognize whether to initiate an automatic failover. Unlike the two partners, the witness does not serve the database. Supporting automatic failover is the only role of the witness.

Note

In high-performance mode, the witness can adversely affect availability. If a witness is configured for a database mirroring session, the principal server must be connected at least to one of the other server instances, the mirror server or the witness, or both of them. Otherwise, the database becomes unavailable and forcing service (with possible data loss) is impossible. Therefore, for high-performance mode, we strongly recommend that you always keep the witness set to OFF. For information about the impact of a witness on high-performance mode, see Asynchronous Database Mirroring (High-Performance Mode).

The following illustration shows a high-safety mode session with a witness.

Mirroring session with a witness

Using a Witness in Multiple Sessions

A specific server instance can act as a witness in concurrent database mirroring sessions, each for a different database. Different sessions can be with different partners. The following illustration shows a server instance that is a witness in two database mirroring sessions with different partners.

Server instance that is a witness for 2 databases

A single-server instance can also function at the same time as a witness in some sessions and a partner in other sessions. However, in practice, a server instance typically functions as either a witness or a partner. This is because the partners require sophisticated computers that have enough hardware to support a production database, whereas the witness can run on any available Windows system that supports SQL Server 2005.

Software and Hardware Recommendations

We strongly recommend that the witness reside on a separate computer from the partners. Unlike database mirroring partners, which are supported only by SQL Server 2005 Standard Edition and SQL Server 2005 Enterprise Edition, witnesses are also supported by SQL Server 2005 Workgroup Edition and SQL Server 2005 Express Edition. A witness can run on any reliable computer system that supports SQL Server 2005, but we recommend that every server instance that is used as a witness correspond to the minimum configuration that is required for SQL Server 2005 Standard Edition. For more information about these requirements, see Hardware and Software Requirements for Installing SQL Server 2005.

Role of the Witness in Automatic Failover

Throughout a database mirroring session, all the server instances monitor their connection status. If the partners become disconnected from each other, they rely on the witness to make sure that only one of them is currently serving the database. If a synchronized mirror server loses its connection to the principal server but remains connected to the witness, the mirror server contacts the witness to determine whether the witness has lost its connection to the principal server:

  • If the principal server is still connected to the witness, automatic failover does not occur. Instead, the principal server continues to server the database while accumulating log records to send the mirror server when the partners reconnect.
  • If the witness is also disconnected from the principal server, the mirror server knows that principal database has become unavailable. In this case, the mirror server immediately initiates an automatic failover.
  • If the mirror server is disconnected from the witness and also from the principal server, automatic failover is not possible, regardless of the state of the principal server.

The requirement that at least two of the server instances be connected is known as quorum. Quorum makes sure that the database can only be served by one partner at a time. For information about how quorum works and its impact on a session, see Quorum: How a Witness Affects Database Availability.

Adding or Removing a Witness

To add a witness

To remove the witness

See Also

Concepts

Automatic Failover
Quorum: How a Witness Affects Database Availability
Possible Failures During Database Mirroring
Mirroring States
Synchronous Database Mirroring (High-Safety Mode)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
Changed content:
  • Reorganized the topic to make the configuration and hardware requirements more prominent.
  • Expanded the description of the role of the witness.

5 December 2005
New content:
  • Added that the witness can run on any reliable computer system that supports SQL Server.
Changed content:
  • Removed statement that setting the witness closes all connections to the principal database and restarts it.