How to Optimize Migration of Schema and Data

To optimize the migration run, we recommend to split the conversion into multiple threads by the following approach:

• (Instance 1) In the first instance of the tool select all the tables (and other objects (views, functions, etc.) if there are so) need to be converted.

On the “Conversion Options“ page click on “Tables” in object's tree, and on “Data” tab uncheck the “Export Data” option:

This will allow our tool to convert all DDL, indexes and constraints without wasting time on exporting data. Next, on the “DDL” tab with “Table Options” item in dropdown list select “Fast” for “Import option for tables” option:

This option allows us to unload constraints and indexes apart from DDL, to be imported then separately into the database.

• (Instance 2 - N) In the rest of the instances only the data will be converted.

On the “DDL” tab with “Generation Options” item of dropdown list need to uncheck the “Generate DDL” option. Thus, only the data will be uploaded and converted in these threads:

Now the “Export Data” option should be enabled.

If necessary, the scope may be divided into approximately equal parts of the data to speed up the process of conversion. There are two ways to split data in a table:
a) If you select only one table for conversion, you can specify the starting row and the number of rows to be uploaded in the “Tables” on “Data” tab. Thus, for example, you can upload N rows starting from the first in one instance, in the second instance – the rest of the rows, starting from N + 1 rows and so on. The number of such instances may be as many as you need:

b) You can specify SQL condition for each table. If your table has a key, you can write a SQL condition using SQL language of source database that will partition the table (more detailed instruction can be found here).

For each instance we also need to select “Fast” for “Import option for tables” options. Further, the algorithm is as follows – firstly, start the conversion of all streams (be sure that the “Start Import Automatically” checkbox unchecked for all threads):

Once all the tables have been converted in the first instance (Instance 1), you need to upload ONLY the DDLs of these tables to the target database. For that purpose, in the “Import Options” window leave only the “DDL” item checked (if there are any other objects have been chosen for migration, you may also check the appropriate items, e.g. “Views”, “Triggers” etc.):

Next, you need to wait for the data export in each thread completed and then import the data into the target database (Instance 2 - N). Note, that only the “Data” item should be checked the “Import Options” window in that case.

As soon as all the data is loaded into the database, it is necessary to select only “Indexes” and “Constraints” in the main instance (Instance 1) in the “Import Options” window and perform an import:

Such a mechanism allows to convert all tables and data in several instances without fear that any problems may arise due to keys and dependencies between objects.

If you have any other questions, please contact us: support@ispirer.com