Applications generally have a lifespan of about 10 years.  This means that sometime during your working life you will be faced with the challenge of migrating data to a new EDRMS, CMS, or other record-keeping system. It’s a common scenario when line of business systems reach end-of-life, and one that requires some  consideration before you go to tender, make a capital funding submission or do anything at all.

Migrating data between systems is complex and generally difficult so you will need to be prepared.  Often your implementation partner, whether it is the vendor, an external contractor or even your internal IT staff, will try to minimise your expectations. So before you begin it’s vital to decide what is feasible or reasonable to migrate.  Do not be swayed by technological considerations at this stage.  Focus on the data requirements of the business and recordkeeping compliance.

It’s also important to understand some basic information about database-driven applications.

Databases often sit behind applications known as the front end.  The application provides forms to add data to the database and provides a compiling mechanism for the data to create ‘Records’ in the form of search queries and aggregation.

Databases are a way of storing, managing and retrieving information. They do so through the use of tables. Database tables consist of columns and rows. Each column contains a different type of attribute (what we call metadata) and each row corresponds to a single record.  Each record has a unique ID.  The relationship of the tables to each other is represented through a schema.  Schema tells you the database names of the attributes and the relationship between the attributes.

This is typical of the common relational databases such as Microsoft SQL, Oracle and IBM DB2. These can also add data and compile data based on scripts and queries. SQL (Structured Query Language) is a standard language for making interactive queries from and updating a database.

Database Schema

In order to undertake a migration of data successfully, it’s essential to have a clear understanding of what is known as the Database Schema. This sets out the links between tables and identifies metadata fields and is one of the essential elements you should have in place before you begin a migration.

If at all possible get the schema for your database. This may be difficult as many vendors regard it as their intellectual property (IP).  However t is vital to have so you know what front end metadata fields are compiled i.e. data is pulled from several different tables.  A good example of this is what is known as current location in a number of different applications.  This can be compiled from the record number table, the officer table and the date moved table.  This means that it will not behave the same way when extracted from the database.  It is also important to find out where attachments live, whether “Objects” are stored separately to the data on a shared drive and where log files live.

You will need to get to know your IT people. This means making sure you aren’t talking at cross-purposes.   Database administrators (DBAs) generally will not understand records management terminology so get used to translating into database speak.  Always clarify when using RM jargon – terms such as file, disposal and archive mean very different things in IT speak. Chocolate biscuits are still good currency when cultivating IT. Terms such as Defensible disposal can be used to differentiate the records management meaning from the IT meaning of disposal (which is just to purge).

You must also know what version of your application you are running.  Identify any cumulative updates and patches to the version of the software you are currently running.  This is where your newly acquired relationship with IT will start to pay dividends as they should know this information.  They will also know what type of database your application backends to e.g. MS SQL Server, Oracle, IBMs DB2?

Records management professionals are still the subject matter experts when it comes to information, so ensure that you are involved in identifying which metadata fields will be migrated and which ones are not required.  After you have identified all of the metadata fields that are required, check which tables these fields correspond to in the database.  This is where your schema is vital.  Pay particular attention to concatenated fields ie ones that pull from multiple tables.  Verify all decisions about metadata fields that will or won’t be migrated with the relevant business units to test your assumptions.  Often usage changes over time and you may need to migrate fields that were previously not used. When analysing the metadata fields that are to be transferred it’s important to know whether they have configuration constraints. Wherever possible try to translate old metadata fields to new metadata fields.  The old practice of dumping all the data that was too difficult to map into the Notes field does not work and should be avoided at all costs.

Data Migration is also a time for data cleansing.  When it comes to data cleansing, records managers tend to fall into two camps, those who want it perfect and others who decide it is too hard. There is a middle ground that can be achieved by calculating the best use of your time and resources. Do as much as you can through the front-end of the application so your actions are recorded in log files.  Some data cleansing can be done via a back-end script but changes will not be recorded in log files so avoid using this method unless it is truly necessary.

Remember before you embark it’s essential to do your homework and find out as much as you can about your application and how it’s used in your organisation. Be prepared to ask questions and learn about database administration and good luck.  You will need it.

Anethea Ulvestad is a Records Management Compliance Specialist at South Australian Water Corporation.This article is based on a presentation originaly given at the Records and Information Management Professionals Australasia inForum 2014 conference, Adelaide, September 2014.