I spend a lot of my time working on system migrations, it’s pretty much all I’ve done for the last few years. Often, I work with the same clients again and again, but when it’s a new client and they don’t have much experience with migrations, some of the things that are best practice in migrations can be quite hard to justify.

One of those practices that people sometimes have a problem with spending time on is building sensible control totals, a set of values which should clearly and simply define the success or failure of a migration.

If you are migrating a financial application, a very simple set of control totals might be:

• Number of accounts to be migrated
• Total outstanding debt across all accounts to be migrated
• Number of accounts in bad debt to be migrated
• Number of bank account details to be migrated
• Number of credit card details to be migrated

Once you have these rules written in plain English, you then need to get someone to build the source database extract queries to produce these figures, and someone to build the same rules on the target database.
Then, when you perform your migration tests, you execute the control totals on the source database prior to the migration, and the same totals on the target database post-migration. If both sets of figures don’t add up, then you have a problem – either a set of accounts has not migrated, or the wrong data values have been inserted.

While people often say that control totals don’t matter, because individual record failures will be highlighted by database loading scripts, their real value is in verifying that the dataacross both the legacy and new systems are in their expected format.

An example of the issues that control totals can highlight is if the source application stores prices in a decimal value in the table ‘prices’, storing a price of $10 as ‘10.00’ in the database, and in the target application the price is stored as an integer in cents, storing the same $10 as ‘1000’. If you insert the decimal value without multiplying it by 100, and simply count number of records inserted into the ‘prices’ to verify it, then you may not find out until it’s too late that you’ve just cut every price in their system to a fraction of what they should be..

A  control total on ‘Total sale price of all products’ would quickly and easily pinpoint the exact issue.

Hopefully this will help some people out there with their own system migrations, it’s certainly helped me crystalise my own thoughts on their worth for any future clients that ask why they should spend time and money on what at first glance can look a pointless extra step.