Witness server- DATABASE MIRRORING SQL Server 2005

Witness server role:

In the database mirroring topology you may have an optional third server called the witness. Witness server is required for enabling automatic failover from principal to mirror server or vice-versa. Unlike principal and mirror servers, the witness server does not serve the database. The role of the witness is to verify whether a given partner server is up and functioning. Supporting automatic failover is the only function for witness server. It uses quorum to identify which server holds the principal copy and which server holds the mirror copy of the database.

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.

The following illustration shows a high-safety mode session with a witness.

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

 Software and Hardware Recommendations

We strongly recommend that the witness reside on a separate computer from the partners. Unlike database mirroring partners, which are supported only by SQL Server 2005 Standard Edition and SQL Server 2005 Enterprise Edition, witnesses are also supported by SQL Server 2005 Workgroup Edition and SQL Server 2005 Express Edition. A witness can run on any reliable computer system that supports SQL Server 2005, but we recommend that every server instance that is used as a witness correspond to the minimum configuration that is required for SQL Server 2005 Standard Edition. For more information about these requirements,

 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

Asynchronous Database Mirroring (High-Performance Mode)

When transaction safety is set to OFF, the database mirroring session operates asynchronously. Asynchronous operation supports only one operating mode—high-performance mode. This mode enhances performance at the expense of high availability. High-performance mode uses just the principal server and the mirror server. Problems on the mirror server never impact the principal server. On the loss of the principal server, the mirror database is marked DISCONNECTED but is available as a warm standby.

High-performance mode, supports only one form of role switching: forced service (with possible data loss), which uses the mirror server as a warm standby server. Forced service is one of the possible responses to the failure of the principal server. Because data loss is possible, you should consider other alternatives before forcing service to the mirror. For more information, see "Responding to Failure of the Principal," later in this topic.

The following figure shows the configuration of a session using high-performance mode.

In high-performance mode, as soon as the principal server sends the log for a transaction to the mirror server, the principal server sends a confirmation to the client, without waiting for an acknowledgement from the mirror server. Transactions commit without waiting for the mirror server to write the log to disk. Asynchronous operation permits the principal server to run with minimum transaction latency.

The mirror server attempts to keep up with the log records sent by the principal server. But the mirror database might lag somewhat behind the principal database, though typically the gap between the databases is small. However, the gap can become substantial if the principal server is under a heavy work load or the system of the mirror server is over loaded.

 When Is High-Performance Mode Appropriate?

High-performance mode can be useful in a disaster-recovery scenario in which the principal and mirror servers are separated by a significant distance and where you do not want small errors to impact the principal server.

Log shipping can be a supplement to database mirroring and is a favorable alternative to asynchronous database mirroring. For information about the advantages of log shipping, see Configuring High Availability. For information on using log shipping with database mirroring.

 The Impact of a Witness on High-Performance Mode

If you use Transact-SQL to configure high-performance mode, whenever the SAFETY property is set to OFF, we strongly recommend that the WITNESS property also be set to OFF. A witness can coexist with high-performance mode, but the witness provides no benefit and introduces risk.

If the witness is disconnected from the session when either partner goes down, the database becomes unavailable. This is because, even though high-performance mode does not require a witness, if one is set, the session requires a quorum consisting of two or more server instances. If the session loses quorum, it cannot serve the database.

When a witness is set in a high-performance mode session, the enforcement of quorum means that:

• If the mirror server is lost, the principal server must be connected to the witness. Otherwise, the principal server takes its database offline until either the witness or mirror server rejoins the session.
  
• If the principal server is lost, forcing service to the mirror server requires that the mirror server be connected to the witness.
  

For information about the types of quorums,

 Responding to Failure of the Principal

When the principal fails, the database owner has several choices, as follows:

• Leave the database unavailable until the principal becomes available again.
  If the principal database and its transaction log are intact, this choice preserves all of the committed transactions at the expense of availability.
  
• Stop the database mirroring session, manually update the database, and then begin a new database mirroring session.
  If the principal database is lost but the principal server is still running, immediately attempt to back up the tail of the log on the principal database. If the tail-log backup succeeds, removing mirroring may be your best alternative. After removing mirroring, you can restore the log onto the former mirror database, which preserves all of the data.
  

  If the tail-log backup failed and you cannot wait for the principal server to recover, consider forcing service, which has the advantage of maintaining the session state.

• Force service (with possible data loss) on the mirror server.
  Forced service is strictly a disaster recovery method and should be used sparingly. Forcing service is possible only if the principal server is down, the session is asynchronous (transaction safety is set to OFF), and either the session does not have any witness (the WITNESS property is set to OFF) or the witness is connected to the mirror server (that is, they have quorum).
  Forcing service causes the mirror server to assume the role of principal and serve its copy of the database for clients. When service is forced, whatever transaction logs the principal has not yet sent to the mirror server are lost. Therefore, you should limit forced service to situations where possible data loss is acceptable and immediate database availability is critical. For information on how forced service works and on best practices for using it

Quorum: How a Witness Affects Database Availability

