Link to home
Start Free TrialLog in
Avatar of dodgerfan
dodgerfanFlag for United States of America

asked on

Import Oracle data into SQL Server 2012

I have a huge Oracle database that I need to import data from into specific tables in SQL server. Right now I have a web service that is slowly churning through the data. It's been running for 2 days now. I was hoping to find something more robust and quick to import the records in. I am talking literally over 100 million records. After 2 days I have 5 million imported. I am looking for any ideas on a better and much quicker solution. I'm using SQL Server 2012. The web service is written in C#, VS 2012. Any ideas would be appreciated.
Avatar of dodgerfan
dodgerfan
Flag of United States of America image

ASKER

Oh, I do have the web service threaded and am running 10 instances of it. Multyple open database connections, too.
Avatar of Steve Wales
Have you tried SQL Server Migration Assistant ?

I've converted 200GB+ databases in less time that you've currently spent on what you're doing.

Both databases need to be visible from the server you're running SSMA on - and there are some limitations on complex functions etc.  But for pure data without any LOB's etc in it (may be a few other restrictions) it has served me well.

This is a great option for a one off thing, not so much if it's something you need to repeat regularly.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Any reason you wrote your own?

I'm not a SQL Server person but I've read about tools that already do this for you.

http://technet.microsoft.com/en-us/library/ms141209(v=sql.110).aspx

I've also seen folks talking about SQL Server Integration Services (SSIS) that can do this.

I even think Oracle has a tool but theirs might be for getting data FROM SQL Server into Oracle.  Not sure about the other way.
Link for SSMA (slightwv got in before I could edit my comment): http://technet.microsoft.com/en-us/library/hh313179.aspx

Guess it also depends if you're doing multiple tables or just one.  Import / Export may work just fine for one table.

SSMA was great when I had to convert a database of 1600 tables...
>>After 2 days I have 5 million imported. I am looking for any ideas on a better and much quicker solution.

Assuming you wrote your own for a reason:
What are your commit points set to?
How is it doing the transactions on the Oracle side?

For example:
In Oracle a select is read consistent.  Once the cursor is opened, all data fetched will be as it was at the time it started. So, you may be going back and reading rows as they looked 2 days ago!  This would likely involve a lot of redo reading.

What I've done in the past when I had to write my own migration was create a temp table and load up all the primary key values that need migration.

My migrate program reads the values from that, moves the row then deletes the row from the temp table.

Then I can set up transactions however I want.  If the row is in the temp table, it still needs migrated.
For pure simple one time transfers, I have created a linked server in SQL Server to Oracle and just done a simple insert into ... select from ...

That method worked and I would think it would be faster than what you are doing.

If this is an ongoing thing that method isn't as efficient, but it should work.

Another way would be to dump the data to a flat file (pretty easily done with TOAD or SQL*Plus) and then use BCP to load it in.
Give a test with Pentaho Data Integration (Kettle). They have a community edition here http://community.pentaho.com/ .
Maybe the tools mentioned above are more reliable, I don't know, but with Kettle I forget about data transfer between databases.

Of course you must take into consideration slightwv comment related to Oracle production data modification.

Regards Alex.
This is not a conversion. I'm pulling data out of a production Oracle db and putting it into a SQL server db that will serve as the backend to a COTS product. This initial import is to get everything I have in SQL, then set up a process to import new records on a daily basis. I have tried linked servers in the past, but I have been unsuccessful in getting oracle linked to this sql server thus far. The import code was provided by the COTS vendor. I tweaked it to allow for threading, but that's been it.
I definitively would go with Kettle, as I said. Initial load of course can be lengthy, but further based on an audit table on SQL Server side which keeps last ID loaded in previous day, can be used for reading from Oracle starting from that ID.
import.jpg
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial