During this series of “Architecting a Database Archiving Solution”, we discussed the Anatomy of A Database Archiving Solution and End User Access Requirements. In this post we will review the archive repository options at a very high level. Each option has its pros and cons and needs to be evaluated in more detail to determine which will be the best fit for your situation.
Archiving to a separate tablespace or schema within the production database
This option is best when performance of accessing production data and archive data is a priority. Data is relocated as is, from source schema to target schema in the same database instance. Most solutions in the market offer a way to join data in both the production and archive database – when using this configuration, joins are accomplished within the same database and performance is optimized. The downside to using this approach is that the archive data still resides in the same database instance. If full clones of production are made for test and development, you will not realize the full potential storage savings archiving offers unless the cloning process is changed to only copy production tablespaces and excludes the archive data.
Archiving to a separate database
This option seems to be the most popular because the storage savings can be significant, end user access is not impacted, and data is moved from source to target retaining its structure and format. Data is still accessible via the native application. The downside is that joins between production data and archive data is typically done using a database link (dblink). This can introduce performance issues for users who want to access archive data. If the access to the archive data is less frequent and the performance is not a priority for this set of aged, inactive data, this is going to be a good option.
Archiving to a file – XML or compressed ODBC accessible archive file
Archiving out of the database to a file involves not only copying transactional data but also master data that makes the archive a complete encapsulation of the original business record. As a result, users would typically not access this archive format from the native application – rather they would use a query or reporting tool with X-query or ODBC/JDBC connectivity – which may be considered a downside if end users want to access data from the original application. Access may be occasional to less frequent, access performance is not a priority nor is expected. When using a compressed file, the storage savings will be the most.
When compliance is a key driver for choosing a database archive solution, the retention requirements are typically based on legal and regulatory statutes that a company must adhere to. In one example, such as the case of SEC rule 17-a4, specific financial transaction data must be archived to Write-Once, Read Many (WORM) type media. With database to database archiving solutions, storing archive data in an active Oracle database does not entirely meet the SEC requirements because open Oracle data files cannot effectively be stored on WORM media. In this case, consider leveraging an archive to file option.
In another example, retiring legacy applications allows IT organizations to drastically reduce costs associated with keeping outdated systems online. However, if data from those aged systems has longer retention periods, retiring the original application introduces a different challenge. How would users access the data while maintaining the original application context – maintaining context is required for eDiscovery. Archiving legacy data to compressed archive data files that can be queried from a standard reporting tool enable IT organizations to realize the cost savings by retiring ancient applications while maintaining appropriate levels of access to the data. Because reports can be recreated using standard, available technology, IT organizations can standardize on an archive technology across varying legacy applications.
One question I ask of my clients – do you really need this data and if not, can we just delete it? It’s a low cost option and leaves room in the database for future growth. It’s not for all data, but you should at least ask the question of the business and legal.
Our next and final part in this series will review the final aspect of architecting a solution – getting started with a Cost Benefit Analysis.
Julie Lockner, Founder, www.CentricInfo.com