our company is running in oracle server. the data which need to provide to another company is running sqlserver.
the data which need to provide to another company is around 85 million rows. each row will have a clob field that will be around 10-15 k.
what is the best way to send them the data?
We have following options: a. we can export the dump using oracle's expdp. Provide the dump to another company. They will first import data into oracle server. And then use sqlserver's link server to import data from oracle server into sqlserver.
b. create the text files in xml format and send it across to another company. They will directly import it into sqlserver.
it is one time effort. for the given volume, what is your recommendation? what is the best approach we can take? what are the pros/cons?
Please tell us.
Thanks in Advance.
asked Jun 29, 2010 at 01:17 AM in Default
I'm not an Oracle DBA but I work with moving data in and out of Oracle from SQL Server quite a lot. In general, each product is pretty good at pulling data from the other product and both products stink at moving data into the other product. Generally I've found that OPENQUERY works best from SQL Server to pull the data from Oracle. You have to build a dynamic query, but that's not a big deal. Pass as many parameters through the OPENQUERY as you can to limit the data coming back. On the Oracle side, they use the Transparent Gateway (a poorly named product) to pull the data from SQL Server. It works well.
If you want to spend a little money, there are third party products available that work with SSIS to provide a data source and destination to Oracle. I've seen these work and they're extremely fast. You can do a Boogle search to track them down.
Finally, if you can't directly connect the two servers, then bcp in/out of SQL Server and whatever the Oracle equivalent is, would be the best way to go.
In every case, it's always best to filter the data as close to the data as you can. This means passing the parameters to Oracle if you're reading from SQL Server. Otherwise, you move a lot of unnecessary data and it will affect the overall performance of the system pretty radically.
answered Jun 29, 2010 at 04:21 AM
Grant Fritchey ♦♦
I have no experience of Oracle databases so cannot talk from experience and you may find that most people on this forum are dedicated SQL Server DBAs so you might have to ask the question elsewhere to get a range of experienced opinions.
For my preference I would go for the linked server approach as that saves the hop in the middle of getting anything into csv or xml or whatever.
Microsoft have a lot to say about this as they want to win SQL Server customers so take a look at their recommendations here http://www.microsoft.com/sqlserver/2005/en/us/migration-oracle.aspx
I have no experience with Oracle, however I would look at doing it as follows:
Plain and simple... Tab separated files using Oracle's SQLExport and SQL Server's Bulk Insert.
answered Jun 30, 2010 at 03:30 PM