Category Archives: HA|DR

Overview: SQL Database Mirroring High-Availability with Automatic-Failover

A High-Availability with Automatic-Failover SQL Database Mirroring Configuration consists of a Principal Instance and Database, a Mirror Instance and Database, and a Witness Instance.  Each instance must have a Database Mirror Endpoint – a special TCPIP endpoint with defined listener IP and port. A mirroring session is configured between these servers utilizing the dedicated mirror endpoints.

A ‘ping’ request bounces around between the 3 servers as a ‘heart-beat’ or ‘is-alive’ type of check.  Given a failure of the ‘is-alive’ test any 2 servers still communicating can form a quorum.  In the case where the quorum members are the Mirror Instance and the Witness Instance these two instances can agree to automatically fail-over the mirror session so that the Mirror Instance becomes the ‘new’ Principal Instance.

The following outline and accompanying diagram illustrates the transaction flow in a High Availability with Automatic Failover SQL Data Mirror Configuration:

synch DBM diagram 

  1. Transactions execute against a mirrored database on the Principal Instance
  2. Before the transactions are written to the transaction log, they persist in the log-buffer memory space and here is where Database Mirroring picks them up.  The transactions remain here – unhardened – until the mirror process completes.
  3. The transaction data is sent over the wire to the Mirror Instance via the Database Mirror Endpoint.
  4. The transactions land in the Mirror Instance log buffer and …
  5. … are hardened to the Mirror Instance Transaction Log.
  6. An acknowledgement message that the transaction is committed to the Mirror SQL Instance t-log is sent to the Principal SQL Instance. Also, the transactions are applied to the Mirror Database as the Mirror Instance Transaction Log is continuously restored to the Mirror Database.
  7. Finally, once the ‘ACK’ that the transaction is hardened on the Mirror Instance, the transaction is written to the Principal Instance T-log.