1

I have a conceptual question.

I have two databases which theoretically have the same structure but one of them is a SQLBase database and the other one is a SQL Server database.

One database (the SQLBase database) already contains a lot of data. This data should be transferred to the other (SQL Server) database via Select and Insert.

How can I do this data migration with the highest performance?

My first approach was to sort all the tables in a list where the tables which contain foreign keys will be stored behind the referenced tables. But with this solution it will be impossible to start parallel processing.

The second idea was to create a custom type which contains the tablename and the tablenames of the referenced tables and a bool flag which stores whether the data in the table have been copied. This type is stored for each table in a list. Then I start a new thread that checks before copying whether the referenced tables have already been created for each table. If not, I execute Thread.Sleep() after which I will check it again.

Is there a well performing approach to this problem?

Improve this question
2
  • 4
    How much data (rows and gigabytes) are you dealing with? Is the source static or changing? Can you afford any downtime?
    – vonPryz
    Jan 13, 2016 at 11:36
  • If you insist on rolling your own migration code is there any reason why you can't disable the FK constraints in the target database, load all the data, then enable the FK constraints?
    – Erik
    Nov 18, 2016 at 0:00

2 Answers 2

Reset to default
1

What about using one of the existing ETL tools? Talend ETL or Pentaho Data Integration are two examples (performance-wise PDI is better). You can design an ETL job then schedule to run it as you like.

Improve this answer
1

I would suggest to use SSIS for this task.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.