So you have been tasked with migrating data from one system to another. What you need to know and how to perform a successful data migration…
Over the years in my IT career, I’ve been involved with many data migrations from one system to another. Whether it’s from an on-premise server to cloud, or Application A to Application B, the practices and principals are always the same.
So what is a data migration?
A data migration is the process of moving data from one system to another.
What tools do I use?
What tools or process we use is irrelevant as long as we meet the desired outcome. At Vinergy for a typical data migration, our favorite tool is KingswaySoft SSIS Toolkit.
ETL Process (Extract, Transformation, Load)
Data plays an important role in every business and it’s the most valuable piece of information when it concerns a business or their customers. When we move data between systems the process can be defined using the ETL process (Extract, Transformation, Load).
Extract
This process is around the extraction of the data from the Source. There could be multiple sources that need to be extracted and brought across to the staging database where we will begin the next phase of Transformation.
‘Extraction should not affect performance and response time of the source systems therefore we need to be mindful of how we perform the extraction’.
Whether it’s peformed on a copy of the live database, or peformed on the production system outside of business hours. Remember performing this extraction during business hours could have an adverse affect on productivity and impact the customers bottom line, therefore care and thought is required.
Transformation
Transformation is about applying business rules or policies to the data (such as concatenation, calculations, joining & linking data etc) and manipulating the data into a format that is easily loaded into the destination database.
We need to identify & address data integrity problems such as different spelling (i.e. Jon, John), Company names (i.e. legal or trading names), abbreviations of states (i.e. QLD, Queensland etc).
Typically you would develop scripts for conversions, cleaning up NULL values, transposing rows and columns, or where you have a full address split into separate fields (i.e. Street, City, State, Post Code, City).
‘Before we perform our Load process, take note of the total records in each of your tables that you are migrating as you will want to validate these numbers once the load is completed’.
Load
The final phase of ETL is loading the data into the destination. How you get that data into the destination is up to you as the consultant, but as you have applied your business rules and transformation in the previous phase it should be a fairly straight forward process of mapping A to B. We need to be mindful of the limitations of the destination database – i.e. transactions per minute, or how many connections per second/minute.
The load progress should be optimised for performance based on the quantity of data that we are loading into the destination. Whilst the data is being migrated to the destination database we must monitor and allow for error handling, so we can review those errors and remedy the problem before resuming the load process.
Loading the data can be broken into 3 types of loading;
- Initial Load
- Incremental Load
- Full Refresh
Where a large amount of data is required to be migrated, I would reccomend breaking the data into smaller subsets of data to migrate, this could be by region, city etc. This means you aren’t overwhelmed when it comes to validating the migrated data which I will discuss next.
Validation
We have spent a day over the weekend migrating 100,000s of records to our destination. We have our total records recorded from the Transformation Process, and now we must check the total at our destination database.
- Do these match?
- Are there more or less records than expected?
- If there is more – did we accidentally run something twice?
- Do we have a join incorrect?
- Are we performing an upsert or insert?
To simply the validation process, we can select a smaller subset of data first, then sample check those records to validate that the data has migrated successfully.
‘One piece of advice that we would give before taking on any migration project is never underestimate the amount of work that is required for a successful migration’.
‘Whether it’s dirty data, incomplete data or outside influences such as hardware performance, internet etc you need to take into consideration before committing to a dollar value’.
Vinergy has vast experience in performing migrations whether data migrations, office 365 migrations or server to cloud migrations we have done them all!
Let the experts take care of moving your data and have that extra level of assurance that it will be migrated by the professionals that have experience and know how to complete successfully.
