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).

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).