Database Reference
In-Depth Information
The portion of transaction log that has yet to be replayed is called the
redo queue
. You should monitor the sizes
of both the send queue on the principal server and the redo queue on the mirror server. The size of the send queue
indicates possible data loss in cases of principal server failure. The size of the redo queue indicates how many log
records must be replayed, and thus how long it could take to bring the mirror database back online after failover.
You can also set up alerts when some of the metrics exceed predefined thresholds. Obviously, the value of the
thresholds depends on the business's requirements, such as the availability SLA and possible data loss in cases of
asynchronous mirroring.
■
you need to test how database maintenance affects the size of the redo queue. some operations, such as an
index rebuild or database shrink, can generate an enormous amount of log records, which, in turn, makes the redo queue
very big. this can lead to a long crash recovery process in case of a failover, which could prevent you from meeting the
availability requirements defined in the sla.
Tip
Synchronous database mirroring is available in two different modes:
High Protection
and
High Availability
.
The only difference between these two modes is automatic failover support. SQL Server supports automatic failover in
High Availability mode; however, it requires you to have a third SQL Server instance,
witness
, which helps to support
quorum what server must work as the principal.
■
the quorum indicates that servers, which participated in the database mirroring session, agreed on their roles;
that is, which server worked as the principal and which worked as the mirror. in practice, quorum can be established as
long as at least two servers (from principal, mirror, and witness) can communicate with each other. We will discuss what
happens with mirroring when one or more servers are unavailable later in the chapter.
Note
You can use any edition of SQL Server, including the Express edition, as the witness. It is critical, however, that
the witness instance be installed on another physical server to avoid the situation where a hardware malfunction of a
single physical server kicks multiple SQL Server instances offline and prevents a quorum from being established.
Table
31-1
shows the similarities and differences among different database mirroring modes.
Table 31-1.
Database Mirroring modes
High Performance
High Protection
High Availability
Commit
Asynchronous
Synchronous
SQL Server Edition
Enterprise Edition only
Enterprise and Standard Editions
Data loss
Possible
Not possible when DB is in SYNCHRONIZED state
Automatic failover
Not supported
Not supported
Supported with witness server
Performance impact
None
Network and mirror I/O subsystem latency
