Featured Database Articles

A Mirror between Oracle and SQL Server

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

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.

In Closing

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