locked
Does SSMA (OracleToSQL) Script file support setting the batch-size, Parallel-Datamigration-Mode and Thread-Count values RRS feed

  • Question

  • I’m trying out to use the SSMA scripting method to perform data migration from Oracle to SQL server in hope of choosing which objects and their sequence during the migration process. Using the SSMA wizard version 6.0, I was able to configure the batch-size, Parallel-Datamigration-Mode and Thread-Count to optimal values and the data migration time is quite acceptable. However when I try running the same migration process using the SSMA (OracleToSQL) script file method, the time taken was significantly longer. My question is am I able to configure the batch-size, Parallel-Datamigration-Mode and Thread-Count using the SSMA (OracleToSQL) script file method and please provide guidance how to speed up the SSMA (OracleToSQL) script file method if it is indeed possible. For instance, to migrate a table with 11 million records, it takes around 10 minutes using the SSMA wizard after configuring the batch-size, Parallel-Datamigration-Mode and Thread-Count with optimal values. But when I drop the table in SQL server and rerun the data migration on the same table using the SSMA (OracleToSQL) script file method, it took around 35 minutes to complete, please advise and thank you for your assistance.
    Tuesday, October 4, 2016 8:58 AM

All replies

  • Hi Johnwanng,

    >>My question is am I able to configure the batch-size, Parallel-Datamigration-Mode and Thread-Count using the SSMA (OracleToSQL) script file method and please provide guidance how to speed up the SSMA (OracleToSQL) script file method if it is indeed possible.

    Apologize for the late response, so I did a little digging in SSMA project folder, it seems the project settings were recorded in ‘preferences.prefs’ file after you changed it via GUI. And if I understand it correctly, you could specify the project environment settings folder for the current SSMA project by using -e switch. In this case, I would suggest you create a new project with proper settings, and load it by using -e switch in your command.

    If you have any other questions, please let me know.

    Regards,
    Lin
    Thursday, October 6, 2016 6:35 AM
  • Hi Lin,

      Thnak you for the email. I am using the the command line - c:\SSMAfororacleConsole.exe -s "my.xml" to perform the manual data migration process. What I question is there a way where I can specify those environment settings, e.g. setting the batch-size=2147483647, inside "my.xml" file so the migration process can be run with my desired environment figure instead of just running with the default values of 10000. And I am not sure what you meant by the -e switch.

    Thursday, October 6, 2016 7:25 AM
  • Hi Johnwanng,

    >>What I question is there a way where I can specify those environment settings, e.g. setting the batch-size=2147483647, inside "my.xml" file so the migration process can be run with my desired environment figure instead of just running with the default values of 10000.

    I thought about that at the very beginning too, but I don’t see any option there to set things like batch-size in script file. So I would suggest you try the following approach:
    1. Create a project in SSMA.
    2. Change settings like batch-size in project and save it.
    3. Use the command below see if it works:
    SSMAfororacleConsole.exe -s "my.xml" -e PathtoProjectFolder

    Please refer to BOL for more information.

    If you have any other questions, please let me know.

    Regards,
    Lin
    Thursday, October 6, 2016 7:41 AM
  • I have tried it using your method, the command does work without any error but I do not think it was using my desired project seting values as it is still taking a long time to migrate data across (just like using the default setting values).

    Thursday, October 6, 2016 9:13 AM