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?

1 Answer 1


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.

  • Thank you. We use AbInitio instead of SSIS to do the task you suggested. Bi-directional migration does seem cumbersome and data-loss friendly. So was wondering if there is a cleaner approach. I guess not.
    – priya
    Mar 2, 2017 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.