What happens when you need to move datasets from one database to another? Your organization is updating its systems, or you’re moving to the cloud, or your company has been acquired and you need to transfer your data into your new parent company’s databases.
Welcome to the database migration experience.
No database migration is easy, but the right approach and strategies can make the data migration process easier and smoother. Here are four key practices to achieving a smooth, successful database migration.
Leverage your database migration to improve your data processes
Has your database won a blue ribbon for “Most Perfectly Ordered Database Ever”? No? (Don’t worry; we haven’t run across any databases that sport awards like that.) Well then, a migration is the perfect time to improve the state of order in and structure of your database.
The first step is mapping out exactly what is happening within your current database. What sources feed your database? Which targets does your database supply? An automated data lineage solution is your best friend when it comes to creating a data flow map into, within and out of your database.
Once you have a clear idea of your current database and data flow structure, you can design an improved plan. Scrap unneeded or outdated datasets; remove redundant or obsolete processes. Maybe there are data pipelines that have been cabled together over years, and they could be streamlined and made much more efficient.
Now that you know what your database currently looks like, and what you would want it to look like, you can move on to creating a database migration map.
Create a database migration map
The point of a map is to help you get from the place you are to the place where you want to be. If you’re in Hicksville, Ohio, and you want to get to Honolulu, Hawaii, you’re going to need a map (and a decently-sized travel budget).
If your data needs to make the trip from IBM Db2 to Azure, it is going to need a map that lays out:
- The data structure of your any source data asset and the intended target data asset
- The process needed to transform the source structure to the target structure
For any data asset being migrated, the data map will show the way – both in where the asset is going, and in what to do to it to get it integrated successfully in its new location.
Choose the right database migration service
I’ve moved house several times. Once we moved about half of our possessions on our own, then got a small van to move the rest. The other two times we used professional movers: one who charged by the hour and the other who charged a flat rate for the entire job. Obviously, the appropriateness of each moving option varied based on the situation (e.g. how far away the new house was, the amount of stuff we were moving, and if we needed the movers to disassemble/reassemble furniture).
Being fallible (and inexperienced) human beings, we did not always match up the right moving option with the right situation. That’s how we ended up spending more money than we intended, needing to wait 24 hours before we could plug in our fridge, or unable to put our 10-month-old down on the living room floor lest she find one of the nails that a mover had scattered all over the floor while reassembling our bookshelf.
Oh, well. Live and learn.
We’re guessing, however, that you’d rather not have to “live and learn” how to perform a database migration without causing yourself damage or inconvenience. You’d rather choose the right moving option the first time.
If so, make sure to define your database migration situation and research which database migration tools are the most appropriate.
If you’re migrating to a specific database platform, it’s possible that the platform has its own migration service. Microsoft Azure, for example, offers the Azure Migrate service, which covers everything from discovery and assessment on your old database to migration and optimization of your new database. Amazon Web Services and Google Cloud also have database migration services facilitating your migration to their database products.
If your target database is not on a major platform, a little more research may be required. Some key questions to ask:
- Is the source database technology (e.g. MySQL, Oracle, PostgreSQL, Spanner) the same as or different than the target database technology?
- Is the source database model (e.g. relational, multivalue, object-oriented) the same as or different than the target database model?
- Is the relationship between number of source and target databases that of direct mapping (1:1), consolidation (multiple sources, one target), distribution (one source, multiple targets) or redistribution (multiple sources, multiple targets)?
- How much downtime is acceptable? (The less downtime available, the more migration processes will need to be handled in parallel and the more change data will need to be effectively dealt with.)
Your database migration service will be doing the heavy lifting in your database migration, so choose one that you’re sure is up to the job for your specific situation.
In addition to system-specific tools that help you migrate to a specific database system, an end-to-end data lineage tool is an invaluable resource throughout a migration. Where is my Point-of-Sale system getting its information from right now? How many users and source systems do I still need to update about the database change? A data landscape-wide view enables you to easily assess the big picture of how the migration is progressing and the effect it is having on your data assets and data flow.
Plan the final stages of your migration carefully
It’s not over ‘til it’s over.
Just like in the physical world, migration success will be cemented or, alternatively, totally thrown off track by how it goes at the very end.
The end of a database migration is tricky. Clients have to close existing connections to the old database and create connections to the new database. And unless your business processes can accommodate substantial downtime, there will still be changes in your data recorded in your source system even after your clients have closed their connections, which you will then need to migrate over to the new database (“draining” your source database).
Invest thought into planning the timing and methodology of the final switchover. Things to think about:
- What are the availability needs of the clients connected to the database?
- How can you minimize the amount of migrating data as the switchover time approaches?
- Can you test concurrently with the migration (minimizing testing downtime at the final stage)?
- How do you ensure that the way your change data is migrated won’t cause consistency or order violations?
While first impressions are key for human interactions, it’s last impressions that make the difference for database transactions.
Just do it
If you surveyed 1000 random data professionals about what they’d like to do this coming weekend, we bet that 0% of them will say “migrate databases” or even “design a data migration strategy.” Even for people who love data, database migrations are just not appealing.
But you gotta do what you gotta do. Keep a stiff upper lip. Give it your all. Believe in yourself.
And once you’ve imbibed your fill of can-do catchphrases, go make that database migration happen.
