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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.