What is your workflow for planning a data migration?

Question

So many times I've been brought in at the end of a software development effort and been told something like "okay, we've got all this new code and it requires tables to change and data to be migrated".

It seems like every time it's a one-off, shoot-from-the-hip, best-guess scenario. I feel like this is my weakest skill set as a DBA.

I'd like to get into some patterns for approaching, managing and testing data migrations.

Please clue me in to some best practices and/or where I can get learning material to help me get better in this area.

Answer 1

Every time I've done it, we've gone for two passes ...

1. take a snapshot, and working on a different server, use that to determine what has to be done for the migration, and script it.
2. once they have the script in hand, the snapshop is restored on the test system, and it's timed to see if it'll run within the required time, or it's tuned and modified until it can.
3. have the stakeholders sign off that nothing looks wrong with the data on the test system.

Then, over a weekend, you have a scheduled outage :

1. Friday night, the systems that use the database are brought down, a full cold backup is made, and the scripts are run to migrate/modify/whatever to the data
2. Systems are brought back up under some private address or somehow set up so it's not open to anyone but the stakeholders for acceptance testing
3. If the stakeholders approve, the system's put online and made public; if not, the database is restored from the backup made on Friday night, and you start the process over again.

With our schedule, the database folks generally had from 6pm on Friday to 10am on Saturday to run the backup & migration scripts, so our goal was that they'd run in under 8 hrs (~6 of that was backups), so we'd have some time for our testing and corrections before it got released to the stakeholders.

Stakeholders were given their time windows in advance, so they knew to leave their weekend open for testing at the beginning of the window. They'd also be told the end of their window, typically Sunday afternoon, where if everyone hadn't signed off, we'd have to start rolling back.

Oh, and of course ... if someone had a change during either of the acceptance tests, and we made a change, it meant that all of the stakeholder's sign-offs were voided, and they had to re-test ... so we'd try to give them all a while to look for issues and run any corrections as a batch, rather than apply them one at a time.

Luckily, the only times I've had one of those situations where we couldn't have significant downtime, the sytems I was migrating were fed from scripts, not user input, so I could just have two parallel systems going, and swap them out when things got signed off. (only once was there a problem, when my boss insisted that we take a full backup, not understanding that the whole thing was going to still be online at a different IP ... so what should've been a 5 min outage on a bad day became a 5 hr outage.)

Answer 2

It all depends on the volume of data compared to the power of the hardware that supports the database and the agreements on the availability of the system. Do you happen to have some downtime or should it all be done online? Start cleaning the data, wiping out outdated rows as much as possible. This is a project on itself. If the data is clean and valuable, have the user decide about downtime. If downtime is available it is fairly straightforward, if their is a known transformation that should be applied on the existing data to form the updated collection. If there is no - or very little - downtime allowed the challenge begins. Oracle supports this in a few ways like online table redefinition and - new in 11g - edition based redefinition. With online table redefinition you can prepare the tables to take their new form. This can be done while the application is running on the old form of the table[s]. If they are all ready you can switch to the new form of the table[s]. This will also be the moment to introduce the new application code and at the same time marks the beginning of the downtime required to put the new application in place. The older historical data can be prepared before the live data migrates and kept in sync using tools like Oracle Golden Gate. In such a scenario you effectively build a new database that takes over the role of the old database. Edition based redefinition is better suited for if no table changes are needed. There are tons of options to consider and I think that it is hard to give a good rule that always works.

It's an interesting subject, Ronald.

Answer 3

Good answers so far. I'll add a couple more points for consideration.

First, when you can do your migrations with simple SQL DML, you can largely rely on your SQL engine to make sure all rows are successfully processed. I've been involved in migrations, though, where some of the migration was a little more complicated -- there were actual data transformations as data was moved into a new structure. In these cases, it's important that you have a process that can handle the following items:

• Count records in vs. records processed.
• Detect errors during transformation and deal with them in a way that allows the transformation to continue and permits re-processing of the "bad" records once you've identified a fix.
• Record counts should include "bad" records -- ie, records-in = records-out-good + bad-records
• If your transformation changes record counts (one input record becomes more than one output record, for instance) have a way to predict the number of output records you'll end up with, and then test your results against that count.

The other point I'd add is that it's important to have a plan for what you're going to do if/when things don't go as planned. This is really a function of the deployment as a whole, but it's one that seems to be glossed over frequently enough that I thought it was worth a mention.

Answer 4

An overview of how to do it

To start with

• You have the "after" database in test/UAT/whatever "working DB"
• You have the "before" database in production. So use it to create a copy of production somewhere = "reference DB". And another as "script test DB"
• I also hope you have a bunch of development scripts with your ALTERs etc. If so, another copy of production with your development script applied, cleanly and in order is useful = "change DB"

Next, downoad Red Gate Compare tools or something like Embarcadero SQL Change manager. You can't easily migrate without it. The cost is trivial to the amount of time saved. And most importantly, the scripts generated make changes in a single transaction which means a clean deployment

Now,

• generate change and rollback scripts using the tools comparing between the "reference" and "change"
• apply the change script to "script test" and compare back to "working DB"
• apply the rollback script to "script test" and compare back to " and compare back to "working DB"

Now, you have safe+tested change and rollback scripts to apply whenever.

And of course you backup the database before any change because statistically shit will always happen eventually.

The Red Gate tools can also compare against a folder which is under source control. We then capture the ALTERs etc in our source control separately to the actual change scripts.