I shall republish an updated version once I get round to phase 2 of my blogging.
Overview
Database mirroring is primarily a software
solution for increasing database availability.
Mirroring is implemented on a per-database basis.
Database mirroring provides an easy-to-manage alternative or supplement
to failover clustering or log shipping.
During a typical mirroring session, after a production server fails,
client applications can recover quickly by reconnecting to the standby server.
Database mirroring maintains two copies of a single database that must
reside on different instances of SQL Server.
Typically, these server instances reside on computers in different
locations.
One server instance serves the database to clients (the principal server), while the other
server instance acts as a hot or warm standby server (the mirror server), depending on the
configuration and state of the mirroring session.
When a database mirroring session is synchronized, database mirroring
provides a hot standby server that supports rapid fail-over with no loss of data
from committed transactions.
When the session is not synchronized, the mirror server is typically
available as a warm standby server (with possible data loss).
Benefits of Database Mirroring
- Increases data
protection.
Database mirroring provides complete or nearly complete redundancy of the data, depending on whether the operating mode is high-safety mode or high-performance mode.
- Increases availability
of a database.
In the event of a disaster, in high-safety mode with automatic failover, failover quickly brings the standby copy of the database online (With no data loss).
In the other operating modes, the database
administrator has the alternative of forcing service (With possible data loss)
to the standby copy of the database.
- Improves the
availability of the production database during upgrades.
To minimize downtime for a mirrored database, you can sequentially upgrade the instances of SQL Server that are participating in a database mirroring session.
How Database Mirroring Works
The principal and mirror servers communicate
and cooperate as partners
within a database mirroring session.
The two partners perform complementary roles in the session: the principal role and the mirror role.
At any given time, one partner performs the principal role, and the
other partner performs the mirror role.
Each partner is described as owning
its current role. The partner that owns the principal role is known as the principal server, and its copy of the
database is the current principal database. The partner that owns the mirror
role is known as the mirror server,
and its copy of the database is the current mirror database.
When database mirroring is deployed in a production environment, the
principal database is the production database.
Database mirroring involves redoing every insert, update, and delete
operation that occurs on the principal database onto the mirror database as
quickly as possible.
Redoing is accomplished by sending every active transaction log record
to the mirror server, which applies log records to the mirror database, in
sequence, as quickly as possible.
Unlike replication, which works at the logical level, database mirroring
works at the level of the physical log record.
Operating Modes
A database mirroring session runs with either synchronous or
asynchronous operation.
Under asynchronous operation, the transactions commit without waiting
for the mirror server to write the log to disk, which maximizes performance.
Under synchronous operation, a committed transaction is committed on
both partners, but at the cost of increased transaction latency.
There are two mirroring operating modes.
One of them, high-safety mode supports synchronous operation.
Under high-safety mode, when a session begins, the mirror server
synchronizes the mirror database with the principal database as quickly as
possible.
Once the databases are synchronized a committed transaction is committed
on both partners, at the cost of increased transaction latency.
The second operating mode, high-performance
mode, runs asynchronously.
The mirror server attempts to keep up with the log records sent by the
principal server.
The mirror database might lag somewhat behind the principal database,
though, typically, the gap between the databases is small.
However, the gap can become substantial if the principal server is under
a heavy work load or the system of the mirror server is over loaded.
In high-performance mode, as soon as the principal server sends a log
record to the mirror server, the principal server sends a confirmation to the
client, without waiting for an acknowledgement from the mirror server.
This means that transactions commit without waiting for the mirror
server to write the log to disk.
Such asynchronous operation permits the principal server to run with
minimum transaction latency, at the risk of some potential data loss.
All database mirroring sessions support only one principal server and
one mirror server.
High-safety mode with automatic fail-over requires a third server
instance, known as a witness.
The witness simply supports automatic failover by verifying whether the
principal server is up and functioning.
The mirror server initiates automatic failover only if the mirror and
the witness remain connected to each other after both have been disconnected
from the principal server.
Role Switching
Within the context of a database mirroring session, the principal and
mirror roles are typically interchangeable in a process known as role switching.
Role switching involves transferring the principal role to the mirror
server.
In role switching, the mirror server acts as the fail-over partner for the principal server.
When a role switch occurs, the mirror server takes over the principal
role and brings its copy of the database on-line as the new principal database.
The former principal server, if available, assumes the mirror role, and
its database becomes the new mirror database.
Potentially, the roles can switch back and forth repeatedly.
The following three forms of role switching exist:
- Automatic fail-over
This requires high-safety mode and the
presence of the mirror server and a witness.
The database must already be synchronized,
and the witness must be connected to the mirror server.
The role of the witness is to verify whether
a given partner server is up and functioning.
If the mirror server loses its connection to
the principal server, but the witness is still connected to the principal
server, the mirror server does not initiate a fail-over.
- Manual fail-over
This requires high-safety mode. The partners
must be connected to each other, and the database must already be synchronized.
- Forced service (with
possible data loss)
Under high-performance mode and high-safety
mode without automatic fail-over forcing service is possible if the principal
server has failed and the mirror server is available.
Database Mirroring Sessions
Database mirroring occurs within the context of a database mirroring
session.
When the mirror database is ready, and the server instances are
configured, the database owner can start database mirroring.
As soon as mirroring starts, each partner begins to maintain state
information in its database about that database as well as the other partner
and the witness, if any.
This state information permits the server instances to maintain a
relationship known as a database
mirroring session.
Throughout a database mirroring session, the server instances monitor
each other.
At the start of a database mirroring session, the mirror server
identifies the log sequence number (LSN) of the latest transaction log applied
on the mirror database and asks the principal server for the transaction log
for all subsequent transactions, if any.
In response, the principal server sends to the mirror server any active
log records accumulated since the last log restored to the mirror database or
sent to the mirror server.
Unsent log that has accumulated on the log disk of the principal
database is known as the send queue.
The mirror server immediately writes the incoming log to disk, where it
is held until it is applied to the mirror database.
The log waiting on the mirror's disk is known as the redo queue.
The amount of unrestored log waiting in the redo queue is an indicator
of the time required to fail over to the mirror database.
The principal server continues to make the principal database available
to clients and client connections.
After mirroring starts, each time a client updates the principal
database, on writing the transaction to the log of the principal database, the
principal server also sends that log record to the mirror server. There, the
mirror server immediately writes the log record to disk as the last record in
the redo queue.
In the background, beginning with the oldest log record, the mirror
server redoes the log on the
mirror database, record by record, as quickly as possible.
Redoing the log involves applying the queued log records to the mirror
database in sequence, starting with the oldest record.
Each log record is redone once and only once.
As the mirror server redoes the log, the mirror database is continually
rolled forward.
When the principal server truncates or shrinks the log for the principal
database, the mirror server also shrinks the log at the same point in the log
stream.
Typically, redoing quickly catches up the mirror database in relation to
the principal database.
Whether the mirror database ever completely catches up to the principal
database depends on the operating mode of the session.
Under synchronous, high-safety mode, the principal server waits to
confirm new transactions until they are written to the mirror server's log
disk.
After the accumulated log records have been sent to the mirror server,
the mirror database becomes synchronized with the principal database.
During a session, if the principal server is unable to send every log
record immediately, unsent log records accumulate in the send queue.
Under synchronous, high-safety mode, after synchronization, new unsent
log accumulates only when mirroring is paused or suspended.
Under asynchronous, high-performance mode, in contrast, unsent log
accumulates whenever the mirror server falls behind during mirroring, as well
as when mirroring is paused or suspended.
The amount of unsent log is an indicator of the possible data loss in
the event that the principal server fails.
How a Witness Affects Database
Availability
Whenever a witness is set for a database mirroring session, quorum is required.
Quorum is a relationship that exists when two or more server instances
in a database mirroring session are connected to each other.
Typically, quorum involves three interconnected server instances.
When a witness is set, quorum is required to make the database
available.
Designed for high-safety mode with automatic failover, quorum makes sure
that a database is owned by only one partner at a time.
If a particular server instance becomes disconnected from a mirroring
session, that instance loses quorum. If no server instances are connected, the
session loses quorum and the database becomes unavailable.
Three types of quorum are possible:
- A full quorum includes both
partners and the witness.
- A witness-to-partner quorum
consists of the witness and either partner.
- A partner-to-partner quorum
consists of the two partners.
As long as the current principal server has quorum, this server owns the
role of principal and continues to serve the database, unless the database
owner performs a manual failover.
If the principal server loses quorum, it stops serving the database.
Automatic failover can occur only if the principal database has lost
quorum, which guarantees that it is no longer serving the database.
A disconnected server instance saves its most recent role in the
session.
Typically, a disconnected server instance reconnects to the session when
it restarts and regains quorum.
Asynchronous Database Mirroring (High-Performance Mode)
When transaction safety is set to OFF, the database mirroring session
operates asynchronously.
Asynchronous operation supports only one operating mode—high-performance
mode.
This mode enhances performance at the expense of high availability.
High-performance mode uses just the principal server and the mirror
server.
Problems on the mirror server never impact the principal server.
On the loss of the principal server, the mirror database is marked
DISCONNECTED but is available as a warm standby.
High-performance mode supports only one form of role switching:
- Forced service (with
possible data loss), which uses the mirror server as a warm standby
server.
Forced service is one of the possible
responses to the failure of the principal server.
Synchronous Database Mirroring (High-Safety Mode)
When transaction safety is set to FULL, the database mirroring session
runs in high-safety mode and operates synchronously after an initial
synchronizing phase.
To achieve synchronous operation for a session, the mirror server must
synchronize the mirror database with the principal database.
When the session begins, the principal server begins sending its active
log to the mirror server. The mirror server writes all of the incoming log
records to disk as quickly as possible.
As soon as all of the received log records have been written to disk,
the databases are synchronized.
As long as the partners remain in communication, the databases remain
synchronized.
After synchronization finishes, every transaction committed on the
principal database is also committed on the mirror server, guaranteeing
protection of the data.
This is achieved by waiting to commit a transaction on the principal
database, until the principal server receives a message from the mirror server
stating that it has hardened the transaction's log to disk.
Note the wait for this message increases the latency of the transaction.
The time required for synchronization depends essentially on how far the
mirror database was behind the principal database at the start of the session
(measured by the number of log records initially received from the principal
server), the work load on the principal database, and the speed of the mirror
system.
After a session is synchronized, the hardened log that has yet to be
redone on the mirror database remains in the redo queue.
As soon as the mirror database becomes synchronized, the state of both
the copies of the database changes to SYNCHRONIZED.
Synchronous operation is maintained in the following manner:
- On receiving a
transaction from a client, the principal server writes the log for the
transaction to the transaction log.
- The principal server
writes the transaction to the database and, concurrently, sends the log
record to the mirror server.
- The principal server waits for an acknowledgement from the mirror server before confirming either of the following to the client: a transaction commit or a roll-back.
- The mirror server
hardens the log to disk and returns an acknowledgement to the principal
server.
- On receiving the acknowledgement from the mirror server, the principal server sends a confirmation message to the client.
- High-safety mode
protects data by requiring the data to be synchronized between two places.
All the committed transactions are guaranteed to be written to disk on the
mirror server.
High-Safety Mode with Automatic Fail-over
Automatic fail-over provides high availability by ensuring that the database is still
served after the loss of one server.
Automatic fail-over requires that the session possess a third server instance, the witness, which ideally resides on a
third computer.
Unlike the two partners, the witness does not serve the database.
The witness simply supports automatic fail-over by verifying whether the
principal server is up and functioning.
The mirror server initiates automatic fail-over only if the mirror and
the witness remain connected to each other after both have been disconnected
from the principal server.
Automatic fail-over requires the following conditions:
- The database is already synchronized.
- The failure occurs while all three server instances are connected, and
the witness and mirror server remain connected.
The
loss of a partner has the following effect:
- If the principal server becomes unavailable under the above conditions, automatic fail-over occurs. The mirror server switches to the role of principal, and it offers its database as the principal database.
- If the principal server becomes unavailable when those conditions
are not met, forcing service (with possible data loss) might be possible.
If
the session loses its witness, quorum requires both partners.
If
either partner loses quorum, both partners lose quorum, and the database
becomes unavailable until quorum is re-established.
This
quorum requirement makes sure that in the absence of a witness the database
never runs without being mirrored (Exposed).
i have a question With mirroring . will the shrink log file or Db changes at Principal automatically reflects the changes at the mirror ? If not how can i shrink log file in mirror DB.
ReplyDelete