asked
best approach to transfer large volume of data(spread across 3 tables) from one company(oracle) to another company(sqlserver)
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.
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: 1. Export table as per your suggestion 2. Import table.... 3. Use SSIS to pump the data from Oracle to SQL Server (running the pump on the target server to reduce network traffic overheads)
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.
Plain and simple... Tab separated files using Oracle's SQLExport and SQL Server's Bulk Insert.
