I have an Oracle 8i database as well as a SQL server 2000 database. Each database is on a seperate server.
These databases contain multiple tables that are identical. The Oracle 8i database is the source database where changes occur. I would like to keep the data on the SQL Server database as real time as possible.
What we had been doing in the past was doing batch updates of all the tables from Oracle and overwriting the existing ones on SQL Server, every 20 min.
This worked fine in the past, but it now seems to be putting a major strain on the Oracle server.
Does anyone have suggestions on how I can send only the updated data to SQL Server rather than the entire table. (I need to include any changes that may have taken place).
I also am very interested if this is more easily achieved using a SQL Server transfer to SQL Server. Rather than Oracle to SQL Server.
One way to do this is to create a linked server (on SQL Server instance) to the Oracle database server. And write your code to pull the delta from Oracle to SQL Server. There are several options using SQL Server linked servers where you have a choice to have the processing done on the linked server or the initiating server.