What is Database Migration and How to Do it Properly

Every business must go through an occasional database migration to optimize costs or enhance service reliability. The problem is that these processes are notoriously difficult—some database migrations take days to complete, and most require some data reformatting or changes in the app's code. There's also the ever-present threat of corrupting data or causing too much service downtime.

This article provides a big-picture view of what a database migration entails and how to create (mostly) painless strategies for moving data between two storage solutions. We also list the most common pitfalls of database migrations to ensure your team is ready to transfer assets without any unpleasant surprises.

What is Database Migration?

Database migration is the process of transferring data and workloads from one or more platforms to a better-suited storage solution. Companies perform database migrations for various reasons—here are a few common ones:

• Hoping to reduce IT expenses by moving to a database with better resource usage.
• Adopting bigger storage to meet a growing business need.
• Upgrading to the latest database version to maintain compliance or improve security.
• Pursuing a different database type to lower latency and boost app performance.
• Retiring an outdated legacy system to modernize the database.
• Merging data from multiple databases into single storage to get a unified view of files.
• Performing data repatriation to move away from cloud storage.

Some simpler database migrations involve moving data from one database instance to another storage of the same type (e.g., transferring data from a MySQL database to another MySQL system on a different server). Other migrations, however, are more complex and may involve different types of databases (e.g., moving data from a MySQL database to Cassandra).

A recent study reveals that over 50% of database migrations exceed the predetermined budget or negatively affect the business. Here are the main reasons why database migrations tend to be so challenging:

• A well-thought-out database migration requires various complex steps (data audits, database schema conversions (if the team is changing engines), functional and performance tuning, post-migration testing, etc.).
• Companies often migrate databases that host mission-critical data or apps, which requires careful coordination of downtime and data loss prevention measures.
• Migrations involve a great deal of time and team effort, from initial strategizing and preparations to the actual rehosting and post-migration testing.
• A database migration often requires the team to reformat the current data to prepare files for the new system. Related apps and services typically need some tweaks too.

Types of Database Migrations

There are two main types of database migrations: big bang and trickle migrations.

Big bang database migrations

A big bang migration moves all data and switches to the new system in a single operation. This migration strategy typically has four steps:

1. Design phase: The team defines the migration scope, analyzes data samples, and sets a schedule (and a budget).
2. Development and testing phase: The team prepares for the upcoming migration and runs the necessary testing.
3. The big bang: The team shuts down the current database and migrates to a new environment. The more data there is, the longer the process takes to complete.
4. User acceptance testing (UAT): The team verifies the migration results to check if everything works correctly.

This type of database migration always involves some availability issues, and an error often requires the team to repeat the entire process. However, the big bang approach is simple as it happens in a time-boxed (albeit exhaustive) event.

Big bang migrations are the go-to option when the team can define the exact scope from the outset or when other projects dictate the deadlines.

Trickle database migrations

A trickle migration is a more agile-like approach to moving a database. The team breaks down the transfer into sub-migrations, each with its own:

• Scope.
• Goals.
• Schedule.
• Transfer deadline.

The team confirms the success of each sub-migration individually, which enables a company to re-work only the failed sections in case of an error. However, the drawback of this approach is that the migration takes more time than a big bang. The team must also run two systems simultaneously, which requires extra resources and effort.

The trickle approach is a common choice when the team can logically split the migration into several stages or when the project scope is difficult to define.

Database Migration Benefits

Here's a list of the main benefits of performing a database migration:

• Cut IT expenses: Using an old or outdated database adds unnecessary costs to your overhead. Moving to a new storage system lowers infrastructure expenses and enables the team to spend less time maintaining the database.
• Pave the way for cutting-edge tech: Keeping up with the latest tech trends is difficult enough without worrying about compatibility with an outdated database. Retiring a legacy system in favor of a more modern database enables the company to more easily stay up to date with the latest IT opportunities.
• Better performance: Moving to a newer, better-fitting database leads to faster response times, which improves your app's performance and ability to scale. The company's overall time-to-market also gets a boost.
• Free up employee time: Modern database systems are significantly more hands-off than legacy storage and require less troubleshooting. A database migration frees up time for your staff to work on projects that directly impact the bottom line.
• Decrease data redundancy: Outdated databases often suffer from syncing mistakes and duplicate data. Migration is an opportunity to filter through files and remove data redundancies, which leads to better storage space use, a lower likelihood of error, and less chance for data integrity issues.
• Centralize vital info: A database migration groups all scattered mission-critical and valuable data in a new database. Keeping everything in single storage leads to better accessibility (especially if you adopt a cloud database) and enables next-level analytics for more informed business decisions.
• Boost security: Databases are the go-to target for cyberattacks like SQL injections or packet sniffing, and criminals always prefer going after targets with outdated database security. Performing a migration means updating the database with the most recent security patches that prevent the latest data breach tactics.

Database Migration Challenges

Here's a list of the most common challenges companies encounter during a database migration:

