SQL Server 2008 : Database mirroring overview, Mirroring modes

10/10/2013 9:07:33 PM

1. Database mirroring overview

Available in the Standard and Enterprise editions of SQL Server, database mirroring is used to maintain a hot standby copy of a database. Transactions generated on the source (principal) database are sent to the mirror over an encrypted, compressed log stream and applied in either a synchronous or asynchronous manner. You can choose between the two modes to prioritize transaction safety or performance.

If the principal instance fails, an optional witness server can be used to automatically bring the mirror database online with zero data loss. With the necessary connection logic, applications can automatically reconnect to the mirrored database. The combination of synchronous mirroring with automatic failover and application reconnection logic delivers a truly hands-free high-availability solution for mission-critical SQL Server databases.

Unlike log shipping (which applies transactions to the database copy using scheduled transaction log backups and restores), database mirroring streams transactions directly to the mirror database in real time. If you're using the synchronous mode, transactions won't commit at the principal until they're received and written to the transaction log at the mirror. As such, databases in a mirroring solution are either an exact mirror, or seconds apart, which explains why we use the term hot standbywarm standby. rather than

Like log shipping, database mirroring is established on an individual database-by-database basis. This is in contrast to failover clustering, which provides protection for an entire instance, and all the databases contained within. But unlike clustering, mirrored databases are typically located on servers in different geographical locations connected via high-speed communication links. Such configurations assist in the creation of disaster-recovery designs for mission-critical databases.

1.1. Terminology

Like failover clustering, database mirroring comes with a healthy dose of new terminology:

  • Principal and mirror database—In a database mirroring solution, there's a single source database that applications connect to. This is known as the principalmirror database is the copy being maintained for failover purposes. If failover occurs, the roles of principal and mirror are swapped. database. The

  • Principal and mirror instance—The SQL Server instance containing the principal database is the principal instance. The mirror instance is the instance containing the mirror database. These instances are frequently located in different locations as part of a disaster-recovery design. A SQL Server instance can't be both a mirror and a principal instance for the same database, but can have different roles for different databases—for example, an instance can be a mirror instance for database A and a principal instance for database B.

  • Failover—A failover is the process of moving the principal role to the mirror. This can be an automatic process using a witness, or can be initiated manually.

  • Witness—A witness is an optional role in a mirroring solution that enables an independent SQL Server instance to observe the mirroring process and provide automatic failover where required.

  • High-performance mode—In high-performance mode, transactions are sent from the principal to the mirror database asynchronously; therefore, depending on the transaction rate and network bandwidth/congestion, the mirror database may fall behind the principal by longer than expected.

  • High-safety mode—In high-safety mode, transactions are sent from the principal to the mirror database synchronously. For mission-critical databases connected over high-speed network interfaces, this is the option used for achieving zero data loss automatic failover with a witness instance.

  • Forced service—In high-performance mode, or high-safety mode without automatic failover, forced service forces the principal role to move to the mirror database, which may result in data loss.

  • Endpoint—Principal and mirror databases communicate over dedicated mirroring endpoints using the TCP/IP protocol and unique port numbers.

  • Send and redo queues—In some situations, the transaction rate on the principal database may exceed the rate at which transactions can be sent to the mirror. Equally so, on the mirror they may be received faster than they can be applied. In each case, the transaction logs are used at the principal and mirror to catch up. The section of the transaction log that's yet to be sent or applied is referred to as the send queue and redo queue, respectively.

As with all high-availability technologies, database mirroring has a number of important restrictions.

1.2. Mirroring restrictions

Before we continue, let's cover some of the restrictions of database mirroring:

  • Unlike transaction log shipping, which supports the Bulk_Logged recovery model, database mirroring only works for databases using the full recovery model.

  • The mirror database can't be read unless you create a database snapshot against it.Depending on the environment and mirroring mode, a snapshot may introduce an unacceptable performance overhead.

  • Mirroring is set up for individual databases—in other words, you can't mirror at an instance level. While you can individually mirror more than one database within an instance, there are special considerations when mirroring databases with interdependencies.

  • System databases (master, model, msdb, and tempdb) can't be mirrored.

  • You can have only one mirror database for each principal, unlike log shipping where logs from a source database can be shipped to multiple destinations. However, the one database can be both log shipped and mirrored.

  • Databases involved in cross-database or distributed transactions aren't supported in database mirroring.

  • Database mirroring can't be enabled on databases containing FileStream data.

With that brief introduction in mind, let's move on and discuss the two major types of database mirroring modes: asynchronous (high performance) and synchronous (high safety).

2. Mirroring modes

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 1.

Figure 1. 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.

2.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.

Database mirroring in SQL Server 2008

First introduced in SQL Server 2005, database mirroring is improved in 2008 through automatic recovery from certain types of data corruption (Enterprise edition only) and log stream compression. Upon detection of a corrupted page, the principal and mirror databases can request fresh copies of the page from each other and overwrite the corrupted page with a good copy. Log stream compression improves the performance of database mirroring by compressing the transaction log stream between the principal and mirror and therefore reducing the network bandwidth requirements while increasing transaction throughput.

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 , 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.

2.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.

A crucial consideration when choosing high-safety mirroring is the network latency between the principal and mirror instances. One of the things that stands out, as shown in figure 2, is that once the network latency increases beyond 50ms, the effect on transaction throughput and response time is dramatic.

Figure 2. 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.

  •  SQL Server 2008 : Transaction log shipping - Usage scenarios, Setting up and monitoring log shipping
  •  SQL Server 2008 : High-availability options
  •  SQL Server 2008 : Policy-based management - Advanced policy-based management
  •  SQL Server 2008 : Policy-based management - Enterprise policy management
  •  SQL Server 2012 : Interpreting Query Execution Plans - Viewing Query Execution Plans
  •  SQL Server 2012 : SQL Server Management and Development Tools - Using the Query Editor
  •  SQL Server 2012 : SQL Server Management and Development Tools - Object Explorer (part 2)
  •  SQL Server 2012 : SQL Server Management and Development Tools - Object Explorer (part 1)
  •  SQL Server 2012 : SQL Server Management and Development Tools - Registered Servers
  •  SQL Server 2012 : SQL Server Management and Development Tools - Organizing the Interface
    PS4 game trailer XBox One game trailer
    WiiU game trailer 3ds game trailer
    Top 10 Video Game
    -   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Total War: Warhammer [PC] Demigryph Trailer
    -   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
    -   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
    -   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
    -   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
    -   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
    -   Satellite Reign [PC] Release Date Trailer
    Game of War | Kate Upton Commercial