question

EdwardVTrockyJr-6003 avatar image
0 Votes"
EdwardVTrockyJr-6003 asked AlexanderIvanov-MSFT published

SQL Server Migration Assistant for Oracle - slow transfer

Migrating a schema from Oracle 19c to Microsoft SQL Server 2017 (RTM-CU25) (KB5003830) - 14.0.3401.7 (X64) and the transfer of data is very slow. Moving an address table with 13 million rows from Oracle to Oracle across a wide are network takes less than 2 minutes. Using SQL Server Management assistant for Oracle, v8.22, takes 17 hours with the SQL Server, app server (with migration assistant) and Oracle all in the same data center. What options can be set to improve the transfer rate from Oracle to SQL Server using the migration assistant?

log
Status,Source table,Target table,Total rows,Migrated rows,Success rate,Duration (DD:HH:MM:SS:MS),Error messages
"Completed","""<schema>"".""ADDRESSES""","[<instances>].[<schema>].[ADDRESSES]",13220149,13220149,"100.00 %","00:17:08:48:587",

sql-server-migration-assistant
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AlexanderIvanov-MSFT avatar image
0 Votes"
AlexanderIvanov-MSFT answered AlexanderIvanov-MSFT published

Hello,

Oracle to SQL Server is a logical data move. SSMA has to read each and every value and convert it to be compatible with SQL Server. There are a lot of factors affecting the data move performance that are dependent on your environment - source and target server performance, etc. In SSMA you can utilize server-side data migration, which will eliminate additional network hop to the SSMA client. Also, what connectivity stack are you using when connecting to Oracle? We found "Oracle Data Provider for .NET" to be more performant than ODBC/OLE DB. With ODP.NET you can additionally configure the FetchSize. In order to do this, you would need to update SSMAforOracle.exe.config file in the SSMA's bin folder to include the following:

 <configuration>
   <configSections>
     <section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess" />
   </configSections>
    
   <oracle.manageddataaccess.client>
     <version number="*">
       <settings>
         <setting name="FetchSize" value="4194304"/>
       </settings>
     </version>
   </oracle.manageddataaccess.client>
 </configuration>


Keep in mind that when using server-side data migration, the actual data migration components would be running on the target SQL Server, so the application configuration file for SSMA data movement component will have to be updated on the SQL Server machine.

Regards,
Alex.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.