Archiving as a Cure for Database Crashes

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

Letting a database grow without action can lead to disaster. Transactional performance can suffer to the point where people are screaming about access rates or their queries timing out. And if you do run out of disk space, your whole system could crash.

“Our overweight database was months away from crashing due to exceeding our production disk space capacity,” said Larry Cuda, global data archiving and migration project leader at Kennametal Inc. “Management determined that we could no longer just keep throwing more disks at the problem.”

“If you don’t archive, and have an overweight database, transactions that used to take one second can take you six seconds.” – Larry Cuda, global data archiving and migration project leader, Kennametal Inc.

What happens is that databases become loaded up with inactive records. As system bloat continues, transactions and queries take longer and longer.

It can get to the point where you enter a query, go for a coffee, return to your desk and the application is still churning. One obvious solution is database archiving: you move all inactive records to another platform and leave only current and recent traffic in the database.

Paring Down the Database

Kennametal Inc. is a large metal cutting tool company with 13,500 employees based in Latrobe, PA. The company has an HP UX 64-bit environment for a series of SAP ERP applications (sales and distribution, materials management, production and planning, financial and controlling) as well as its Oracle 8.1 database. Its SAP database, already well over 2 TB, was swelling at a rate of 27 GB per month. And that began to cause serious issues.

“If you don’t archive, and have an overweight database, transactions that used to take one second can take you six seconds,” said Cuda. “You are also vulnerable to time outs or even system outages as a large database requires too much processing to read through millions of inactive records.”

Further, this can also put the company at risk during backup. The bigger the size of the database, the more time it takes to backup/restore. During those activities, you are vulnerable to an outage or a power surge that can ruin the data. If the backup wasn’t completed, you could have lost a whole day’s transactions, for example.

Kennametal pared their database down to the essentials using a database archiving tool called eCONtext by IXOS Software AG (Germany). Kennametal chose this system for its archiving as well as its optical imaging capabilities. The company actually takes an optical image of invoices and delivery notes at their point of creation. This amounts to 80 million documents that are optically imaged onto WORM CDs to satisfy country-specific legal requirements. When the system was first implemented, it took seven months round the clock to take an image of everything.

“This is a cost avoidance mechanism as you don’t need to keep these documents on paper,” said Cuda.

Continued on Page 2.

Continued from Page 1.

This optical archive, though, is separate from the database archive. A torrent of daily transactions hits the SAP system. These documents are retired from the production database and archived at regular intervals depending on the type of data and legal requirements.

Deliveries, for example, are archived after an 18-month retention period. The system is configured to detect such files on a weekly basis and send them to the archive. Financials, on the other hand, are not automated. Cuda notes that the year-end close out happens at different times and it is wise to wait for financial OK before doing any year-end archiving.

Archiving Complexity

When the project was completed, the results were significant. Cuda reports transactions that used to take six seconds now take one second. The company saved an estimated $700,000 annually in terms of avoided hardware acquisition costs alone. The database is maintained at 2 TB, with another TB residing in rapid access archives.

This happy ending, however, was no overnight success. Cuda admits to considerable struggles during the project. These only vanished once he made some major changes to project management. Initially, he took a technology view if archiving, thinking if he could just find the right tool, all would be well. When this approach ran aground badly, he realized it needed a whole different methodology in terms of understanding the business and legal side thoroughly and THEN finding technology or adapting technology to meet those needs.

As part of the process, he managed to eliminate much of the implementation complexity by laboriously plotting out all 223 data objects within his SAP database. This showed him the dependencies that existed among data types and highlighted exactly how to retire things so as to minimize risk. Thus policies could be correctly set for archive automation.

For example, invoices should not be archived until the corresponding shipping and delivery documentation denoted a closed transaction.

“SAP has mechanism’s built in that prevent retirement of open transactions,” said Cuda. “So it makes good sense to use diagrams to map out data interdependencies and plot the optimum archiving path.”

Cuda advises others to opt for the easy pickings in any archiving project rather than a big bang. Financial documents, for example, often have no dependencies. By finding these low- or no-dependency records, you can implement the archiving of a segment of the database without wreaking havoc across the system.

“Attacking such low-hanging fruit not only gives you significant data recovery,” advised Cuda, “it also gives your team a sense of victory, and highlights to management and users that archiving is beneficial to the system.”