July 19, 2006 at 6:58 pm
Good day
I have been tasked with coming up with ideas / thoughts / strategies for archiving data out of our databases. I am not sure that I am the best person for this task, as I am not the one that knows the business rules or the data. One of the questions that I posed is what is the data retention policy? What other questions might I need to ask?
More importantly, what are some of the strategies you all are using? I have worked in places that had one very large transactional table, about 30 million rows a month, and each month a new table space and table was created, and the oldest table space and table (once 2 years of data was built up) was deleted. I do not know much about the data inside the databases, as they were built before my time and by the engineers (who also still own that process), I do know about the database options and backups and other administrative tasks.
As far as I can tell, we have a number of transactional type databases, I suspect that a similar setup as described above could be used, but we do not do near that sort of volume, more like 100k a month could be the high end in the foreseeable future. Perhaps, more of an annual or quarterly set up would be more advantageous.
Okay, I know I rambled on, and this did not flow well, however, I hope you understand my question. If not, ask questions and I will get right back to you!
Thanks in Advance
Cory
-- Cory
July 20, 2006 at 9:02 am
Well, having been down this route a number of times I can make one essential suggestion - keep it simple!
I always go for table partitioning as first point. If most of your data is in a couple of tables then partition them. This is where I start, the older partitioned tables can be removed either to another database or altogther ( as they will have been part of the backups )
I have created a copy of the database before and just moved essential data to be archived into it every night - as access was only through stored procedures it was easy to implement logic to search in the archive database if data was not found in the main. This approach kept the size of the production database reasonably static whilst the archive database grew.
I've used partitioned views to great success in oltp system for very fast growing tables.
Essentially don't over engineer your solution. You can also keep archived ( partitioned data ) on seperate filegroups in 2005 - backup the filegroups seperately. 2005 has native table partitioning but I haven't used this in a production system yet. The archived data could be federated thus spanning servers - depends what you need to achieve.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply