FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:

FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!


Publisher reserves the right to serve qualified requesters only.

Active Archiving Helps Solve Application Performance Management Challenges

  • DM Direct, February 2007

One of the biggest challenges facing a corporate IT support team once an application "goes live" is the daunting task of maintaining application performance service level agreements (SLAs). As data volumes and number of users accessing the systems grow, performance can be adversely impacted.

Application performance can be affected by many factors, including: database size, server and infrastructure class, number of users accessing the application and the quality of the application code. Most application support teams, developers and database administrators will say that most of their time is spent reactively responding to performance issues as opposed to other support and maintenance issues.

Monitoring tools are widely available to help relieve the burden of performance management. The tools assist with a number of tasks, such as identifying performance bottlenecks and debugging poorly written code. But if the amount of data continues to grow and the infrastructure isn't upgraded to keep up with the volumes, performance will degrade continually.

Tuning the application will only get you so far. A costly, yet common way to improve performance is to upgrade the hardware infrastructure, such as upgrade to a faster disk subsystem, increase the number of CPUs or upgrade the class of server. However, these solutions are a temporary fix because the data volumes will continue to grow, as will the number of users accessing the system.

At the end of the day, no matter how much you "tune" the application, the performance will not improve in a predictable manner unless, as my father would say, "You take the bricks out of the trunk." In other words, by taking out the inactive and dormant data from the production system, you are taking the dead weight out of the production system. Moving it to an online active archive keeps the data available to end users. Reducing the size of the most heavily hit transaction tables, full table scans and reports will run significantly faster in the production system.

Maintain native application end user access to the archive data. There are a number of database archiving solutions available that provide the ability to relocate data while maintaining application access to the data. This means that even though the data is removed from the production database, it is still available to the application users. This is an important feature because it helps ensure compliance and end-user productivity. One of the main reasons end users choose not to implement data archive projects is because they fear that they will no longer have access to the data. With the products available in the market today, this is no longer an issue. Data is relocated to an online archive and completely accessible to the end users through the native application. This way, IT organizations realize the benefit from archiving while keeping the end users happy.

As an example, one well-known CIO who wanted to implement database archiving was receiving a lot of resistance from his end user community. He decided to start archiving older data to an online active archive. First he archived 10 percent of the data, and then, on a monthly basis, he would archive another 10 percent. The CIO was able to archive more than 60 percent of the data without issue - largely due to the fact that they were still able to access the data. In fact, during their peak season it was noticed that the performance improvements had a direct impact on reducing the number of performance-related trouble tickets submitted to the IT support desk.

Another example where database archiving improved application performance involves a recent customer implementation where their financials application was in production for almost five years. It was found that by relocating all general ledger data older than two years to an online active archive, the closing process improved by more than 50 percent. By establishing a recurring archive process one month after every quarter, the growth rate of the financials database was reduced so much that they were able to defer capital expenditures for more storage and additional CPUs for that application by more than two years.

Intelligent Data Partitioning

Archiving inactive data to an online archive is, in effect, partitioning the data using the application business logic to determine its status. Examples of policies used to determine the inactive state are a closed purchase order, shipped inventory or a closed booking period. The policy is defined by the business application and the line of business's implemented processes.

The policy definition and execution varies by software vender. Most use native database technologies, such as partitioning or transaction-based SQL to separate the data.

For database partitioning, most relational database technologies have built-in features to partition data in a table based on a column in the table. For example, if there is a date field, tables can be partitioned by date. For complicated applications, such as Oracle E-Business Suite, partitioning is not an option because the business logic in determining the inactive state may span across multiple tables and schemas. In this case, transaction-based SQL is needed.

Transaction-based SQL partitioning is used to select the inactive records and move them to another database or another tablespace within the same database. With transaction-based SQL, the complete application transaction is relocated in its entirety, ensuring relational integrity is maintained in the archive database. Some solutions deploy the use of database triggers to execute the transaction-based SQL - this can consume CPU resources. It is recommended to evaluate nontrigger-based solutions when considering transaction-based SQL.

Lower Total Cost of Ownership