• Data loss: Every database migration comes with the risk of data loss. That's why testing is vital—the team must look for data loss or corruption potential during the planning stage and verify whether post-migration data is present and intact. Data backups are also a must.
• Identifying an ideal database system: A migration makes little sense if you see no improvements from the project. Consider all the options on the market and know how to pick a database type (whether paid or open source) that is worth the migration effort.
• Precise predictions: One of the biggest challenges of database migration is making the process predictable. You want to avoid surprises when you are twenty terabytes deep in the migration project, so spend as much time in the planning phase as possible.
• Defining a scope: Companies with disparate and siloed databases in different departments or locations often struggle with defining a precise scope of the upcoming migration. There's always the chance of leaving out some data that belongs in the new database.
• Necessary software changes: Converting all schemas and normalizing data formats is a common challenge during migrations. Teams also typically must make changes to the app code, which further complicates the project.
• Post-migration security: Once data is in a new environment, the team must ensure that the new database is as secure as possible. You need an experienced team to check if all the security measures are in place (such as at-rest encryption and proper IDSes) and that there are no potentially exploitable vulnerabilities.
• Poor data filtering: Teams often bring unnecessary data to a new system, which wastes resources, prolongs the migration, and creates redundancy problems.

How to Do a Database Migration?

Every database migration is a unique project, but all of them go through a similar multi-step process—let's look at how companies plan a database migration.

Step 1: Pre-Migration

The first step is to form a database migration team. At the very least, you require an expert for the database engine (two of those if the target database has a different engine) and a network specialist well-versed in your servers, ports, and firewall rules. A cybersecurity expert is also a worthwhile addition to the team.

Once you assemble enough talent, the team should define the scope of the upcoming database migration. The team must determine the following:

• The goal(s) of the migration (e.g., scale the storage, move to a new type of database, reduce data complexity, improve the performance, etc.).
• The resources the migration team requires to do their job.
• The deadline for the upcoming project.
• Basic parameters (e.g., object types, source objects in scope, connection parameters, etc.).

The migration team must also make early decisions concerning potential data migration tools and plan for testing procedures, plus create a high-view framework for the entire process.

Step 2: In-Depth Database Analysis

Once the team knows the migration scope and goals, staff members must analyze the current database. Here's what the migration team must answer:

• What is the size of the database?
• What's the state of the stored data?
• How many schemas and tables do we have?
• Do the tables have LOBs (Large Objects)?
• How large are the tables?
• What are our transaction boundaries?
• Are there any engine-specific data types?
• How "hot" (i.e., busy) is the database? What are its dependencies?
• What kind of users, roles, and permissions does the database support?
• Is there any compacted data? If not, can you compact some data to speed up the migration?
• How can the migration team access the database (firewalls, tunnels, VPNs, etc.)?
• Will there be enough bandwidth to move all the data?
• Can apps and services that rely on the database afford downtime? If yes, how much?
• Does the company require the current database to stay alive after the migration?
• What are the desired HA metrics?
• Are you transferring all the data in the current database? Does it all belong in the same place?
• Will the team have to make some changes to the app code?
• Does the database require some refactoring?
• Can we adopt any security improvements not present in the current database?
• Does the big bang or the trickle strategy make more sense?

This phase is also an ideal time to check the existing database for duplicate values, inconsistencies, and incorrect info. Avoid bringing these issues into the new system.

Once the team thoroughly understands the current database, it's time to determine the best option for the target system. The team creates a birds-eye view of how the database migration will unfold for each worthwhile database type. Then, the decision maker picks the target system.

Step 3: A Step-By-Step Migration Plan

Now that the team knows the database migration requirements and what the desired system should look like, it's time to create a detailed, step-by-step migration plan. The team assesses the most efficient way to transfer data and workloads.

This step is also the right time to:

• Define a precise migration schedule.
• Plan how to reduce downtime during data transfer (if the database traffic is very high, it might be unrealistic to plan a live migration).
• Create backups of all involved data.
• Plan for database schema conversions if you are performing a heterogeneous migration (i.e., a migration between different database engines).
• Determine contingency plans in case something goes wrong during the migration.
• Perform threat modeling for the target database to ensure it's secure by design.

Database schema conversions are often too resource-intensive and time-consuming to perform manually. Most companies opt for a tool to expedite the process.

Some teams opt to test the migration build at this point. Start with a small subset of data, profile it, and convert its schema. This process ensures all mappings, transformations, and quality rules work before you go all-in on the migration process.

Step 4: Database Migration

The team starts the migration process. If the previous steps were successful, this stage goes without any issues (delays, budget overruns, migration failures, etc.).

Most companies perform a database migration at a time they can afford service availability issues, such as on weekends, at night, or on a public holiday. Despite these precautions, most companies nowadays try to outright eliminate service interruptions with database migration tools that offer data synchronization or the Change Data Capture (CDC) functionality.

Step 5: Post-Migration Checkup

Once the database migration ends, the team must analyze the new environment for:

• Missing or corrupt data.
• Inconsistencies, duplicate values, or incorrect info.
• Performance-related issues.
• Security vulnerabilities.
• Issues with connections to apps and services.
• The validity of all permissions and roles.

The team then fine-tunes the new database to ensure optimal performance levels, sets up monitoring, and brings the new database to production. The team's decision-maker evaluates whether the new system meets the pre-migration goals.

Depending on the migration plan, this final step may also include the deletion of the original database.

Never Rush into a Database Migration

Database migrations are not something anyone looks forward to, but a slow-and-steady approach to transferring storage takes most of the risk and headaches out of the project. You also remove the pressure off the team the more you prepare, so ensure every migration goes through thorough planning before you go after a more fitting storage solution.