Next, we can discuss the second phase of database migration: Coming up with a plan.  Now your goal here is to map out your final solution in detail.  This includes both the end state, as well as all required steps.  Now this is why you created that list of dependencies in phase one.  You will use this list to figure out what needs to be changed, as well as what order in which to make the changes.

Again, building prototypes (or proof-of-concepts) can be very useful.  If you don’t know where to begin, then try migrating a test database.  As you figure out each step, write it down.  This will also help you choose between multiple options.  Your best bet is to try them all out and discover which works best.  

Now, a migration involving a lot of complex, manual steps can be very risky.  By using the right tools and scripts, you can ensure that each step is completed correctly and in the proper order.

Of course, a prototype won’t necessarily reveal everything.  For example, replicating a sample database might only take minutes or hours.  But replicating a production database could take weeks or even months.  So when building your plan, make sure to include realistic time estimates for every step.  And you also need to consider the costs.  You should understand exactly what your new solution will cost before you try to implement it.  By creating prototypes and using the Google Pricing Calculator, you should be able to get some pretty accurate predictions.

Now depending on what you need to do, your final plan might end up being pretty simple, or extremely complicated.  But every migration plan should have two main things:  A methodology and a fallback plan.  Your methodology will determine how you will accomplish your migration.  And your fallback (or backup plan) will specify what you should do if the migration fails.

So let’s talk about some of the common migration methods.  First, you are going to need to decide if this is going to be a one-time migration.  Or if it will be a continuous migration.  A one time migration is generally simpler and it gives you more options.  Continuous migrations are generally more complicated, but they are useful in cases where you do not plan to retire your old database.

A one-time migration will typically involve doing either a backup and restore or replicating from a snapshot.  Basically, you will take the old database offline and then export your records.  Then you will bring up the new database and import them.  Once everything looks good, you can start updating your clients to connect to the new environment.  Obviously, this technique will require some scheduled maintenance downtime.

In a continuous migration, you need to replicate your database without taking it offline.  Data will be copied on an ongoing basis over hours, days, weeks or maybe even months.  Once your synchronization is complete, you then can start migrating your client connections.  Of course, depending on your exact scenario, this still may require some downtime.  But, if you can’t actually achieve zero downtime, then at least it should be near zero downtime.

So your final strategy will depend on several factions: How big is your database? How often is the data modified? And will you be keeping the old database, or is it going to be retired?  You also need to think about how much downtime you can afford.

A one-time, offline migration works great if your database isn’t too big.  And you don’t change your records too often.  And you are planning to retire the old database.  Continuous, live migrations work better when your database is huge, your records are constantly changed, or when you cannot afford a lot of downtime.

For one-time migrations, you probably should be focused on ways to minimize your downtime.  Maybe you can break your migration up into stages.  If you already are doing incremental database backups, then you can use those to copy the majority of your data to the new environment beforehand.  Then, during the actual migration, you only need to go offline long enough to copy any records modified in the last day or two.   

For a continuous migration, the main challenge will be in migrating over your clients.  If you only have a few, it probably won’t be too bad.  But if you have a lot, it’s going to be difficult.  In that case, you may want to consider using one of the following techniques:  

First, you could first build a data access microservice in-between your clients and the database.  This would allow you to remove any complex connection logic from your clients.  Instead, you shift all the logic to the microservice.  So this means once replication is complete, you can update many different clients at once.  This also will make it easier to switch back in case of an emergency.  But of course, this technique requires additional code, steps, and time.

The second way you could go would be to change your clients to do split reading and writing.  So if you didn’t want to mess with creating a microservice, you can simply add additional logic to all your clients.  Basically, you modify them to connect to both the old and new databases at the same time.  And then you can add or modify records in both as required.  

So that means initially, your new database will start out empty.  However, over time, the two databases will slowly become synced.  After that happens, you can then update your client code to stop connecting to the old database.  Now of course, this does require a lot of client changes.  And depending on the total number of clients you have, it might be impractical.  

Still, it does have its advantages.  This will allow you to slowly migrate over to a completely different database with zero or almost zero downtime.  It can also support fallback if one of your databases becomes unresponsive.  The key is to carefully consider all your requirements, and pick the method that will work best for you.

Now as I previously mentioned before, you will also need a fallback plan.  Now matter how much preparation you do, something can always go wrong.  And that is why it is important to have some way to back out if you find you can no longer proceed.

You do not want to realize in the middle of a 8 hour migration that your new environment is not going to work and you have no way of going back.  So figure out the worst case scenario and plan accordingly.  Also, make sure to schedule enough time for failure and recovery.

So if you are doing something like a Blue/Green deployment, then you can probably switch back to your old database pretty easily.  In a Blue/Green deployment, your new environment is 100% backwards compatible with your old environment.  

Now what if your migration is going to be extra risky?  In that case, you could try using a Canary deployment.  With a canary deployment, you run both the old and new databases at the same time.  Then you only migrate over a small partition of your clients and verify that everything works for them before migrating the rest.  Either way, your fallback plan should be pretty straightforward.  You just migrate your clients from the new database, back to the old.

What about scenarios in which you need to make a backwards incompatible change?  You might need to do a complete reverse migration if something goes wrong.  Now whatever you need to do, you need to figure that out ahead of time.  Do not wait until it is too late.  If you do not have a backup plan, then you may find yourself stuck in an impossible situation.

Also remember, automation can make a huge difference!  The more complicated your migration plan, the more important it is to automate as much as possible.  Fewer manual steps means lower risk.  In addition, when you automate your migrations, that means you can easily automate your rollback as well.  Consider your options, weigh the costs, and get everyone to agree on a plan before trying to change anything.