Archiving large amounts of old data in SQL Server

Question

Pretty simple question.

I have a large 70gb database in which there are four of five tables, each of which contains about 50 million rows. These tables contain data for 6 years. We are limited by the amount of space in our database to 80 GB, and we will quickly come close to this over the next 6 months or so.

We only need to store data for two years in a database. What is the best approach for archiving old data without using a standalone database (24/7 database)?

We run SQL Server 2008 R2 Standard in a clustered environment using active passive configuration using shared storage.

Thanks.

Answer 1

When you say archive, it means "you need to save data for later use."

Also, if data is no longer available, then you risk losing it if you rely on tape backups. In addition, you need to have disk space to recover it in the future.

Of course, these are not insurmountable problems, but if something is not critical, I would save the data online until the opposite is proved. My experience shows that people want to archive data when you least expect it ...

One option (question marked as SQL Server 2008) is data compression . You can compress archive tables, for example, on the Internet.

To create an archive table.

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

Or use split 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 / section too