How to efficiently archive older parts of a big (multi-GB) SQL Server database?

Question

Right now I am working on a solution to archive older data from a big working database to a separate archive database with the same schema. I move the data using SQL scripts and SQL Server Management Objects (SMO) from a .Net executable written in C#.

The archived data should still be accessible and even (occassionally) changeable, we just want it out of the way to keep the working database lean and fast.

Hurling large portions of data around and managing the relations between tables has proven to be quite a challenge.

I wonder if there is a better way to archive data with SQL Server.

Any ideas?

Answer 1

I think if you still want/need the data to be accessible, then partitioning some of your biggest or most-used tables could be an option.