What is the best strategy for retainment of large data sets?

Question

I'm leading a project where we'll be recording metrics data. I'd like to retain the data for years. However, I'd also like to keep the primary table from becoming bloated with data that, while necessary for long term trending, isn't required for short term reporting.

What is the best strategy for handling this situation? Simply archive the old data to another table? Or "roll it up" via some consolidation of the data itself (and then store it off to a different table)? Or something else entirely?

Thanks.

Additional info: sorry, yes, we are using SQL Server 2005.

Answer 1

Either of those options are excellent, but it really depends on the problem domain. For things like cash balances or statistical data, I think that rolling up records and consolidating them is the best way, you can then move the rolled up records into a parallel archive table, keying them in such a way that you can "unroll" if necessary. This keeps your primary data table clean and quick, but allows you to retain the extra data for auditing or whatever. The key question is, how do you implement the "roll-up" process. Either automatically, via a trigger or server side process, or by user intervention at the application level?

Answer 2

We use both methods at my work, but slightly different, we keep all sales data in the primary table for 30 days, then at night (part of the nightly jobs) the days sales are rolled up into summaries (n qty of x product sold today ect) in a separate table for reporting reasons, and sales over 30 days are archived into a different database, then once a year (we go on tax years) a new archive database is started. not exactly perfect but..

this way we get the summaries data fast, keep all current sales data at hand and have an unlimited space for the detailed archive data. we did try keeping it all in one database (in different tables) but the file size of the database (interbase) would grow so large that it would drag the system down.

the only real problem we have is accessing detailed data that spans several database, as connecting and disconnecting is slow, and analysis has to be done in code rather than sql

Answer 3

If you are using SQL server 2005, this may be a good candidate for using partitioned tables.

Answer 4

Don't discount keeping your archive in a plain old text file.

@scottmarlowe
How big is large? Several gigabytes or terabytes?

@Peter

Data warehouse can be prohibitively expensive and could impact the operational needs of the current database. Looping over a text file in any language is cheap. Text files are also durable, I've heard plenty of horror stories where a database became corrupted and backups where bad. A simple text file needs to get kicked pretty hard to become 100% unrecoverable.

BTW I agree Analysis services is a great product and if your already running SQL Server you already own it.

Answer 5

@Jason - I don't see how keeping data in plain old text files will allow you to do long term trending analysis easily on the data.

@Jason - I guess my point is that if any sort of ad-hoc analysis (i.e. trending) needs to be done on the data by business people, rolling up or archiving the data to text files really doesn't solve any problems. Of course writing code to consume a text file is easy in many languages, but that problem has been solved. Also, I would argue that today's RDBMS's are all extremely durable when setup and maintained properly. If they weren't why would you run a business on top of one (let alone archive data to it)? I just don't see the point of archiving to a plain text file because of the claim that durability of text files is superior to that of databases.

Answer 6

Depending on constraints like budget, etc, this sound like a perfect candidate for a data warehouse application. This would typically introduce a new server for use as a data warehouse. SQL Server 2005 supports a lot of this activity out of the box, further you might be able to utilize additional SQL Server services (e.g. Analysis Services, Reporting Services) to provide additional value to your users. (see http://www.microsoft.com/technet/prodtechnol/sql/2005/dwsqlsy.mspx)

Answer 7

@Jason I anticipate in the gigabytes for the more granular data, but then obviously smaller for the archives if we only saved off summary data.

Thanks for the clarification on the text file idea.