My company uses an ERP system that is totally wrapped around a SQL Database. All functions are tied to stored procedures and such which makes the database not only an information store, but a fully dynamic processing engine.
The problem is that there is no "archive" strategy for taking data after a certian time period and moving it to a separate database where it becomes archived. All data stays in the database forever which after time causes performance degradation.
What I am trying to do is create a method of copying the database structure (which is easily done by copying the database and simply clearing the data in the tables) and storing any data that exceeds 18 months for example in the archive database. I'll also need to link those databases in such a way that if a report is ran that includes any data outside of the 18 month active period, the data will pull from both databases.
I'll need the time frame to be a configurable option of course... but what are some thoughts on this? All of the reports that come with the system are crystal reports and any custom reporting also is done in crystal. I'd like it if I didn't have to customize all of the reports to make this happen... is there a way to create the database or set things up where this happens automatically?
I've pretty much decided (and feel free to correct me) that the movement of the data to the archive DB instance could be fairly simple since the two database structures will be identical. Just create queries that loop through each table and inserts any new archivable data into the corresponding table in the archive DB. It's the automatic cross linking that I really am not sure about...
I can always setup the archive function as a maintenance task that occurs monthly looking for any data older than whatever time frame I eventually end up deciding on.
All ideas are greatly appreciated :)
Start Free Trial
