Complex data can be migrated between databases by either copying the data directly from one database to another using SQL statements or indirectly using code.
An advantage of the direct approach is that is it extremely fast, particularly if bulk inserts are used. If the process is fast an iterative approach to transferring the data can be adopted where the migration can be repeated numerous times until it is successful.
A disadvantage of the direct approach is that it bypasses any business rules in the application code. Depending on the complexity of these rules it may be possible to replicate them in SQL, but this is not always easy and it duplicates functionality that already exists in the application code. This means that there is a significant risk of invalid data appearing in the target database.
An advantage of the indirect approach is that once the data has been migrated it is guaranteed to be correct. This is because it has been validated by the application business logic. The same logic that is applied when data is entered via the UI.
A disadvantage of the indirect approach is that depending of the amount of data being migrated it can be a prohibitively slow process, which means that it is difficult to run repeatedly.
On balance the indirect approach, favouring data quality over the speed of migration is preferable.
You must log in to post a comment.