Transact-SQL Settings and Database Mirroring Operating Modes
This topic looks at the operation of a database mirroring session from the point of view of ALTER DATABASE settings and the states of the mirrored database and of the witness. The topic is aimed at users who manage database mirroring primarily or exclusively using Transact-SQL, rather than using Microsoft SQL Server Management Studio. If you are unfamiliar with operating modes, see Database Mirroring Sessions.
Note
As an alternative to using Transact-SQL, you can control the operating mode of a session in Object Explorer using the Mirroring page of the Database Properties dialog box. For more information, see How to: Configure a Database Mirroring Session (SQL Server Management Studio).
How Transaction Safety and Witness State Affect the Operating Mode
The operating mode of a session is determined by the combination of its transaction safety setting and the state of the witness. At any time, the database owner can change the transaction safety level, and can add or remove the witness.
Transaction Safety
Transaction safety is a mirroring-specific database property that determines whether a database mirroring session operates synchronously or asynchronously. There are two safety levels: FULL and OFF.
- SAFETY FULL
Full transaction safety causes the session to operate synchronously in high-safety mode. If a witness is present, a session supports automatic failover.
When you establish a session using ALTER DATABASE statements, the session begins with the SAFETY property set to FULL; that is, the session begins in high-safety mode. After the session begins, you can add a witness.
For more information, see Synchronous Database Mirroring (High-Safety Mode). - SAFETY OFF
Turning off transaction safety causes the session to operate asynchronously, in high-performance mode. If the SAFETY property is set to OFF, the WITNESS property should also be set to OFF (the default). For information about the impact of the witness in high-performance mode, see "The State of the Witness," later in this topic. For more information about running with transaction safety turned off, see Asynchronous Database Mirroring (High-Performance Mode).
The transaction safety setting of the database is recorded on each partner in the sys.database_mirroring catalog view in the mirroring_safety_level and mirroring_safety_level_desc columns. For more information, see sys.database_mirroring (Transact-SQL).
The database owner can change the transaction safety level at any time.
The State of the Witness
If a witness has been set, quorum is required, so the state of the witness is always significant.
If it exists, the witness has one of two states:
- When the witness is connected to a partner, the witness is in the CONNECTED state relative to that partner and has quorum with that partner. In this case, the database can be made available, even if one of the partners is unavailable.
- When the witness exists but is not connected to a partner, the witness is in the UNKOWN or DISCONNECTED state relative to that partner. In this case, the witness lacks quorum with that partner, and if the partners are not connected to each other, the database becomes unavailable.
For information about quorum, see Quorum: How a Witness Affects Database Availability.
The state of each witness on a server instance is recorded in the sys.database_mirroring catalog view in the mirroring_witness_state and mirroring_witness_state_desc columns. For more information, see sys.database_mirroring (Transact-SQL).
The following table summarizes how the operating mode of a session depends upon its transaction safety setting and on state of the witness.
| Operating mode | Transaction safety | Witness state |
|---|---|---|
High-performance mode |
OFF |
NULL (no witness)2 |
High-safety mode without automatic failover |
FULL |
NULL (no witness) |
High-safety mode with automatic failover1 |
FULL |
CONNECTED |
1 If the witness becomes disconnected, we recommend that you set WITNESS OFF until the witness server instance becomes available.
2 If a witness is present in high-performance mode, the witness does not participate in the session. However, to make the database available, at least two of the server instances must remain connected. Therefore, we recommend keeping the WITNESS property set to OFF in high-performance mode sessions. For more information, see Quorum: How a Witness Affects Database Availability.
Viewing the Safety Setting and State of the Witness
To view the safety setting and the state of the witness for a database, use the sys.database_mirroring catalog view. The relevant columns are as follows:
| Factor | Columns | Description |
|---|---|---|
Transaction safety |
mirroring_safety_level or mirroring_safety_level_desc |
Transaction safety setting for updates on the mirror database, one of: UNKNOWN OFF FULL NULL= database is not online. |
Does a witness exist? |
mirroring_witness_name |
Server name of the database mirroring witness or NULL, indicating that no witness exists. |
Witness state |
mirroring_witness_state or mirroring_witness_state_desc |
State of the witness in the database on a given partner: UNKNOWN CONNECTED DISCONNECTED NULL = no witness exists or the database is not online. |
For example, on either the principal or mirror server, enter:
SELECT mirroring_safety_level_desc, mirroring_witness_name, mirroring_witness_state_desc FROM sys.database_mirroring
For more information about this catalog view, see sys.database_mirroring (Transact-SQL).
To add a witness
To remove a witness
To change transaction safety
Factors Affecting Behavior on Loss of the Principal Server
The following table summarizes the combined effect of the transaction safety setting, the state of the database, and the state of the witness on the behavior of a mirroring session on the loss of the principal server.
| Transaction safety | Mirroring state of mirror database | Witness state | Behavior when principal is lost |
|---|---|---|---|
FULL |
SYNCHRONIZED |
CONNECTED |
Automatic failover occurs. |
FULL |
SYNCHRONIZED |
DISCONNECTED |
Mirror server stops; failover is not possible, and the database cannot be made available. |
OFF |
SUSPENDED or DISCONNECTED |
NULL (no witness) |
Service can be forced to the mirror server (with possible data loss). |
FULL |
SYNCHRONIZING or SUSPENDED |
NULL (no witness) |
Service can be forced to the mirror server (with possible data loss). |
See Also
Tasks
How to: Change Transaction Safety in a Database Mirroring Session (Transact-SQL)
How to: Remove the Witness from a Database Mirroring Session (Transact-SQL)
Concepts
Asynchronous Database Mirroring (High-Performance Mode)
Mirroring States
Database Mirroring Witness
Synchronous Database Mirroring (High-Safety Mode)
Other Resources
sys.database_mirroring (Transact-SQL)