Problem solve Get help with specific problems with your technologies, process and projects.

Replicating data between SQL Server 2000 and Oracle

I want to replicate data between SQL Server 2000 and Oracle. The dataset is huge, and the replication has to occur every five minutes. Is it possible, and if not, what is the alternative? Both the servers are well-settled servers and cannot be disturbed. They carry huge amounts of business data.

This is quite a task when you consider that you have two different vendor's RDBMS platforms. They don't always talk to each other very well. If both databases used the same RDBMS software, then this chore would be easier.

From an Oracle perspective, you can make the SQL Server database appear as if it is an Oracle database by using a product called Heterogeneous Services. This let's you connect the Oracle database to SQL Server through an ODBC driver. You can then use triggers in the Oracle database to push the data to the SQL Server database when the data changes in Oracle. This way, data is replicated instantly!!

Another method is to use database triggers to populate a local table with the underlying table's changes. For instance, let's assume I have an EMP table. Then I can create a EMP_CHANGES table. Also create a trigger on the EMP table that populates EMP_CHANGES with all of the changes that occurred to EMP. Now have the other database query this table on a regular basis (5 mins?) and only query the EMP_CHANGES table. This greatly reduces the burden on the EMP table!

For More Information

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.