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:
- Transactions execute against a mirrored database on the Principal Instance
- 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.
- The transaction data is sent over the wire to the Mirror Instance via the Database Mirror Endpoint.
- The transactions land in the Mirror Instance log buffer and …
- … are hardened to the Mirror Instance Transaction Log.
- 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.
- Finally, once the ‘ACK’ that the transaction is hardened on the Mirror Instance, the transaction is written to the Principal Instance T-log.