Archiving Little Used Data

Question

Archiving Little Used Data

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
February 21, 2011 at 9:23 pm

#157455

Comments posted to this topic are about the item Archiving Little Used Data

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Cade Roux Ten Centuries Points: 1346 More actions · Answer 1

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.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 2

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

OCTom SSChampion Points: 11785 More actions · Answer 3

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.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

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.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 5

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