Database Archiving Best Practices: Advice from the Trenches

Datamation content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Data is growing at a rate of 125 percent a year. And a laundry list of regulations such as Sarbanes-Oxley, SEC 17a and HIPAA make it necessary to hold onto just about all of it. Yet according to Meta Group (Stamford, CT), up to 80 percent of this data remains inactive in production systems, where it causes countless problems.

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:

Preliminary Steps:

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.

Page 2: How to Archive, Optimization and Cost Reduction

Continued from Page 1

How to Archive:

8. Store data in classes appropriate to the age of data and its access/legal requirements.

9. “Use diagrams to map out data interdependencies and plot the optimum archiving path,” said Larry Cuda, global data archiving and migration project leader at Kennametal Inc, a. metal cutting tool supplier based in Latrobe, PA. Kennametals SAP database almost crashed due to an explosion of data. eCONtext by IXOS helped solve the problem.

10. Relocate inactive data to the archive.

11. Strike while the iron is hot. “Archive before data volume creates a noticeable performance implications or necessitates unanticipated expenditures for storage, memory, etc.,” said Adam Gwosdof, CTO of Applimation.

12. Enforce data retention based on a published central retention document.

13. “Retain application transparency for users regardless of where the data resides,” said Howard.

14. Get your archiving methodology approved as legal in each country and by each agency concerned

15. Distinguish between archiving of original documents upon creation for legal/audit purposes and retirement of inactive data.

“We use optical imaging of certain documents at their point of creation,” said Cuda. “Later, we retire data from the production database.”

Optimization and Cost Reduction:

16. “Run the archive DB as a separate instance within one Oracle system, if possible, to reduce costs,” said Hughes.

17. “Optimize your archive by removing very old data to another XML/XSD archive stored in a flat file that is application and database independent and is less expensive to store,” said Howard.

18. “Make sure you backup your archive database after you archive, “said Cotrone.

19. Establish service level agreements with user community for access to active and inactive transactions.