Database Mirroring Witness
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.
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.
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.
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 2008.
We strongly recommend that the witness reside on a separate computer from the partners. Database mirroring partners are supported only by SQL Server 2005 Standard and later versions and by SQL Server 2005 Enterprise Edition and later versions. Witnesses, in contrast, are also supported by SQL Server 2005 Workgroup and later versions and by SQL Server 2005 Express Edition and later versions. However, in SQL Server 2008 and later versions, a SQL Server 2005 witness is supported only when you are upgrading from a SQL Server 2005 mirroring configuration. A SQL Server 2005 witness cannot be added to existing or new SQL Server 2008 or later mirroring configurations, which require the witness to run on SQL Server 2008 or a later version.
A witness can run on any reliable computer system that supports any of these editions of SQL Server. However, we recommend that every server instance that is used as a witness correspond to the minimum configuration that is required for the SQL Server Standard version that you are running. For more information about these requirements, see Hardware and Software Requirements for Installing SQL Server 2008 R2.
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.
To add a witness
To remove the witness