Data Migration Best Practices
Introduction
As a professional in the field, I have helped some of the largest financial institutions and insurance companies migrate multiple on-premises database systems to the cloud through software that has taken my team decades to build.
The first lesson I learned is that if a company wants to move data from legacy or on-premises systems to the cloud, they will also need to move it back. Migration is a two-way street. So, what goes up must come down, and what moves off must move back.
Before migration, a company had one system, but during the migration process, there are now two systems. Individual users must be able to move data seamlessly between the old and new systems to complete their assigned tasks.
It’s important to understand that migration is not just about hardware or software; it’s about the seamless integration of both. A successful migration strategy must consider the unique roles that hardware and software play in the process.
The software piece is the most important because it automates migrations. Companies can make the mistake of spending years designing a migration. Advanced software can take seconds, minutes, or hours to complete the same tasks. Shockingly, what can take seconds is better than years of manual labor.
The hardware piece combines an employee’s laptop or desktop with an ODBC connection to the database systems that do the database conversions and build the load-scripts. That laptop or desktop then schedules the data movement job to execute on a Server to move the data.
Hardware scalability is important so that multiple laptops or desktops can perform simultaneously and work in parallel with multiple servers constantly moving the data.
Best Practice – Plan a Migration Timeframe
If you fail to plan, you plan to fail, so the first task is to devise a goal for how long you want the migration to take place.
One of the main questions to ask is whether or not you want to move all of the data from the source system or just some of the data.
Another important question is whether you want to have both systems active or shut off the legacy or on-premises system.
How many tables will you want to migrate, and what is the average size or, even better yet, the median size?
You can then run tests to move data. Choose a table(s) with the average/median table size to estimate the time it will take to move each table. To understand the differences, you can also test moving the smallest and largest tables.
It would be best to consider that the source system has specific Service Level Agreements (SLAs) that must be met, so you might need to migrate data at night or on weekends when the system is less busy. However, you might be able to move data during the workday, depending on whether data migration can be done simultaneously with normal source system activities.
Once you have a migration timeframe and know the approximate times it will take to move the tables, you can scale the number of data movement specialists needed to meet the deadlines.
Best Practice – Convert the Tables Automatically
The first step to a successful migration is converting the table structures, which is called the Data Definition Language (DDL). You originally had to create tables on the source system, and now you must convert those tables to a completely different proprietary system with a different way of creating tables.
The great news is that my team has already done that for you. A best practice is to take a database you want to move from the source system and convert the DDL with our automatic DDL converter. The DDL converter converts between any two source and target systems extremely effectively. If you have thousands of tables, receiving the tables in converted form will only take a few minutes.
You can then execute the table creation on the new system and create the tables in minutes. You probably won’t get any errors, but you can easily modify the table creation statements if you do.
Now that you have the table’s DDL created for the new system, this is the perfect time to check the data types on the new tables and make any global changes that might better fit your data.
Watch the video to see how to convert your table structures from the source to the target system and test it.
Best Practice – Build the load scripts, test, and save
Building the load scripts to migrate data results in code that looks like Greek to most, which is why it can be a long, complicated process. We have automated the process.
With a simple right-click, users can select their source and target systems, databases/schemas, and tables for migration. This level of control puts the user in the driver’s seat, ensuring a personalized and efficient migration process.
The user will see if the table(s) exists in the target system. If the table(s) do not exist, a checkmark will automatically appear on the CREATE TABLE box. If the table(s) exist, the user can load more data, delete the current rows in the table, or drop/recreate the table.
Users have the flexibility to choose the load utilities that best suit their needs. With most combinations of source and target databases offering approximately four utility options, users can select the most suitable utility based on speed, ensuring a tailored and efficient migration process.
A best practice is to use powerful utilities on the large tables and less powerful utilities on the smaller tables. Some utilities are meant for only the largest tables, and they often have limitations when running simultaneously because they take up source system resources.
Another super smart best practice is to execute the load scripts to test for errors while moving the data. If everything works without errors, most companies save the load scripts so they can schedule them to execute on a Server when migration time comes.
Best Practice – Prepare to Scale with Multiple Servers
You have unlimited scalability for building table conversions and load scripts because you can assign from one to dozens to hundreds of individual users to perform these activities on their laptops or desktops.
You can also run tests on servers responsible for executing these load script jobs built by the users from their laptops or desktops.
A great practice is to have your migration servers scalable so users who have built the load jobs can schedule them to run using a global calendar.
A major financial and insurance leader utilized 12 contractors to build the load jobs, test them, and save them. When the time came to perform the migration, they had three migration servers and assigned four contractors per server to schedule their migrations. Twelve contractors using three servers allowed them to move millions of tables within six weeks.
Watch the concept of the Nexus Server in this one-minute video.
Best Practice – Federate while you Migrate
Modern software must query all systems, convert and migrate data automatically across any two systems, and seamlessly federate data among all systems.
An incredible advantage during a data migration is seamlessly joining data across the source and target systems. Some call this a cross-system join, but experts refer to this as a Federated query.
Nexus allows users to join data across systems and handles everything. The migration might take a day, week, month, or year, so both systems must be able to integrate while data is moved to a new system.
You will join data across many systems in a single query one day. Nexus has joined 30 tables spanning 30 systems in a single query, so your confidence in joining tables across two systems should be high.
Check out the video below to see how Nexus handles Federated queries.