The Smart Historical Database
Archiving with the End User in Mind
Posted on 12/16/2006 (originally published in print on 7/1/2005)
by Matt Tomlinson
Your organization has been live on PeopleSoft for 7 years. You have been through two major enterprise upgrades. You have an experienced PeopleSoft support team and user base. But, your database is over 100GB now. Backup times are too long, refresh times are too long, and queries/reports/batch processes can all perform better.
The easy conclusion to draw is that it is time to archive some of that data to reduce the size of the database. Archiving data is different than purging data. Purged data does not need to be viewed again and can be thrown away. Archived data is moved out of the current transactional tables, but it must still be retrievable. Reducing the size of the database through archiving will provide many benefits that translate into cost savings:
quicker backup times
quicker refresh times
reduced disk requirements
faster queries and reports
faster batch processes
steady-state performance
But, the hard part is determining how best to archive your data. Functional business requirements dictate a lot of the solution. How long must the data be retained for? How will users access the data? Most solutions focus on keeping the archive solution simple from a technical perspective. The focus is not on the end user and how to keep the solution simple for the end user both today and years from now.
Some solutions rely on moving data to historical tables. This is a simple approach, but how will end users access this historical data? All of your current components, queries and reports access the delivered tables, not these historical tables. Other solutions rely on moving the data to flat files or other formats dissimilar to your current RDBMS platform. Neither of these solutions address how users will retrieve the archived data. Also, what happens two years from now when your next Enterprise upgrade takes place? The data in historical tables and flat files is not upgrade-able.
To continue reading this article you must have a current VP1 Subscription.
