Reduce the cost of compliance: database archiving and Information Lifecycle Management.

Database growth and storage issues are a top priority for large and small enterprises worldwide. Companies across industries rely on large, complex relational databases to support mission-critical applications. According to META Group, relational databases are growing at a rate of 125% annually. Compounding these challenges, data is stored across a variety of different database management systems, operating systems, hardware platforms and storage systems. These heterogeneous environments provide open, selective or restrictive access to application data for hundreds or thousands of users.

Experience shows that larger databases negatively impact application performance and take longer to load, unload, search, reorganize and recover. Response time can slow to the point where maintaining service levels can become nearly impossible. Batch processing windows expand, processing times overlap, and the start of routine operations is often delayed.

So, what are the alternatives? Database tuning? Expensive capacity upgrades? More storage? These options may abate the problems in the short term, but only offer diminishing returns because they only address the symptoms, while databases continue to grow. Data retention requirements and the need to manage and store information for longer periods are resulting in increased operational costs. Companies want to reduce IT costs and gain a greater return on their data management and storage investment. That is why Information Lifecycle Management (ILM) is considered an overall "best practice" approach, and database archiving is an essential component of ILM for managing database growth over the long term.

What is Active Archiving?

Unique to database archiving, active archiving is a proven strategy that allows organizations to archive rarely accessed data from application databases and manage the data efficiently, while providing easy access to data on demand. Archived data, business context data and metadata are saved to an Archive File (referentially intact and complete) and these files can be easily stored on the most convenient and cost-effective storage medium. Once archived, the data can be safely removed from application databases, improving the performance and availability of critical systems immediately. However, when needed, the archived data can be easily accessed, researched and selectively restored.

By separating essential data from nonessential data, many organizations can safely reduce the size of overloaded databases by up to 50% or more during the initial archive. Ongoing active archiving (daily, weekly or monthly) enables organizations to maintain mission-critical databases at a size that fits comfortably within the desired processing and disaster recovery windows. Routine backup and database maintenance processes take much less time. Mission-critical data is available at peak performance.

Most importantly, active archiving ensures data quality and referential integrity. Active archiving understands and remembers data relationships with 100% accuracy and saves not only the data, but also the metadata describing the tables, columns and relationships used to create the Archive File. Users continue to have real-time access to archived data using comprehensive search, browse and reporting capabilities without restoring a single row.

[FIGURE 1 OMITTED]

With active archiving, users no longer need to restore all archived data for the sake of restoring a small subset of the data. Archived data can be restored selectively by specifying the criteria that identifies the data to be restored, even if it differs from the original criteria used to archive the data. Active archiving also ensures that data can be restored to a new or existing database, even if the data model has changed over time. So, how does active archiving complement ILM and reduce the cost of compliance?

Managing the Information Lifecycle

All information has a lifecycle, from acquisition through disposal (see Figure 1). During this period, the data access requirements range from heavily accessed to rarely accessed. At each stage in the lifecycle, the business value of the information, as well as the access rates and performance requirements will vary. In general, for any given type of information, you should be able to compare the business value against the cost to store and access it in a timely manner.

You can reduce the storage and operational costs associated with data retention, and reduce the cost of compliance, by matching the storage medium to the access and performance requirements corresponding to the business value of the data. In other words, to achieve an optimal return on investment, you should manage and store data in a way that matches its business value.

Basically, data is most valuable once it is created, or soon thereafter. As data ages, the relative value and the corresponding access rates decrease. There comes a point of diminishing returns where the cost to provide optimal production-level access and performance outweighs the actual business value derived from the data. At this point, it makes sense to move your data out of the high-cost, fast response system and into lower-cost, slower response systems to better match its business value. Although ILM is a simple concept, implementing ILM can be highly challenging, especially for managing complex relational data. Database archiving is the critical component within any ILM framework that enables companies to manage relational data according to its business value.

For example, as shown in Figure 2, while storing the initial two years of current financial data in the production database, you can still provide optimal performance for financial processing and yearly comparison reporting. To maintain performance levels, data that is two to five years old can be moved into an archive database that provides full data access when needed.

Data that is five to seven years old can be saved to an Archive File to take advantage of more cost-effective storage options. If you need access to data stored in this online archive, you can still access the data directly, and/or selectively restore some of the data to the archive database. Finally, for the older financial data that must be saved for a decade or longer, the capability to store these Archive Files on an offline archive tape further reduces storage costs.

