11.4. Mirroring modes
Later in the chapter we'll go through the process of preparing for and setting up a mirroring session. For now, let's concentrate on the process behind the delivery of transactions from the principal to the mirror during a mirroring session, and how this process is performed based on the mirroring mode.
A database mirroring session begins with the mirror instance identifying the log sequence number (LSN) of the last transaction applied to the mirror database. The mirror instance then obtains any outstanding transactions from the transaction log of the principal database. The outstanding transactions received from the principal instance are written to the transaction log of the mirror database and rolled forward. The outstanding transactions to roll forward are known as the redo queue, and the depth of this queue determines the catch-up time and therefore the minimum time to fail over the principal role to the mirror database.
The mirroring process for a synchronous mirroring session is summarized in figure 11.9.
Figure 11.9. Synchronous database mirroring. In asynchronous mirroring, the transaction commits on the principal database after step 2.
As updates on the principal database continue, the transactions are streamed from the principal's transaction log to the mirror's transaction log and rolled forward on the mirror database. The mirroring mode, asynchronous (high performance) or synchronous (high safety), determines how the principal's transactions are sent and received.
11.4.1. High performance (asynchronous)
Asynchronous mirroring is only available in the Enterprise edition of SQL Server. Under asynchronous mode, a transaction is committed on the principal as soon as it's sent to the mirror; it doesn't wait for an acknowledgment from the mirror that the transaction has been written to the mirror's transaction log, nor is the principal affected in any way by a failure at the mirror (other than a loss of failover capabilities). As such, asynchronous mirroring is used when transaction performance at the principal is of prime concern.
The high-performance nature of asynchronous mode comes with a reduction in high availability. In cases where the transaction load at the principal is very high, or the mirror server is overloaded (or both), the redo queue on the mirror may become very deep, increasing failover time. Further, given the transaction delivery method, there's no guarantee that the mirror partner receives and applies each transaction.
The only failover option for asynchronous mirroring is forced service, which is only available if the principal instance is disconnected from the mirror. When this option is invoked (we'll cover this feature later in this chapter), the mirroring database assumes the role of principal.
Given the possibility of data loss, the forced service failover option should be used as a last resort. If you're considering using this option due to failure of the principal, consider these alternatives:
You can wait for the principal server to recover.
If the downtime is unacceptable and service needs to be resumed immediately, attempt to back up the tail of the transaction log on the principal. If this succeeds, mirroring can be removed and the tail of the log restored to the mirror database and brought online.
Asynchronous mode mirroring is typically used in disaster-recovery designs where the principal and mirror servers are in different physical locations and the network connectivity between them may lead to unacceptably large transaction latency under the synchronous mode. If the possibility of some data loss is accepted as a consequence of the highest performance, asynchronous mirroring presents a good disaster-recovery option, but for situations in which zero data loss is the target, consider high-safety synchronous mirroring.
11.4.2. High safety (synchronous)
Synchronous mirroring is available in both the Standard and Enterprise editions of SQL Server. In synchronous mode, transactions aren't committed on the principal database until written, or hardened, to the transaction log on the mirror database. While this increases transaction latency, synchronous mirroring ensures each transaction is recoverable on the mirror database, and is therefore an excellent solution for protecting mission-critical data.
When running in high-safety mode, special consideration needs to be given to long-running or intensive operations (or both) such as index rebuilds and bulk loads. The load from these types of operations often leads to a measurable reduction in the performance throughput. While the suspend option, discussed later in the chapter, can assist here, such impact should be carefully measured in a testing environment before production implementation.
A crucial consideration when choosing high-safety mirroring is the network latency between the principal and mirror instances. The excellent Microsoft whitepaper [] "Database Mirroring Best Practices and Performance Considerations" highlights a connection between network latency, transactions per second, and transaction response time. One of the things that stands out, as shown in figure 11.10, is that once the network latency increases beyond 50ms, the effect on transaction throughput and response time is dramatic.
[] Database Mirroring Best Practices and Performance Considerations, Sanjay Mishra, Microsoft Corporation, February 2006. Links and further details available at http://www.sqlCrunch.com/HA.
Figure 11.10. As network latency increases, the impact on transaction response time and throughput increases.
The average local area network (LAN) typically has a latency of less than 5ms with metropolitan area networks (MANs) and wide area networks (WANs) anywhere up to 200ms or more. Before selecting synchronous mirroring, perform thorough testing with actual or simulated network latencies to measure the performance impact on the expected workload.
When the principal and mirror instances are separated over large distances using a WAN, asynchronous mirroring is typically used as part of a disaster-recovery solution. In LANs or low-latency MANs, synchronous mirroring is often deployed in preventing/reducing downtime for both planned and unplanned outages.
Based on the mirroring mode, and the presence or absence of a witness instance, there is a variety of failover options.