Imagine an Oracle database facing a SQL Server
database as if each were looking at a mirror and seeing their
somewhat distorted reflections. The Oracle database entity lifts
its right hand and expects to see four fingers and a thumb
reflected back, but instead, sees five thumbs and a claw. And to
be fair, when the SQL Server (MSSQL from here on out) database
exposes its management interface (Management Studio) and expects
to something similar, it will be disappointed to see Oracle’s WSOD
(white screen of death) in the form of SQL*Plus. However, there
are some areas where the mirroring will match, and what this
article is about is database mirroring in MSSQL and how it
corresponds to what takes place in Oracle.
First off, a mirrored database in MSSQL is
analogous to a standby database in Oracle. To be precise, we must
take into account the differences between what a database is in
each system. MSSQL operates as an instance, where an instance
contains several databases. You logon to an instance, and then
choose which database to work with. In Oracle, the simple model
(ignoring RAC) is one where a database is associated with only one
instance. The standby database in Oracle is a complete picture, so
to speak, of the primary database. The mirrored database in MSSQL
is only that database, and does not include external items such as
agents, logons, and tasks (those and more may need to be
separately created/replicated on the mirror).
In terms of servers, Oracle’s primary and
standby configuration (assuming this is for real) require a
minimum of two. In MSSQL, the minimum amount is two or three,
depending on your choice of high availability versus high
protection and high performance. To enable automatic failover, a
third server is needed, and it is identified as the witness (the
other two being the principal and the mirror). As an analogy, you
can consider the witness to be like a member of a cluster, and if
the quorum concludes that a member is off, it is voted off the
island. Stated more colloquially, it follows the “Shoot the Other
Machine in the Head” high availability model (also known as
STONITH or STOMITH, even though this really isn’t a cluster, but
it gets the point across).
In a transaction in Oracle, the log buffer is
flushed/written to the redo log before dirty data blocks are
written to datafiles (ignoring write-ahead cases). That write to
the redo logs is necessary for things like instance failure
(2-phase recovery process with roll forward and roll back). MSSQL
also acknowledges the importance of getting log buffer data
written to disk, but here, it is called hardening. The transaction
log buffer is written to disk, or hardened, and then a block (may
be more than one) of log records is sent to the mirror. The mirror
receives the block into a buffer, and in turn hardens the block.
How does MSSQL keep the principal and mirror
coordinated with respect to changes? Oracle users are quite
familiar with the SCN, and MSSQL mirrors that mechanism via use of
mirroring_failover_lsn (roughly, a log sequence number). MSSQL
differs from Oracle in that it considers the transactions to be
separate transactions (two transactions on two servers) as opposed
to a distributed transaction (one waits for the commit on the
remote before committing itself).
Another similar, but somewhat distorted
reflection concerns redo logs and transaction logs. In Oracle,
archived redo logs can be sent to a remote (a.k.a. standby) server
to have the archived redo logs applied against the standby. In
MSSQL, the transaction logs (or log, doesn’t have to be more than
one) are not shipped, but as mentioned above, the log buffer data
is what gets sent over the network. This leads to yet another
mirrored reflection: the backup or recovery mode.
Oracle is pretty cut and dry when it comes to
which mode you are in: archivelog mode or not. If archived redo
logs are shipped or transmitted to a remote server, then the
primary is obviously in archivelog mode as how else are those
files generated. Operating in this mode allows for minimal, if
any, data loss as recovery can be implemented up to virtually any
point in time before a failure (whatever the nature of the failure
is). The reflection in MSSQL-land is similar, but there are three
states to choose from.
SQL Server Books Online, as do many other
sources online, covers the differences among the three recovery
models used in MSSQL. The quick and dirty comparisons are that the
full model in MSSQL corresponds to being in archivelog mode in
Oracle; the simple model is like being in noarchivelog mode; and
the bulk model is similar to using direct path inserts, append
hints, or nologging modes of operation.
Given the descriptions of the three recovery
models (where it is very easy to switch among them, no
shutdown/re-start needed) in MSSQL and the preceding discussion of
the log buffers versus archived redo logs, it should be easy to
figure out that a requirement to mirror a database in MSSQL is to
have the database’s recovery model set to full. The simple model
seems like it could work, but that model maintains a minimal
amount of data in the transaction log, and upon backups, the log
is truncated, so if you were waiting for a transaction to be sent
to a mirror and the log were truncated, the process would break.
Speaking of breaking, that is exactly what the
purpose of mirroring (or having a standby) deals with: what
happens when the principal breaks or suffers a failure? We want
the system to fail over to the mirror or standby. How does that
take place? We can have it done automatically or do it ourselves
(manually). These choices require other items or features to be in
place. In MSSQL, automatic failover is characterized by being in
an HA mode, transaction safety is full, data transfer is
synchronous, and a witness server is required. To operate in this
mode also requires the use of the Enterprise Edition. High
protection and high performance can all be implemented using the
Standard Edition (and the witness server can be either).
There are other edition choices in MSSQL, but
these don’t have as “clean” of a reflection in Oracle, and those
editions include Developer, Workgroup and SQL Express. The witness
server, for example, can be any edition, and if you wanted to take
snapshots of what the mirror has, you will need the Enterprise or
What of the mirror (or standby) and your
ability to query data out of it à la a read-only database used for
reporting? In setting up the partners (the group formed by the
principal and the mirror), their recovery states come into play.
The mirror is established (using the Configure Database Mirroring
Security Wizard is the easiest approach) on the remote/mirror
server using the same database name (the instance name can and
will most likely be different) and the database is set to
NORECOVERY, as it is always recovering. In MSSQL, a recovering
database is not available, so without going above the basics, it
cannot be used as a read-only database by other users.
To get around this limitation, you can take a
snapshot of the mirror and make that “image” available for users.
As mentioned, this requires the Enterprise (or Developer) Edition.
This implies that users have knowledge of the snapshot database,
that is, how to access it (along the lines of a TNSNAMES entry).
How do you tell the application which database (server in this
case) to use? Courtesy of configuration files used in .NET, you
can establish a primary and a failover partner. If you have
configured a standby database in Oracle (the “old” standby or Data
Guard flavors), you can see the similarities.
The take-away from this article includes a
better understanding of how another major RDBMS implements
mirroring or replication along the lines of what Oracle has
available. In trying to learn or explain how your RDBMS of choice
works (i.e., Oracle), having another model to draw from can help
clarify what takes place in your system. One example I found
particularly useful is the relationship between archiving (and
nologging) in Oracle and that of the three recovery models used in
MSSQL. Other terms used in the MSSQL scenario (partner, principal,
witness, mirror, etc.) can help frame or identify the components
in Oracle’s implementation of mirroring a database.
To gain a better appreciation of how mirroring
works and is implemented, you can run two separate (they are
anyway by definition) instances of MSSQL on an XP or 2003 computer
and go through the setup steps as shown on the MSDN Books Online
site. Download and attach the AdventureWorks database (similar to
Oracle’s HR/SH/etc. schemas, but it doesn’t come pre-installed
anymore), and then mirror it to the server hosting the mirror
(which is the same PC in this case). Not only will this expose you
to a relatively complex feature in another RDBMS, it will also
give you an appreciation of what MSSQL can do (or conversely, some
things you wish Oracle would do differently).