Migration to new database 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?
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:


CustomerID uniqueidentifier
CustomerName varchar(100)
BalanceOutstanding - decimal, total amount they owe us


CustomersV2 table, new design:


CustomerID - integer, autonumbered identity field
CustomerName nvarchar(100) - because now we want to store unicode data
(No BalanceOutstanding field - instead we're going to have the app calculate current balance by querying several other tables)


Moving from CustomersV1 to CustomersV2 is hard enough, but how do you deal with:


Keeping a mapping of the old CustomerID to new CustomerID
What happens if a customer puts unicode data in the new CustomersV2.CustomerName, which simply can't be sent back to CustomersV1.CustomerName due to the mismatched data types
Changing application code to refer to BalanceOutstanding gathered via different ways


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:


Built custom SSIS packages to keep the data in sync in both directions
Built complex business rules to handle scenarios like we discussed above (what happens if someone stores data in the new schema that the old schema won't handle - in that case, the customers were willing to simply walk away and lose that data)
Tested migrations in both directions, repeatedly, leading up to go-live
Froze the database schemas on both sides months in advance to prevent changes to the complex SSIS packages


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.