Skip to Main Content
  • Questions

  • Oracle and MS SQL server data transfers

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Manjula.

Asked: March 22, 2018 - 4:27 am UTC

Last updated: March 23, 2018 - 3:27 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Tom,

Currently our company is Implementing an ERP system based on Oracle (12c - 12.2.0.1). We already have a product testing system that has data in a SQL Server database (MS SQL Server 2008 R2 64 bit - 10.50.2500.0). We need to Establish a communication between the 2 databases. there are some suggestion to use XML file transfers but I'm not keen in that.
This is how we need to communicate between 2 databases

From Oracle to MS SQL - About Twice a day
From MS SQL to Oracle - Real time when ever a testing is done in the tester system.

Currently the company can't do significant changes in MS SQL side because the environment is live.

What is your suggestion to send and receive data between the 2 databases?

and Connor said...

For intermittent data transfer *from* SQL Server *to* Oracle, you look at heterogeneous services.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/heter/index.html

In essence, you will be setting up a database link in Oracle, which is in reality, a hook into (say) an ODBC connection to a remote database (which would be SQL Server). It is fairly simple to setup, but the functionality is predominantly reading of remote data.

If you need a comprehensive data transfer solution, then your options could include:

A full gateway product

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/gmswn/index.html

which provides a large suite of functionality between the two databases. Or if you just want data transfers, you could consider GoldenGate which would let you replicate information in each direction.

http://www.oracle.com/technetwork/middleware/goldengate/documentation/index.html

The latter two offer bi-directional data exchange.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you

Manjula Gamage, March 23, 2018 - 4:05 am UTC

Thanks Tom,

It seems we may need to implement the SQL Server Gateway. But I still needs to investigate bit more about data receiving part to Oracle from MS SQL server because the data selection should be done after an event in the MS SQL Server.

connecting to sqlserver

Rajeshwaran, Jeyabal, March 24, 2018 - 2:23 pm UTC

....
But I still needs to investigate bit more about data receiving part to Oracle from MS SQL server
....

very recently worked in an application that requires Oracle database to connect with a sqlserver instance and did a simple test using Oracle Heterogeneous service and it worked well.

you can see more details about that in the below link.

kindly check if that helps.

https://asktom.oracle.com/pls/apex/asktom.search?tag=connectivity-to-sql-server#9537025800346795592
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database