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.

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