Need advice on archiving large SQL Server tables

Question

Our SQL Server databases (SQL Server 2008 Standard edition, so to be R2) are getting bigger and bigger from years of data gets stored. So we are thinking of moving the old data (possibly older than a year) into an archive database. There are many reasons why we would benefit from having our production databases smaller.

This is how I plan to do that (using the Stock database as an example). For the Stock database, there will now be Stock and Stock_Archive databases. The StockResults table will be named StockResults_Current in the Stock database and StockResults_Archive in Stock_Archive. And in the Stock database there will be a view called StockResults that unions the two. At regular times, we'll archive our data (i.e. move it to the Stock_Archive database). That's the basic idea.

I have considered partitioning these tables by putting old data in a different filegroups, but I want to keep the main database small -- not just some of the data files.

Has anyone tried anything similar? Or have advice on trying to archive in this way? Or can recommend a better way?

I'm not too concerned about the performance of the archived data, but I don't want reading and writing of the current data to be affected. Tables like StockResults are used by our reports.

I'm planning on doing this for all transaction tables that store a lot of data.

Any advice would be much appreciated.

Answer 1

Sounds like a perfect data warehousing scenario. You could create tables that match your existing Stock tables with extra StartDate and EndDate fields in order to track your slowly changing dimensions. You could then also look into using SSIS packages in order to schedule your ETL.

See here for some into links - http://stackoverflow.com/questions/1109085/good-place-to-start-learning-data-warehousing

If you haven't got one already it would then be worth looking into putting a cube together as part of a reporting platform.

Good Luck!

Answer 2

DataWareHouse is an option, but i think you can use partial tables to archive and split your big data.

You can split all your data by a parameter (for example StockDate). You can store each part individual .mdf file, of course if you want you can store this file another drive.

IF you split data with partial table, when you query "SELECT * FROM StokResults" you can see all your data but you know they are fetching desperate files.