Of course, this entire information lifecycle approach could easily be adjusted to meet your company's unique requirements. It is important to remember that every company values information differently; make sure you understand how your information is valued and spend as much, and only as much, as necessary to provide the appropriate level of access.

Selecting Database Archiving Technology

If you decide to research vendor offerings to purchase archiving technology and software, knowing the appropriate questions to ask is critical to selecting the ideal archiving solution for your environment. The ideal database archiving solution must meet several critical requirements:

* Provide out-of-the-box archiving capabilities to ensure a fast and easy implementation

* Preserve the business context and referential integrity of the data

* Provide predefined archive templates that can be easily customized to meet your database archiving requirements

* Reduce downtime during application upgrades and ensure access to the archived data after the upgrade is completed

[FIGURE 2 OMITTED]

* Allow business users to access archived data easily using standard forms and reports

* Offer intuitive or integrated functionality that is easy to learn and use without extensive training

* Address data retention regulations and audit requirements

* Support information lifecycle and storage management strategies

* Enable your company to defer or eliminate future hardware expenditures to accommodate data growth

* Provide proven technology that has been in operation at customer sites around the world for several years.

Putting Database Archiving Technology to the Test

In general, to obtain an accurate assessment in the shortest period of time, a Proof of Concept that focuses on a particular application can really put the database archiving solution to the test. For that reason, the Proof of Concept design should verify the application and database issues that are of greatest concern. The success criteria for the Proof of Concept must be thoughtfully defined to verify that specific capabilities satisfy the application archiving and retrieval requirements. Familiarity with the application functionality and the database architecture is critical for success. At a minimum, the Proof of Concept should demonstrate capabilities that:

* Archive precise subsets of related data and store archived data on the most appropriate and cost-effective storage medium

* Accurately remove precise subsets of related data from the application database based on their specifications, leaving all other data referentially intact

* Locate and browse a specific instance of a set of related data in an Archive File quickly and easily

* Selectively restore data to a separate archive database, mirrored archive tables or back into the production database environment, even if the data model has changed

* Provide business users with easy access to archived data upon request

* Demonstrate that basic application functions are unaffected by removing the historical data. In some cases, it might be necessary to make adjustments, either in the application or in the business processes, to resolve any effects of removing specific data

* Once the Proof of Concept success criteria are defined, all that remains is developing a Proof of Concept verification plan and setting up the test environment. In most cases, this effort takes just a few days.

Benefits of Active Archiving

Upon completing the Proof of Concept, the results will clearly indicate whether active archiving meets all the criteria and provides needed capabilities. Some expected benefits include the ability to:

* Archive almost 50% of the application database, resulting in shorter batch processing and maintenance windows, and significant improvements in performance and availability

* Store archived data to a near-line system and store some data in a separate archive database. This approach allows for faster access to archived data when needed and reduces the impact on the online system when accessing historical data in the production environment

* Store archived historical data on the most cost-effective and appropriate medium (online in an archive database, near-line on a file server, on optical devices, offline to tape, WORM devices, such as EMC Centera or NetApp's SnapLock) and enhance the value of SAN, NAS, HSM and other storage strategies

* Defer or eliminate the need to spend millions of dollars for the additional high-end processing units and reduce costs.

Selecting a proven database archiving solution eliminates the costs of developing, testing and maintaining an in-house solution, a much more complex and costly project. The Proof of Concept provides an effective way to ensure that the archiving software can perform the tasks required for a specific database environment. Once the application success criteria are defined, this "show me" technique puts the selected software to the test, conclusively proving the value of archiving to the organization.

The benefits of active archiving are clear. Removing rarely accessed historical data from application databases improves the performance and availability and can significantly reduce the costs associated with additional capacity upgrades. Most importantly, active archiving works within the framework of existing storage strategies to offer a value added proposition. Having the flexibility to store data cost effectively on the most appropriate medium offers advantages, and selecting a storage medium based on the types of data to be stored makes it easier to comply with data retention requirements throughout the information lifecycle.

Jim Lee is vice president, product marketing, at Princeton Softech (Princeton, NJ)

www.princetonsoftech.com