Top three reasons to migrate databases

Top three reasons to migrate databases

Moving is challenging. I recently moved into a new house. Boxes are everywhere. Little pieces of styrofoam surprise me in most rooms. There are so many noises I don’t understand. And the electric drill I used maybe twice a year is sitting on my kitchen counter. I use it daily. 

But I didn’t move into the house because I thought moving would be pleasant. I moved because my family needed more space.

There are plenty of other reasons someone might move: Cost. Quality of life. Proximity to the people that are important to you. Database migrations are the same.

With that context, let’s review the top 3 reasons to migrate databases in 2022, and some of the ways migrations can be simpler than you imagine.

Three reasons to migrate databases

  1. The app/service outgrew the current database. A product was built on a traditional 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. We don’t have to unpack all the benefits of the cloud here. Suffice it to say there are some opportunities to reduce cost and increase efficiency. 
  3. The database does not fit the use case. Initially, it made sense to build on something like MongoDB or Cassandra but as the application took shape it became clear that transactional consistency was a priority and a relational data model was needed. 

There is particularly green grass on the distributed database side of the fence. Maybe you want the advantage of knowing that you can scale your workload at any time without any application changes. Or maybe you want the ability to survive more than just a node going down, but an entire data center failing. Or perhaps you’re trying to deliver a true low latency experience for users that are spread out across the world.

Regardless of why you want to migrate. There is always going to be some apprehension about the difficulty of migrating a database. But, if you’re migrating to CockroachDB, it doesn’t have to be so challenging.

How to migrate databases well

Migration journey to CockroachDB

In the image above you see what a typical journey to CockroachDB looks like:

  • First, you need to take the schema that exists in some other database platform, whether that be Postgres or Oracle or SQL Server, and you need to migrate that schema so that the same functional and performance characteristics transfer from your current database to CockroachDB.
  • 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 proceed to the next step. 
  • 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. 
  • 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. 
  • And finally, when that’s complete, you move all of your applications over to CockroachDB.

To make several steps in that process more simple, we’ve introduced two new tools: A schema conversion tool and support for AWS DMS. The tools are the foundation of a new migration suite we’re calling MOLT (which might stand for “Migrate Off Legacy Technology”). 

Schema conversion tool for database migrations

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

The schema conversion tool allows you to address the incompatibilities between the legacy and distributed systems quickly and easily. Check out the demo below in which we begin with zero presence on a CockroachDB cluster. We will create a new serverless cluster and we will migrate our data over there. After nine minutes you will have a migrated schema in a CockroachDB cluster that you can now start loading data into. 

CockroachDB supports AWS database migration service

After migrating the schema you need to get the data into CockroachDB. There are a handful of ways to do that. You can read about each of them in our documentation or watch the clip of Adam Storm, director of engineering, explaining each option. For the purposes of this blog, I want to introduce our support for AWS DMS. 

What is AWS DMS?

AWS DMS is a database migration service that allows you to migrate between any two endpoints created through the AWS UI. It works for SQL Server, Oracle, MySQL, PostgreSQL, and many other database sources. 

While configuring CockroachDB you want to specify for it to run as a PostgreSQL target. That 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:

\ \ We know that 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. If you're interested in learning more, we have this fully explained in our documentation about \\[how to set up CockroachDB to use AWS DMS](https://www.cockroachlabs.com/docs/stable/aws-dms.html).

There is also a webinar in which we demo the migration suite and go into more depth about the benefits of migrating to a distributed database. If you have further questions please connect with us in the CockroachDB community slack.

About the author

Dan Kelly

Dan has been producing technical blogs, videos, whitepapers, and webinars at Cockroach Labs for the last 4+ years. His mission is to identify the most pressing problems developers face and to create content to solve them. Outside of work Dan is usually chasing his three year old daughter or cooking something indulgent.

linkedin link

Keep Reading

CockroachDB serverless is generally available & more product updates

When we set out to build a better relational database seven years ago, we envisioned a solution that was scalable, …

Read More
What is Distributed SQL? An Evolution of the Database

As organizations transition to the cloud, they eventually find that the legacy relational databases that are behind some …

Read More
What is a Serverless Database?

Before we define what a serverless database is, perhaps we should talk about why there seems to be building momentum …

Read More
x
Developer Resources