Archiving as a Cure for Database Crashes

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.”

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 is a large metal cutting tool company with 13,500 employees based
in Latrobe, Penn. 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.”

This can also put the company at risk during backup. The bigger the size of
the database, the more time it takes to backup and 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 around 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.

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 of 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 only 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 mechanisms 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.”

Article courtesy of Enterprise
IT Planet