Whenever a witness is set for a database mirroring session, quorum is required. Quorum is a relationship that exists when two or more server instances in a database mirroring session are connected to each other. Typically, quorum involves three interconnected server instances. When a witness is set, quorum is required to make the database available. Designed for high-safety mode with automatic failover, quorum makes sure that a database is owned by only one partner at a time.

If a particular server instance becomes disconnected from a mirroring session, that instance loses quorum. If no server instances are connected, the session loses quorum and the database becomes unavailable. Three types of quorum are possible:

• A full quorum includes both partners and the witness.
  
• A witness-to-partner quorum consists of the witness and either partner.
  
• A partner-to-partner quorum consists of the two partners.
  

The following figure shows these types of quorum.

As long as the current principal server has quorum, this server owns the role of principal and continues to serve the database, unless the database owner performs a manual failover. If the principal server loses quorum, it stops serving the database. Automatic failover can occur only if the principal database has lost quorum, which guarantees that it is no longer serving the database.

A disconnected server instance saves its most recent role in the session. Typically, a disconnected server instance reconnects to the session when it restarts and regains quorum.

Important:
The witness should be set only when you intend to use high-safety mode with automatic failover. In high-performance mode, for which a witness is never required, we strongly recommend setting the WITNESS property to OFF. For information about how a witness affects database availability in a high-performance mode session,

 Quorum in High-Safety Mode Sessions

In high-safety mode, quorum allows automatic failover by providing a context in which the server instances with quorum arbitrate which partner owns the role of principal. The principal server serves the database if it has quorum. If the principal server loses quorum when the synchronized mirror server and witness retain quorum, automatic failover occurs.

The quorum scenarios for high-safety mode are as follows:

• A full quorum that consists of both partners and the witness.
  Ordinarily, all three server instances participate in a three-way quorum, called a full quorum. With a full quorum, the principal and mirror servers continue to perform their respective roles (unless manual failover occurs).
  
• A witness-to-partner quorum that consists of the witness and either partner.
  If the network connection between the partners is lost because one of the partners has been lost, two cases are possible:
  
  • The mirror server is lost, and the principal server and witness retain quorum.
    In this case, the principal sets its database to DISCONNECTED and runs with mirroring in a SUSPENDED state. (This is referred to as running exposed, because the database is currently not being mirrored.) When the mirror server rejoins the session, the server regains quorum as mirror and starts resynchronizing its copy of the database.
    
  • The principal server is lost, and the witness and the mirror server retain quorum.
    In this case, automatic failover occurs. For more information.
    
  Rarely, the network connection between failover partners is lost while both partners remain connected to the witness. In this event, two, separate witness-to–partner quorums exist, with the witness as a liaison. The witness informs the mirror server that the principal server is still connected. Therefore, automatic failover does not occur. Instead, the mirror server retains the mirror role and waits to reconnect to the principal. If the redo queue contains log records at this point, the mirror server continues to roll forward the mirror database. On reconnecting, the mirror server will resynchronize the mirror database.
  
• A partner-to-partner quorum that consists of the two partners.
  As long as the partners retain quorum, the database continues in a SYNCHRONIZED state, and manual failover remains possible. Without the witness, automatic failover is not possible; but when the witness regains quorum, the session resumes regular operation, and automatic failover is supported again.
  
• The session loses quorum.
  If all the server instances become disconnected from each other, the session is said to have lost quorum. As server instances reconnect to each other, they regain quorum with each other.
  
  • If the principal server reconnects with either of the other server instances, the database becomes available.
    
  • If the principal server remains disconnected, but the mirror and witness reconnect to each other, automatic failover cannot occur because data loss might occur. Therefore, the database remains unavailable, until the principal server rejoins the session.
    
  • When all three server instances have reconnected, full quorum is regained, and the session resumes its regular operation.
    

When a session has a partner-to-partner quorum, if either partner loses quorum, the session loses quorum. Therefore, if you expect the witness to remain disconnected for lots of time, we recommend that you temporarily remove the witness from the session. Removing the witness removes the requirement for quorum. Then, if the mirror server becomes disconnected, the principal server can continue to serve the database. For information about how to add or remove a witness.

How Quorum Affects Database Availability

The following illustration shows how the witness and the partners cooperate to make sure that, at given time, only one partner owns the role of principal and only the current principal server can bring its database online. Both scenarios start with full quorum, and Partner_A in the principal role and Partner_B in the mirror role.

Scenario 1 shows how after the original principal server (Partner_A) fails, the witness and mirror agree that the principal, Partner_A, is not available any longer and form quorum. The mirror, Partner_B then assumes the principal role. Automatic failover occurs, and Partner_B, brings its copy of the database online. Then Partner_B goes down, and the database goes offline. Later, the former principal server, Partner_A, reconnects to the witness regaining quorum, but on communicating with the witness, Partner_A learns that it cannot bring its copy of the database online, because Partner_B now owns the principal role. When Partner_B rejoins the session, it brings the database back online.

In Scenario 2, the witness loses quorum, while the partners, Partner_A and Partner_B, retain quorum with each other, and the database remains online. Then the partners lose their quorum, too, and the database goes offline. Later, the principal server, Partner_A, reconnects to the witness regaining quorum. The witness confirms that Partner_A still owns the principal role, and Partner_A brings the database back online.