Database Mirroring

• Works only if the database is in full recovery model
• master, msdb, tempdb, or model databases cannot be mirrored
• Two copies of a single database that must reside on different server instances of SQL Server Database Engine.
• One server instance serves the database to clients (the principal server).
• The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session.
• When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions.
• When the session is not synchronized (asynchronized), the mirror server is typically available as a warm standby server (with possible data loss).
• Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance – high performance mode. Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.- high safety mode

Transaction safety is controlled by the SAFETY property of the ALTER DATABASE statement. On a database that is being mirrored, SAFETY is either FULL or OFF.

• If the SAFETY option is set to FULL, database mirroring operation is synchronous, after the initial synchronizing phase. If a witness is set in high-safety mode, the session supports automatic failover.
• If the SAFETY option is set to OFF, database mirroring operation is asynchronous. The session runs in high-performance mode, and the WITNESS option should also be OFF.

Role Switching

Within the context of a database mirroring session, the principal and mirror roles are typically interchangeable in a process known as role switching. Role switching involves transferring the principal role to the mirror server. In role switching, the mirror server acts as the failover partner for the principal server. When a role switch occurs, the mirror server takes over the principal role and brings its copy of the database online as the new principal database. The former principal server, if available, assumes the mirror role, and its database becomes the new mirror database. Potentially, the roles can switch back and forth repeatedly.

The following three forms of role switching exist.

• Automatic failoverThis requires high-safety mode and the presence of the mirror server and a witness. The database must already be synchronized, and the witness must be connected to the mirror server.

  The role of the witness is to verify whether a given partner server is up and functioning. If the mirror server loses its connection to the principal server but the witness is still connected to the principal server, the mirror server does not initiate a failover.

• Manual failoverThis requires high-safety mode. The partners must be connected to each other, and the database must already be synchronized.
• Forced service (with possible data loss)Under high-performance mode and high-safety mode without automatic failover, forcing service is possible if the principal server has failed and the mirror server is available.

  Important
  High-performance mode is intended to run without a witness. But if a witness exists, forcing service requires that the witness is connected to the mirror server.

In any role-switching scenario, as soon as the new principal database comes online, the client applications can recover quickly by reconnecting to the database.