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 Database Mirroring Operating Modes.
The following illustration shows a high-safety mode session with a witness.
In This Topic:
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.
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 2017.
Software and Hardware Recommendations
We strongly recommend that the witness reside on a separate computer from the partners. Database mirroring partners are supported only by SQL Server Standard edition and by SQL Server Enterprise edition. Witnesses, in contrast, are also supported by SQL Server Workgroup and by SQL Server Express. Except during an upgrade from an earlier version of SQL Server, the server instances in a mirroring session must all be running the same version of SQL Server. For example, a SQL Server 2008 witness is supported when you are upgrading from a SQL Server 2008 mirroring configuration but cannot be added to an existing or new SQL Server 2008 R2 or later mirroring configuration.
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 2016.
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 (Database Mirroring).
To Add or Remove a Witness
To add a witness
To remove the witness
Role Switching During a Database Mirroring Session (SQL Server)
Database Mirroring Operating Modes
Quorum: How a Witness Affects Database Availability (Database Mirroring)
Possible Failures During Database Mirroring
Mirroring States (SQL Server)