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