Apart from the obvious ones such crippling performance and running out of disk space, companies report such problems as: entire system outages as the database requires too much processing; backup failures as there is too much data to backup in the available window; and transactions timing out as they have to search through millions of records.
The solution to this list of woes is to archive old or non-accessed database records at regular intervals. This leaves production systems trim and responsive.
Based on interviews with end users, vendors and analysts, here is a summary of best practices in database archiving:
1. Achieve corporate and end user buy in early in the process.
"We successfully embarked upon a pilot archiving project to solicit buy-in for corporate wide international adoption," said Lois Hughes, senior business systems analyst of Tektronix Inc., a test measurement and monitoring business headquartered in Beaverton, OR. Tektronix utilized LiveArchive by OuterBay Technologies Inc. of Campbell, CA, to archive large amounts of data in its Oracle database.
2. Adopt a business process and legal view of archiving, not a technology approach.
3. Conduct a business process evaluation to determine the core impact on business processes, as well as the interdependencies that exist among data.
"If you archive just delivery notes and not the linked sales order, for example, the online sales order will appear as though there is no delivery note associated with it," said Jochen Hager, Vice President of Professional Services at database archiving vendor IXOS Software AG of Grasbrunn, Germany.
What to Archive:
4. Identify inactive business transactions in the database. Separate data into transaction categories, each with their own predefined archiving constraints
5. "Decide how much data you need to keep in production for each application and business unit," said Luca Cotrone, a systems analyst at Southwest Gas Corp. of Las Vegas. "Also, some areas such as inventory can have archiving done monthly, whereas HR/Payroll may need to have annual archiving as they typically need to access data for the entire year."
Southwest Gas implemented Applimation Archiver from Applimation Inc. of New York to archive its Oracle database.
6. Don't archive anything unless it is a closed transaction.
"Even very old data, if it is still an open transaction, should be left in the production database," said Michael Howard, CEO of OuterBay.
7. Set a data retention policy that is tailored to each country. Integrate these data retention policies for each country into one archiving system.