I designed and implemented this solution one year ago to improve the communication between two systems based on distributed-heterogeneous DB : Oracle (10.2) RAC on the main system and SQL Server (2008R2) on the peripheral system located geographically far, in a place with lower bandwidth and 30ms ping time (120ms tnsping).

The SQL server application performed many accesses to the Oracle DB through ODBC and, because of the network latency and the overload of the original specs,  it could not meet the time constraints any more.

I decided to deploy a SQL Server replication to replicate the set of tables where the two applications communicate.

The main trouble with this idea is that you can not implement a “nonSQL“-transactional-updatable (I mean bidirectional) replication with MS replication technology  … in theory … so I decided to build it on my own tackling the ancient hatred between the two RDBMS.

Here is the core of my idea :

I setup a transmission replication with Oracle publisher and SQLServer subscriber, and a second receive replication with inverted roles. As you can see from the picture I had to duplicate the tables and create a copy in the Transmit schema and a copy in the Receive schema.

Note that I consider the TX and RX from the Oracle point of view … and this is not only because of my Oracle DBA background, but also because usually the Oracle application triggers the communication.

The red arrow in the image is the link between the tables in RX schema and TX schema : I realized it with triggers “for each row” after update/delete/insert : the triggers propagated the changes to the twin tables in the TX schema.

Of course I had to avoid the loop … and I did it modifying the triggers created by SQL Server on the TX tables : I simply inhibited the triggers when the user is the owner of RX tables… unfortunately this was not the only issue I had to tackle : here is a list of troubles that I encountered: 

  1. I could not use the same tnsnames connector for the Oracle publication and the Oracle subscription … I had to duplicate and rename it in the tnsnames.ora
  2. I could not initialize the subscriptions in the subscription-creation-wizard : I had to create the tables manually  on Oracle and SQL Server and copy the data with other methods (SQL Server , import data wizard … aka DTS)
  3. Date/time datatype mismatch : I had troubles on the time datatypes : on Oracle date  starts on 4712 BC and has a precision of 1 sec. .. on SQL Server datetime  starts on 1753 AD  and has precision of 1 ms … I had to filter dates older than 1753 in TX tables and use timestamp(3) in Oracle RX tables.
  4. Characterset issues : it seems that the replica with Oracle publisher has problem with some non ASCII characters when Oracle WE8ISO8859P1  characterset is used and subscriptor SQL server has collation Latin1_General_CI_AS
  5. Suboptimal execution plans could pop up on the queries generated by the SQL Server log reader on Oracle : in case a large number of rows are changed/inserted/deleted in a short period of time the log reader could become very slow … this is because the statistics on the queue tables (owned by the SQL Server replication administrator user) becomes suddenly stale… in order to fix I had to compute statistics on the tables of the SQL Server replication administrator user.

After some time of trial stage and fine tuning the replica communication is now running with satisfactory performance (a round trip can take less than 10 seconds but the throughput is much higher than the previous communication system) and it needs few or no maintenance at all.