I have an Oracle 11g database in one machine with unique a IP, and a SQL Server 2008 database in another machine.
A table (A) in Oracle has some fields (not all) which are the same as table (B) in SQL Server.
Now I want to synchronize the fields' values in A with the fields' values in B. That is (maybe it is wordy!), when the value changes in A, so it is automatically updated in B without any manual operation in SQL Server!
How to get this done?
Set up a database link between Oracle and SQL server, then set up a trigger in the Oracle database to update the SQL Server table whenever the oracle table changes.
The easy solutions fall into "push" or "pull" strategies, which each have their benefits and drawbacks.
1) Push data from the Oracle database into SQL server, via a scheduled job or table triggers. Doing this, you'll have to remember to update the links to SQL if that database ever moves. With a table trigger, you'll also have some performance overhead on writes, updates or deletes to the table.
2) Pull data from the Oracle database using SQL server, via scheduled Merge job or SSIS ETL package.
Using either kind of scheduled job you'll be slightly behind the live table updates, which is usually not a problem. The job can run as often as necessary and possible to keep the gap small.
A DBA shouldn't have a problem setting this up with you, but if you're unfamiliar with database internals it can be difficult to choose and implement an option. Third party tools might be able to help if a DBA is not available. You should also be able to find guides on each of these options if you're going to implement one yourself.
