This content has been marked as final. Show 20 replies
I believe you already have the Database/ tables created in SQL Server.
As for data migration, A traditional way to do it is to extract the data from oracle tables in to flat files using SQL Loader and then use bulk copy in (bcp) to load data into sql server. This works perfectly fine. However, it might be cumbersome option when dealing it frequently.
I believe there's DTS (Data Transformation Services) in SQL Server that does this too.
If you need to do it once or periodicaly - create Linked Server in MS SQL that points to Oracle via OLE/DB. You have to install Oracle Client there on the MS SQL host.
Create job in MS SQL that calls
insert into target_table select * from oracle_linked_server.schema.table;
If you need continuous, close to real time, transactional replication - your options could be Oracle Golden Gate, DataCurrents or other 3-rd party products.
oracle golden gateextracts DMLs of data changes from archived logs, applies these DMLs on target.
target can be non-Oracle, MS SQL, My SQl etc.
oracle transparent gatewaykind of "Linked Server" for a specific non-Oracle DB. There is OTG for MS SQL.
hint: There are tons of different level docs about it on OTN.
how a middle layer through some API work for data replication from SQL SERVER ----------> oracle or vice versa ?
surely some expert have gone through this . please guideYes we have gone through this. But the question above is too generic. There are different APIs work on both sides.
What is your particular question?
What are you trying to achieve?
What requirements are?
We are running oracle R12 (e-business suite).
our company is implementing a CRM application ( .net and sql server ).
There are about 30 tables from oracle db whose data will be replicated from oracle --------> sql server through some middle layer interface . this data will be updated in CRM after 24 hours.
i want to know more details about these APIs etc and middle layer.
What is a maximal latency for data changed in Oracle come into MS SQL?
this data will be updated in CRM after 24 hours.Does it mean that data replicated from Oracle will be updated in MS SQL and should be replicated back?
i want to know more details about these APIs etc and middle layer.what API? In MS SQL you can use Linked server and write SQLs that select from Oracle tables via that linked server and insert/update/delete into MS SQL. You can organize these SQLs into T-SQL procedures and run them in jobs.
Also you can use MS SQL replication, snapshot or transactional, which is ugly because it places triggers on Oracle tables. I doubt that this will be approved by app vendor.
Also you can use Oracle Streams via Transparent Gateway for MS SQL, which will be quite heavy solution.
Also, if real-time replication is needed, you can use "middle layer" Oracle Golden Gate, which may be quite pricy for 30 tables.
Also, if real-time replication is needed, you can use 3-rd party middle layer like DataCurrents or others.