7 challenges you may face with data migration tools
January 6, 2020 | BY Pushpender Garg
Cloud migration has become a key part of Digital Transformation initiatives today. Since there are considerable investments at stake, it requires careful planning and assessment to anticipate the challenges that you might face during your migration journey.
Migrating large volumes of data from on-premise systems to cloud is a highly complex and nuanced process. Even with the best tools available, migrating hundreds of terabytes and thousands of tables of data to cloud may pose some unexpected challenges that can adversely affect and/or delay your cloud migration process.
Before we begin…
Your data migration journey typically involves cloud assessments, target platform POCs and signoffs, and then the actual data migration.
Here are some basic requirements for any data migration project:
- It should be quick
- It should be automated and should require minimal manual intervention
- There should not be any data issues or losses
Many data migration tools in the market claim to fulfil these requirements and more. The most common offering is “All you have to do is select the source tables that you want to migrate and hit the migrate button. That’s it.” The tool will automatically create target schema, map data types, migrate data and also perform Change Data Capture (CDC) for syncing. Apart from that, these tools also have capabilities to scale, schedule, tokenize, etc.
During our many on-premise to cloud migration initiatives, we have observed and outlined some key challenges which are likely to occur despite using some of the best cloud migration tools in the market.
Some of the most common challenges with Data Migration Tools
When you are assessing these tools, at first glance it looks like a straightforward thing. But when you begin with large scale implementations, you might start observing and experiencing some gaps.
Stumbling upon these gaps in the middle of execution phase may impact timelines, efforts and overall quality for your migration.
Considering these gaps in your assessment phase may help you choose the right data migration tool as part of your cloud migration strategy.
1. Throttling and Elasticity
During data migration, you may need to apply throttling on data extraction processes because of limited availability of resources on your source database.
Since you want to migrate, you don’t want to spend more to add resources on your source system and at the same time you don’t want your data migration processes to overload source database and impact performance of your production applications/loads. You can control this by limiting the number of parallel extraction processes but that’s still not an optimal solution. Most of the data warehouses have a variable load throughout the day which eliminates running a constant number of processes all the time.
To optimally use the source database, you need an efficient data migration tool to sense the load of source database and accordingly throttle data extraction processes while scaling up or down. In practice, we had to create scripts to throttle the extraction process as none of the existing data migration tools provided such feature.
2. Efficient Data Validation
Data validation provides assurance that data is loaded without any datatype, truncation, formatting, or consistency issues. If your source and target are different databases, then it becomes all the more important to perform data validation. You can’t validate thousands of tables manually. In our opinion, it is always good to perform full data validation, however it depends upon how critical the data is. We have observed the following issues with most of the migration tools regarding data validation:
- While many tools don’t provide any data validation features, some tools provide limited (as explained in below points) data validation.
- They could validate data only if tables had primary keys.
- Even if some tools can validate on primary key, they perform so slow that it’s not possible to use this feature, and furthermore they add double load on source database.
The right tool should provide efficient data validation without putting extra load on source database, irrespective of whether the table has primary key or not.
3. Automatic Configuration and Scheduling
While most data migration tools provide certain features to configure and schedule data migration, it still takes a considerable amount of manual effort for configuration.
Imagine you have thousands of tables and for each table, you need to manually setup pipeline, specify primary keys (for validation), watermark column (for CDC), load modes (based on partitions), etc. and schedule these tables at a particular time or as per the priorities. Again, if the tool can’t get these configurations done automatically, it is required to create custom scripts to automate these processes.
4. Fault Tolerance and Restartability
Most data migration tools work well when they are on a smooth path and nothing is failing. But it becomes very difficult to manage when you have many processes that start to fail. We have observed the following issues for fault tolerance and restartability
- On restart, it doesn’t start from the point of failure but right from the extraction.
- There were instances where duplicates were loaded in case of a restart. In such cases, it requires a lot of manual handholding during this process to avoid such issues during the process failure.
- During some of these instances of failure, it was very hard to get a detailed error message to pinpoint the cause of failure. Only generic messages were available.
The right data migration tool must have provisions for such possibilities and should perform an automatic restart from the point of failure while ensuring that there is no duplicate loading of records.
5. Upserts for Change Data Capture (CDC)
Data syncing is almost always required since you want to continue with the source system until your target system is stable enough. Change Data Capture (CDC) is needed for data syncing and this feature is available in many tools. However, many of these data migration tools don’t support updates/upserts on target for data synching, as they only support append mode.
In data warehouses, there are often a large number of dimensions which are typically upsert tables, so it requires developing extra scripts to perform upserts. The right tool should have options to configure tables for append/upsert/replace mode for CDC.
6. Generate/Respond to Events for Integration
Sometimes data migration and syncing is just one part of your overall pipeline. You have some process upstream which should trigger the data synching process or you want to trigger/notify a downstream process once data synching is done. As trivial as it may sound, many tools do not provide such integration endpoints, nor can they be integrated with an external scheduler.
7. Data Type Support
Depending upon your use case of source and target database, there might be some tricky datatypes which do not have direct mapping in target database. Many data migration tools available in the market have limitations of data type support per source or target database.
This requires you to additionally run a metadata query on your source database and to make sure that you have support for all data types.
Ensuring successful data migration to cloud
Large-scale data migration from on-premise systems to cloud needs carefully mapping and preparing for these and other unanticipated challenges that may occur during the migration, despite whichever data migration tool you may be planning to use.
The key factors to achieving an efficient and successful cloud migration are:
- On-premise resources expert in cloud migration
- Agile processes
- Continuous and effective collaboration with all teams involved
- Data governance
Ensuring that your cloud migration journey is completed without any glitches, delays and worse, crucial data losses, requires you to fulfill above criteria, avoid complete dependence on the data migration tool and to better assess the tool before you begin with your on-premise to cloud migration initiative.
RELEVANT Reads
Perspective | October 10, 2023
BITWISE’S CONTINUOUS TESTING SOLUTIONS: BRINGING VELOCITY TO YOUR...
As DevOps transformation continues, the quality testing speed and agility are a big challenge that…
Perspective | September 4, 2023
PROACTIVE SUPPORT MODEL FOR DISPUTES MANAGEMENT SYSTEM
Bitwise’s Solution for a Fortune 500 Client
Perspective | June 20, 2023
SUPPORT AND MONITORING MADE SIMPLER THROUGH EFFECTIVE AUTOMATED...
Today, production support is a critical part of any business, and the most critical part…