We have a weird situation. We have an old database and new database both different schemas (SQL Server). We are using abinitio to replicate data from old to new.
Now if for some reason the new structure does not work for us, we want a rollback plan to copy data from new to old. But this could also mean records that are updated in new and also inserts.
Also if this has to be done with no downtime to customer, how can we achieve this?
Best Answer
It's not a weird situation at all - lots of project managers say they want this until they discover just how hard it is. This is way, way harder than it sounds. Let's take a simplified scenario to illustrate it:
CustomersV1 table, original bad design you're moving away from:
CustomersV2 table, new design:
Moving from CustomersV1 to CustomersV2 is hard enough, but how do you deal with:
In reality, I've been on several projects where managers wanted to accomplish something like this, but once we even white-boarded out the changes to just one table, they realized we couldn't keep them both in sync at a price that the customers were willing to pay.
I have been on one project where the customers did pay, and here's how they did it:
So to start the discussions with your management, lay out the differences in just one of the complex tables, and white-board out what would happen if they had to fail back from the new to old schemas. Talk about the ways you could lose data, and how much work it would be to build apps that would migrate the data back and forth.