Database mirroring is primarily an availability feature, but it has been gaining popularity as a disaster-recovery option. Asynchronous mirroring, in particular, is being leveraged for disaster recovery in scenarios where log shipping might have been used in the past. Asynchronous mirroring offers several enhancements over log shipping, making it an attractive option.
Asynchronous Mirroring vs. Log Shipping
Asynchronous mirroring and log shipping share several common traits:
- Both can be used for increased availability and for disaster recovery.
- Both protect at the database level. Anything external to the database must be synchronized separately.
- Both provide limited point-in-time, read-only capabilities.
- Neither one supports the simple recovery model.
Although they’re similar, each technology has some unique advantages over the other. The most obvious advantage with log shipping is its ability to log ship to multiple secondary databases. In addition, log shipping supports several features that asynchronous mirroring doesn’t, such as the bulk-logged recovery model, delayed replaying of log records, and FILESTREAM support. The features supported by asynchronous mirroring but not log shipping include simple reversal of roles, database snapshots, near real-time transfer of transactions, automatic resynchronization of partners after a role change, support for failover of a replication publication, and transparent client redirection.
One of the most compelling reasons to use asynchronous mirroring instead of log shipping is simplified management. This is particularly true for those times when you need to temporarily fail over the disaster-recovery server for maintenance or some other reason.
Understanding Asynchronous Mirroring
When I talk with administrators about implementing asynchronous mirroring as a disaster-recovery solution, one of their primary questions is about the potential for data loss. Not many people fully understand the nature of the data loss potential, and several misconceptions have arisen as a result. If you want to understand how data loss can occur, you first need to understand how the mirroring modes operate.
It’s easier to understand how the mirroring modes work if you think of mirroring as a system of queues. There’s a send queue on the principal server (aka principal) and a redo queue on the mirror server (aka mirror). The send queue’s job is to send log records to the redo queue as they are generated. The redo queue accepts the log records, hardens them to the mirror’s log, and sends back acknowledgements. The mirroring queues aren’t real service broker queues. The best way to describe them is as special views into the transaction log. The send queue is looking directly at the principal’s log file, and the redo queue is looking directly at the mirror’s log file.
With synchronous mirroring, when a transaction is committed, the log record is sent to the redo queue. The redo queue hardens it to the log and sends an acknowledgement back to the principal. When the principal receives the acknowledgement, it completes the commit operation on the principal. If the hardening fails on the mirror, the mirroring session will be suspended until the cause of the failure can be found and corrected. If the principal doesn’t receive confirmation from the mirror, the connection times out and the principal transitions to a disconnected state. When the principal is disconnected, transactions continue to be queued in the send queue and mirroring continues to operate in asynchronous mode until the session reconnects and the mirror catches up with the principal. Figure 1 illustrates how synchronous mirroring works.
Figure 1: How synchronous mirroring works
When mirroring is operating asynchronously, the principal doesn’t wait for a transaction to be hardened on the mirror. As soon as it sends its log record, it completes the commit operation on the principal. If the record hardening fails on the mirror, the mirror disconnects from the mirroring session and the session is suspended. Activity on the principal continues as normal, and transactions continue to be queued in the send queue. When the condition that caused the failure is corrected, the mirroring session can be resumed. Figure 2 illustrates how asynchronous mirroring works.
Figure 2: How asynchronous mirroring works
Figure 2 also shows when data loss might occur. If the principal fails or crashes when running asynchronously, there’s a limited window for potential data loss. Data that has been committed on the principal but has not yet been hardened to the log on the mirror could be lost if you force service on the mirror. If you wait for the principal to return to service, committed transactions won’t be lost.