Note that all of the numeric columns have precision and scale, whereas the description for EMP in SQL*Plus shows EMPNO, MGR and DEPTNO as NUMBER 4, 4 and 2. The primary key constraint is picked up as well, and the naming suggests Microsoft Heterogeneous Replication via MSHREPL.

The data file, usable via the bcp utility in MSSQL, looks XML-ish, but is not. Records are separated by <,@g>, columns or changes in field by , and null values by a blank line (somewhat of a departure where whitespace is typically ignored, but in these files, it matters).

There are several other objects worth examining, and the curious reader is left to do that upon his or her own accord. With everything all said and done, how do you see what takes place? One interface into that is the Replication Monitor. The entry and setup of this monitor is quite similar to how the setup for Database Mirroring Monitor is performed.

Right-click Replication and select Launch Replication Monitor.

Select Add Publisher after right-clicking My Publishers to add the Oracle publisher (or use the Action menu if so desired). The ensuing steps are fairly self-explanatory, and the end result is that the MyOraclePub distribution is added to the list of publishers. Once enabled, select it and view the activity (there may be none) under the various tabs.

Of interest to us is the Agents tab.

Right-click>View Details the first line (completion of the Snapshot Agent) and view the history of how your selected article was replicated via a snapshot.

For a 14 row table, with both RDBMSs running on the same server, the time to snapshot was about ten seconds. Obviously, a very small example in terms of data quantity, but you may want to consider testing the amount of time it takes to snapshot a significantly sized table.

All of this may seem like a lot of work to replicate data between Oracle and SQL Server, and quite frankly, it is. There is lots of room for error, and even with all steps being faithfully followed via wizards and the like, you can still wind up with errors related to data access (MSSQL forums have an abundance of questions related to where a publisher or distributor test errors out because of a data access issue, not to mention what it takes to clean up an aborted or failed replication installation).

Within Oracle, we have other options, and perhaps the simplest is to go back to what was mentioned as the framework concept in Oracle as a Data Source, that is, use a materialized view. An even simpler approach would be the use of an auditing table and a job.

We don’t care about the change history in the replicated set of data, but rather, we care about the current state of data being present. If EMPNO 7369 exists in the audit table, then we are either inserting into the replication table or updating the existing record. Once all records are processed in the audit table, it is flushed and initialized for the next set of transactions to be replicated. What’s even better about this simpler approach is that the target data store – SQL Server in this example – does not need to be Enterprise Edition level. Standard Edition will suffice quite nicely, along with its much lower cost. We can dodge the big bucks for bells and whistles paid for otherwise that can be emulated by much simpler means.

In Closing

A more formal comparison between replication methods within Oracle and within SQL Server can be found in an October 2008 Oracle white paper (“Technical Comparison of Oracle Database 11g versus SQL Server 2008”). Oracle Corporation, never bashful in its claims, states right up front than in all areas, “Oracle continues to provide a more robust and flexible solution.”

The paper compares Oracle’s features against those of SQL Server’s, and does not go into the pros and cons of using one RDBMS as the data source for the other. Overall, there is more than one way to publish Oracle data into SQL Server. It may be best for Oracle to take care of the transactional updates and snapshots, and then use already updated materialized views as the Oracle data source for SQL Server.