Re: copy large amounts of data to MSSQL
Date: Fri, 6 Nov 2020 19:30:20 +0000
Message-ID: <DM6PR01MB5929AF1D64BB05B78100EB27CEED0_at_DM6PR01MB5929.prod.exchangelabs.com>
MS has a product for migrating to MS SQL From Oracle and other platforms. It is capable of creating the objects and copying the data. But once tables created in target you also would have the option to use SSMS database import feature to copy tables from Oracle to SQL Server using various drivers. You would need an Oracle client on target to use this option.
What is your best option depends on if this is a one-time task or going to be repeated and/or if you need to run some type of update process after the initial load is completed. A java program could be written to connect to both Oracle and SQL Server at the same time and to pump data from one to the other.
Mark Powell
Database Administration
(313) 592-5148
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Jeff Chirco <backseatdba_at_gmail.com> Sent: Friday, November 6, 2020 7:38 AM
To: Frank Gordon <frankagordon_at_gmail.com>; Mladen Gogala <gogala.mladen_at_gmail.com> Cc: oracle-l-freelist <oracle-l_at_freelists.org> Subject: Re: copy large amounts of data to MSSQL
Thanks for the ideas. We want to avoid having the Cloud instance have a direct connection to our database, hence why we want to push instead. Plus that works out better for our users, when they are done processing orders they initiate the send to the cloud vendor. This will be a multi day event to push to MSSQL. I've been testng with 5000 rows, right now it is taking 2.5 minutes before it was 6-8 minutes. I believe the latency to OCI is fluctuating, currently I am getting 14ms ping times, however it is early in the morning here in California as I am working on a 19c upgrade. We've had ping latency in the upper 60ms ranges and we've experience a lot worse around 8:30am with packet loss.
I was thinking the same thing about a Perl or Python script "might" be faster. Any one happen to have a sample of that?
On Fri, Nov 6, 2020 at 1:35 AM Frank Gordon <frankagordon_at_gmail.com<mailto:frankagordon_at_gmail.com>> wrote: Hello,
Have you tried doing a pull from the MS-SQL-Server side?
How much data in the table?
How "far" apart are the Oracle and MS sides?
How you tried sending one row and timing that?
Look at the MS-SQL-Server funtion OPENQUERY.
Regards,
Frank
On Fri, Nov 6, 2020 at 12:31 AM Jeff Chirco <backseatdba_at_gmail.com<mailto:backseatdba_at_gmail.com>> wrote: We have a new project where we need to copy a large dataset from Oracle (on prem) to SQL Server (cloud).
I found that just a
insert into table_at_mssql
select * from table;
Returns ORA-02025: all tables in the SQL statement must be at the remote database
I am trying out the SQL Server Gateway driver from Oracle because the documentation looks like it would work with the Remote Insert Rowsource https://docs.oracle.com/en/database/oracle/oracle-database/12.2/gmswn/database-gateway-for-sqlserver-features.html#GUID-513FBA3C-3458-4129-93E4-38DB2DF97F7A<https://clicktime.symantec.com/3UrjFt7E7qXQzdDZUoBxJAo7Vc?u=https%3A%2F%2Fdocs.oracle.com%2Fen%2Fdatabase%2Foracle%2Foracle-database%2F12.2%2Fgmswn%2Fdatabase-gateway-for-sqlserver-features.html%23GUID-513FBA3C-3458-4129-93E4-38DB2DF97F7A>
However I get the same error. Does anyone know if this should work?
We've converted it to a PL/SQL loop inserting row by row but 5000 rows is taking 8 minutes. Oh and the funny thing is that this SQL Server instance is running on Oracle Cloud Infrastructure.
Any other ways you can think of to do this quick? 8 minutes is killer.
Thanks,
Jeff
--
+353-86-0695383
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 06 2020 - 20:30:20 CET