Top Three Reasons Behind Database Migrations

I recently moved into a new house. Boxes are everywhere. Little pieces of styrofoam surprise me everywhere I go. There are many noises I don’t recognize or understand. And the drill I’d forgotten I even owned now occupies a prime spot on my kitchen counter. I use it daily. 

I didn’t move to a new house because I thought moving would be fun, or even easy. I knew it would be a lot of work. I moved because my family needed more space. Which is just one of many reasons someone might decide to endure the pain of moving: Cost. Quality of life. Proximity to the people/places that are important to you. 

Database migrations, it turns out, happen for the same reasons. Your current database is too hard or too expensive to scale any further (need more space). Your teams are facing burn out from firefighting too many outages and too many working weekends doing scheduled downtime (better quality of life). Your business is going great and you want to scale into more regions, all across the globe (easier proximity to important places).

With that context, let’s review the top three reasons to migrate databases in 2024 — and how CockroachDB’s powerful suite of MOLT migration tools makes it simpler than you’d ever imagined. 

Three reasons to migrate databases

1. The app/service outgrew the current database. A product was built on a legacy relational database like Postgres or MySQL. Now the app has grown and the database is the bottleneck. Sharding will add unwanted complexity. It’s time to migrate. 
2. The business is moving away from legacy tech. Many enduring businesses are undergoing tectonic architecture and infrastructure shifts as they move to the cloud and adopt distributed systems. Suffice it to say that this shift to the cloud provides some opportunities to reduce cost, improve availability, and increase efficiency. But capturing the full benefit requires distributing not just the application, but also the database.
3. The database does not fit the use case. Early on, perhaps it made sense to build on something like MongoDB or Cassandra. But then, as the application took shape, it became clear that the lack of transactional consistency or the difficulty and expense of scaling vertically or the need to expand to new regions of the globe was holding you back. The original database had become a bottleneck.

You start researching better options, and find that the grass is particularly green on the distributed database side of the fence. Maybe you want the advantage of knowing that you can scale your workload at any time with no need for application changes. Or maybe you want the ability to survive not just one node going down, but an entire cloud region failing. Or perhaps you’re trying to deliver a true low latency experience for users that are spread out across the world.

Regardless of the reason, there is always going to be some apprehension about migrating a database because database migrations are notoriously difficult. Gartner compares the process to a heart transplant, due to both complexity and the database’s fundamentally interconnected role at the center of IT infrastructure. The stakes are high. 

If you are migrating to CockroachDB, though, it doesn’t have to be so challenging. Meet MOLT: a full suite of database migration tools for Migrating Off Legacy Technologies.

The database migration workflow

The journey to CockroachDB typically follows the same migration workflow, no matter which database you are starting from.

1. Schema migration. First, you need to take the existing schema, whether that be Postgres or Oracle or SQL Server, and migrate that schema so that the same functional and performance characteristics transfer from your current database to CockroachDB.
2. Partial load. Once that schema is migrated and you have the basic structure of your database in CockroachDB, the second phase is to do a partial load. Customers will often load part of the data so that they can then quickly proceed to the next step: validation. 
3. Validation. Now you need to do a functional and performance validation to make sure that everything is working properly. This phase is iterative: load some data, do some functional and performance evaluation, rinse, repeat. 
4. Full data load. You’ll reach a level of confidence in those validations, at which point there’s a full data load stage where you pull everything that you have from your production system today into CockroachDB. 
5. Final cutover. And finally, when that’s complete, you move all of your production applications over to CockroachDB.

As you can see, it’s a complex process. Until now, it has also been a mostly manual process — which is why we built MOLT.

CockroachDB’s MOLT tooling is here to help you de-risk, execute, and validate your migration process and make your database transitions as smooth as possible. MOLT offers different tools for each of the common elements of a migration workflow, from schema migration to final cutover (as well as support for CockroachDB as an AWS DMS target). 

These tools include schema conversion with MOLT SCT; post-migration verification with MOLT VERIFY; and preview support for data migration using MOLT Fetch. For the final step we now offer a powerful tool for executing live migrations: the MOLT Live Migration Service.

