Professional SQL Server™ 2005 Programming

24.4. Archiving of Data

Ooh — here's a tricky one. There are as many ways of archiving data as there are database engineers. If you're building a OLAP database — for example, to utilize with Analysis Services — then that will often address what you need to know as far as archiving for long-term reporting goes. Regardless of how you're making sure the data you need long-term is available, there will likely come a day when you need to deal with the issue of your data becoming simply too voluminous for your system to perform well.

As I said, there are just too many ways to go about archiving because every database is a little bit different. The key is to think about archiving needs at the time that you create your database. Realize that, as you start to delete records, you're going to be hitting referential integrity constraints and/or orphaning records — design in a logical path to delete or move records at archive time. Here are some things to think about as you write your archive scripts:

• If you already have the data in an OLAP database, then you probably don't need to worry about saving it anywhere else — talk to your boss and your attorney on that one.

• How often is the data really used? Is it worth keeping? Human beings are natural born pack rats in a larger size. Simply put, we hate giving things up — that includes our data. If you're only worried about legal requirements, think about just saving a copy of never or rarely used data to tape (I'd suggest multiple backups for archive ...