Because the relocated data is, in most cases, considered "read-only," there are typically fewer users accessing the data. As a result, it may be more cost-effective to set up the archive database on a lower cost infrastructure. For example, the server could have fewer CPUs than production and the disk-subsystem could be network-attached storage, serial advanced technology attachment disk drives, or even RAID 5 (as opposed to service area network or RAID 0/1). We have seen customers implement the archive on an environment that costs 40 percent less per gigabyte than the production system.

By managing the data growth volumes and running archiving processes once a month or once a quarter, the size of the database is easier to maintain. The environment can be tuned and managed based on predictable growth rates. Application performance improves drastically by deploying this technique. In addition, because the inactive data is relocated out of the production environment, all copies of production are also smaller.

Most IT data centers create backup copies of their email servers and file shares. Unlike email servers and file shares, database applications also require IT data centers to create multiple copies of the database for patch testing, development, QA, training and disaster recovery.

The average number of copies of a database application range between five to 10 copies. By archiving data out of the production copy, all other copies are reduced. Most data centers will take daily incremental backups and weekly full backups of the production and a similar backup schedule for the copies. Most full backup copies are stored either for a period of time before the tapes are recycled, or they are stored indefinitely. When redundant data is relocated out of the production, it is also removed from all backup copies. Total disk and tape consumption can be reduced by as much as 50 percent in the first six months.

Offload Servers

Another method that works well in improving application performance is reports archiving. Reports archiving involves understanding the performance impact from batch and ad hoc reports and identifying which reports are burning up precious CPU.

When multiple users run the same report over and over again, one solution is to run the report once, archive it to a central archive repository (or a local file share if you haven't set up an archive repository) and provide an integrated search interface to the report. When users come to work in the morning, they simply search for their report and peruse the data instead of rerunning the report against the database or application server again.

By integrating a reports archiving solution with an integrated search solution, reports can be run once, archived, indexed, searched and viewed multiple times without burdening the application server. Users may find that searching and reviewing reports via an integrated search, such as Google, is a lot easier and quicker, thus improving productivity levels significantly. Most search engines also provide integrated access control, ensuring reports are only accessible by those with the proper authority.

For heavy ad hoc reporting, consider using one of the database copies as a report database. The enterprise resource planning or customer relationship management system is setup to process transactions - not run reports. Do whatever possible to offload reports from the production system.

Archiving data and offloading report generation and archiving reports can provide significant benefits - the most noticeable benefit is in performance improvements. When providing a solution that combines these two best practices, performance management becomes a more predictive and proactive process at a lower total cost of ownership. When evaluating archiving solutions, consider those with a single metadata repository and the ability to have a common archive. Reducing the number of archives improves manageability as well as makes it easier to retrieve data during an audit or e-discovery project.


If your application has been in production for more than a year and you are considering purchasing more hardware or faster servers, here are a few questions you should answer before making the investment:

  1. Where are the performance bottlenecks? Are any associated with the larger tables in the database?
  2. Have you already tuned the application? If so, are the performance improvements less effective as the data volumes grow?
  3. Are the longer running reports running full-table scans on the larger tables?
  4. Are there any consolidation projects on the horizon that will add data to these larger tables?
  5. Are there many reports run against the database that impact CPU utilization?
  6. Can these reports be run once and made available outside the application?

If your organization requires an ROI calculation before proceeding with any of these alternative solutions, seek the assistance of storage providers who can assist with ROI analysis. Most database and reports archiving providers have formulas that can help speed up the assessment process. Also, from a technology perspective, understand the vendor's features with respect to following:

  1. Does the product leverage a central archive?
  2. Is there a single metadata repository?
  3. Does the solution provide support for all data types (files, documents, reports, database data and attachments)?
  4. Does the solution have an integrated search capability?
  5. Does the solution require a specific storage and server platform, or can it leverage what exists in the data center?

When evaluating options for improving performance management, make sure data management is an integral component to the solution. Data and reports archiving offers significant benefits. Do your homework - one solution does not fit all - spend time to understand what the venders offer and how they can be used in your environment.

Julie Lockner is vice president of sales operations for Solix Technologies of Sunnyvale, California. She may be reached at

For more information on related topics, visit the following channels:

Vendor Showcase