Schema conversion tool for database migrations

MOLT’s schema conversion tool allows you to quickly identify and address any incompatibilities between the legacy and the new distributed system.

The real beauty of MOLT SCT, though, is that it’s simple to use. You upload a schema file that’s a pgdump from a PostgreSQL instance, and then MOLT SCT will walk you through the entire schema line by line. It will show you which statements are good for CockroachDB and which ones need some changes. It will also make suggestions for changes that will help you achieve the performance characteristics that you want from a distributed database system. 

Check out the demo below in which we begin with zero CockroachDB presence, create a new serverless cluster, migrate our schema to CockroachDB, and create a database containing the migrated schema. After nine minutes you can have a migrated schema in a CockroachDB cluster that is ready for you to start loading data into it. 

Migration verification tool 

Once the schema itself has been successfully migrated, it’s time to test it by moving some data. MOLT VERIFY checks for data discrepancies between a source database and CockroachDB during a database migration.

To ensure data integrity during a migration, MOLT VERIFY performs the following verifications:

• Table Verification: Check that the structure of tables between the source database and the target database are the same.
• Column Definition Verification: Check that the column names, data types, constraints, nullability, and other attributes between the source database and the target database are the same.
• Row Value Verification: Check that the actual data in the tables is the same between the source database and the target database.

You can see MOLT VERIFY in action in this quick video:

LMS: The live database migration service

The MOLT Live Migration Service (LMS) is a self-hosted, horizontally scalable proxy that routes traffic between an application, a source database, and a target CockroachDB database.

Self-hosted on Kubernetes and configured using Helm, LMS allows you to control which database is the “source of truth”, i.e. serving reads and writes to the application. Optionally, you can also configure the LMS to shadow production traffic from the source database and validate the query results on CockroachDB. Finally, when you have sufficiently tested your application and are confident with its consistency and performance on CockroachDB, you use the LMS to perform the cutover to CockroachDB.

MOLT LMS provides flexibility with three different cutover strategies to suit your specific needs:

• Offline Cutover: Ideal for scenarios where some application downtime is acceptable; a consistent and complete copy of the source database is ready to handle application workload.
• Consistent Cutover: Keeps applications functional during the cutover. This comes at the cost of some (temporary) latency increase.
• Immediate Cutover: Achieve zero downtime migration with no latency increase, though some post-migration consistency adjustments may be required.

Currently, MOLT LMS serves MySQL, Postgres, and CockroachDB as sources. The CockroachDB source makes it possible to do CRDB self-hosted to Cockroach dedicated (CockroachDB-as-a-Service) migrations, as well as migrations from CRDB dedicated to CRDB serverless —or vice versa.

Watch a walkthrough of each one of these three types of cutover:

Migrating to CockroachDB with AWS DMS

AWS DMS (Database Migration Service) is a database migration service that allows you to migrate between any two endpoints provided that one of them live on AWS. It works for SQL Server, Oracle, MySQL, PostgreSQL, and many other database sources. 

While configuring CockroachDB as your migration target, you’ll want to configure it as a PostgreSQL target. This leverages our PostgreSQL wire compatibility which tells Amazon that it’s moving data into PostgreSQL when the data actually ends up in CockroachDB. What’s important is that your cluster does not need to be running on AWS to use DMS. DMS requires that only one of the endpoints, either the source or the target, be on AWS. So if you’re moving to a CockroachDB cloud instance on AWS, your source database could be self-hosted or in another cloud provider.

There are essentially three steps for using CockroachDB with AWS DMS:

1. Create a replication instance
2. Set up a source and target endpoint
3. Create your migration task

Adam explains each step in this clip:

This is a simple example of AWS DMS, but we have customers who have been moving large amounts of data in a very seamless and quick fashion using the tool. If you’re interested in learning more, read our documentation about how to set up CockroachDB to use AWS DMS.

If you’re looking for more info on CockroachDB migrations, take a look at our YouTube videos demoing the MOLT migration suite and our webinar showing how to migrate your database without losing your sanity, with MOLT Live Migration Service