Database Archiving Versus Backup:
Complementary Best Practices

Source: Computerworld Storage Networking World Online by Jim Lee

Database Archiving Versus Backup: Complementary Best Practices

Comprehensive ERP, CRM and other mission-critical applications collect enterprise business data that must be managed for long periods of time and protected from loss. However, maintaining all this data online in production databases can limit the very processes needed to optimize performance, conduct routine maintenance and ensure data recovery in the event of a disaster.

For these reasons, companies across industries are recognizing the value of database archiving as a best practice for managing their application data. IT departments have implemented database archiving to maintain optimal application performance while keeping historical data intact and accessible for compliance purposes. However, there still remains a notion that IT managers do not need to archive if they are performing regular database backups.

Database archiving and backup both provide value to enterprises by preserving and protecting data. Each technique, however, serves a very different purpose. Database backup should not be considered a replacement for database archiving, and vice versa. Utilized in tandem, these two best practices complement each other to improve IT processes.

What is the difference between backup processing and archiving?
The purpose of backup processing is to ensure that the most recent copy of operational data is available for recovery in the event of a system failure, accidental file deletion or disaster. Backup processing takes a "snapshot" and/or "delta" of current operational data from production systems and copies it to the appropriate backup storage media. Typically, companies use some combination of full and incremental backups. A full backup copies the entire database, while incremental backups copy only the data that has changed since the last backup.

Depending on the type of backup, backup processing is conducted on a frequent schedule that assigns a rolling window or short-term retention period (daily, weekly, monthly) for saving backup data. Once the period is up, the backup data is overwritten and the backup media can be reused.

For example, a company may perform full backups weekly and incremental backups on a daily basis. It also maintains four different storage media for daily backups and copies a week's worth of data at a time. Based on the rotation cycle, the storage medium for full backups is overwritten every three months, while each storage medium for the daily backups is overwritten every four weeks.

Figure 1. An example of a rolling backup window for daily backups. The backup is kept for four weeks and then the storage medium is overwritten.

In contrast to backup processing, the purpose of database archiving is to optimize data management strategy, while maintaining data access, complying with data retention regulations and reducing costs. Users must be able to view and report on both current and historical data whenever needed. However, it is not cost effective to keep years of rarely accessed data online in production databases.

Database archiving archives and removes historical data from the production database. When the production database is streamlined, only current, business-critical information remains in the online production environment, which significantly improves application performance and availability. Archive processing not only captures the historical data based on business criteria, but also captures the metadata that describes tables, columns and relationships.

As a result, the referential integrity and business context of both the archived and production data is preserved. Business users set the functional policies for archiving historical data based on departmental requirements. The functional policies consider the type of data, age and transaction status. In most implementations, archived data is migrated to a secure, high-performance archive file, which is then stored on the most cost-effective and appropriate storage media for the long-term to satisfy retention, access and security requirements.

Whether stored online on a file server, tape or disk-based WORM device, archived data remains easily accessible. Users can browse and report on the data to satisfy a customer inquiry or audit request. For compliance purposes or to perform additional business processing, users can restore any portion of the archived data to an application environment.
Figure 2. Database archiving keeps archived data easily accessible and allows you to restore specific subsets for audit or compliance inquiries.

Why not use backup and purge processing in place of archiving?
Anyone considering database backups as a functional replacement for database archiving should review the objectives of each technique to ensure that they select the appropriate process for addressing their needs. Some IT departments back up their historical data for future retrieval and then purge that data from the production database. Whenever they need to retrieve this historical data, however, they must first locate it and then reconstruct the backup. Without indexes or applications to search for the specific data, this effort is no small task.

Rebuilding from backup media requires significant amounts of time and money. In some cases, it may be almost impossible to find and retrieve the exact data needed for a compliance request in a timely manner. In addition, users who lack the technical ability and time to retrieve specific information from backup will rely on the IT department to obtain the data they need each and every time. However, with a database archiving solution, users will have the ability to search and access archived data on demand.

How do database archiving and backup complement each other?
Both database archiving and database backup are vital components of best practices that are essential to a company's day-to-day operations.

Ongoing database archiving allows users to manage and maintain historical data for reporting and compliance purposes. And keeping databases at a manageable size allows them to maintain the level of performance and availability that business users and customers have come to expect. Consistent backup processing ensures that in the event of a disaster or inadvertent data loss, operational data can be recovered quickly from the last backup point.

Successful recovery depends on how often and how well backup procedures are performed. When implemented efficiently, these two procedures can also complement each other. Implementing routine database archiving can significantly reduce backup and recovery times. For example, the IT department at an information services provider was able to reduce database size by 40 percent after implementing database archiving. Because only the current operational data is contained in the database, the IT department saves an estimated four hours each week on database backups.

More significantly, they save an equivalent amount of time on recovery, allowing the company to be operational in a much shorter time frame. The IT department also takes care to backup archived data, which can be recovered on an as needed basis in the event of a disaster.

What is the best strategy for implementing backup and database archiving? By archiving and removing rarely accessed data, users ensure that only current, operational data remains in the production database. In addition, database backup and routine database maintenance tasks take less time. Consider the following guidelines for employing the most efficient database archiving and backup procedures:

• Define the database backup schedule (including full and incremental backups) appropriately to provide the necessary resources for recovery.
• Define the data to archive based on business policies. Schedule routine archive processing as needed to keep databases at a manageable size.
• Select the appropriate storage media for archiving based on the business value and access requirements for each type of data.
  Update the backup process to include archived data.
  Select the appropriate low-cost storage media for database backups to ensure recovery and reuse.

Together, database archiving and backup processing allow companies to meet the demands of their application users. Application performance improves, which expedites transaction processing. Both current and historical data remain available for reporting, auditing and compliance inquiries. Because databases contain only current operational data, backup processing is completed in less time. And in the event of a disaster, the most recent backup is quickly recovered to the production database.