Archiving large amounts of old data in SQL Server

Question

Pretty simple question.

I have a large 70gb database that has four of five tables that contain about 50 million rows each. These tables contain about 6 years worth of data. We are limited to amount of space in our database to 80gb, and we are going to be quickly approaching that in the next 6 months or so.

We only need to keep about two years worth of data in the live database. What is the best approach to archiving the older data WITHOUT taking the live database offline (it's 24/7 database)?

We are running SQL Server 2008 R2 Standard in a clustered environment using active-passive setup using shared storage.

Thanks.

Answer 1

When you say archive, this implies "need to keep data for later use".

Amongst other things, if the data is not left available now, then you run the risk of losing it if you rely on tape backups. Also, you need to have disk space to restore it in future.

These aren't insurmountable problems of course but unless things are critical, I'd keep the data on-line until proven otherwise. My experience shows that folk want archived data when you least expect...

One option (the question is tagged SQL Server 2008) is compression of data. You can compress archive tables that are online for example.

To create an archive table.

SELECT * INTO ANewArchiveTable
FROM CurrentTable
WHERE SomeDateColumn <= DATEADD(year, -2, GETDATE())

Or use partitioning to achieve the same

If the data is online you can reduce the "current" table further and have data older than, say, 3, months in a compressed archive table/partition too