February 21, 2011 at 9:23 pm
Comments posted to this topic are about the item Archiving Little Used Data
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 22, 2011 at 2:35 am
Typically, we've relied on the profiler for this. It isn't the greatest, but if nothing shows up there over some time, we can consider renaming the table or making it otherwise inaccessible (with permissions) with an obviously simpler path to restoring access. Then archiving after some time. Some things may only be used once a year, and that's often far too long a timescale to consider catching with the profiler.
February 22, 2011 at 7:49 am
There actually are methods to capture object access events for the purpose of maintaining usage data. We could do it with a SQL Profiler trace, filter on the appropriate events, and saving output to a table or file. Also, in SQL Server 2008 there is a security audit feature more specific for this purpose.
http://searchsqlserver.techtarget.com/tip/Implementing-security-audit-in-SQL-Server-2008
But really when we talk about archiving old or rarely used data, we're generally talking about datasets (a range of rows) and not actual tables, at least in an operational or OLTP database. In a datawarehouse or some other scenario involving partitioned views, we may perhaps be talking about wanting to archive rarely accessed tables.
Brent Ozar has done some articles and presentations on using SQL Server's table partioning frature and "sliding window" techniques to move individual partitions between filegroups, which could mean moving older data (keyed on something like datetime) to less expensive SATA drives.
http://www.slideshare.net/BrentOzar/files-filegroups-and-partitions
http://msdn.microsoft.com/en-us/library/aa964122(v=sql.90).aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 22, 2011 at 8:18 am
For long term archiving, you have to be concerned with the media and the format of the data. I was involved in a project a couple of years ago that converted old Paradox files being stored on obsolete Iomega zip media.
February 22, 2011 at 8:32 am
Media/format is a big deal. I didn't cover it, but I've tried to set archive files as CSV so that they can easily be converted later.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 22, 2011 at 8:35 am
Perhaps the best option is to keep the data online and in the database, but on a seperate read-only filegroups located on cheap drives. Those filegroups don't even need to be included in the regular backup plans. That way the data is still cataloged and accessible if needed while not requiring the same level of resources or adminintration as the more recent operational data. Another problem with taking archived data offline, that is backing it to tape and deleting it, is that the business and DBA lose track of what's been archived. For example, business requests the restore of a backup, so they can query data on an old account. However, once done they discover it's not there and probably located on a different and even older backup, but which backup?
It's best not to go there.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply