| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | |||
|---|---|---|---|---|---|---|---|---|---|---|---|
Database Mirroring Witness |
Learn about the function of a witness in automatic failover in SQL Server database mirroring. Unlike the partners, the witness does not serve the database. |
MikeRayMSFT |
mikeray |
03/14/2017 |
sql |
database-mirroring |
conceptual |
|
[!INCLUDE SQL Server] 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 [!INCLUDEssNoVersion] 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 Database Mirroring Operating Modes.
The following illustration shows a high-safety mode session with a witness.
In This Topic:
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 [!INCLUDEssnoversion].
We strongly recommend that the witness reside on a separate computer from the partners. Database mirroring partners are supported only by [!INCLUDEssNoVersion] Standard edition and by [!INCLUDEssNoVersion] Enterprise edition. Witnesses, in contrast, are also supported by [!INCLUDEssNoVersion] Workgroup and by [!INCLUDEssNoVersion] Express. Except during an upgrade from an earlier version of [!INCLUDEssNoVersion], the server instances in a mirroring session must all be running the same version of [!INCLUDEssNoVersion]. For example, a [!INCLUDEsql2008-md] witness is supported when you are upgrading from a [!INCLUDEsql2008-md] mirroring configuration but cannot be added to an existing or new [!INCLUDEsql2008r2] or later mirroring configuration.
A witness can run on any reliable computer system that supports any of these editions of [!INCLUDEssNoVersion]. However, we recommend that every server instance that is used as a witness correspond to the minimum configuration that is required for the [!INCLUDEssNoVersion] Standard version that you are running. For more information about these requirements, see Hardware and Software Requirements for Installing SQL Server 2016.
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 a witness
-
Add or Replace a Database Mirroring Witness (SQL Server Management Studio)
-
Add a Database Mirroring Witness Using Windows Authentication (Transact-SQL)
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)