Need advice on archiving large SQL Server tables

Question

Our SQL Server databases (the standard version of SQL Server 2008, so for R2) are getting bigger and bigger with the year of data storage. Therefore, we are thinking about transferring old data (possibly older than a year) to the archive database. There are many reasons why it would be beneficial for us to reduce our production bases.

Here's how I plan to do this (using the stock database as an example). For the stock database, there will now be the Stock and Stock_Archive databases. The StockResults table will be called StockResults_Current in the stock database and StockResults_Archive in Stock_Archive. And in the stock database there will be a view called StockResults, which combines the two. In normal times, we will archive our data (i.e. move them to the Stock_Archive database). This is the main idea.

I examined the separation of these tables by putting old data in different filegroups, but I want the main database to be small - not just some of the data files.

Has anyone tried something like this? Or do you have tips on trying to archive this way? Or can you recommend a better way?

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

I plan to do this for all transaction tables that store a lot of data.

Any advice would be highly appreciated.

Answer 1

Sounds like the perfect data warehouse script. You can create tables that match existing stock tables, with optional StartDate and EndDate fields, to keep track of slowly changing parameters. You can then also explore the use of SSIS packages to plan your ETL.

See some links here - A good place to start data storage?

If you don’t have one yet, then you should consider collecting a cube within the reporting platform.

Good luck

Answer 2

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

You can split all your data by parameter (e.g. StockDate). You can save each individual .mdf file, of course, if you want, you can save this file on another drive.

IF you share data with a partial table, when you query for "SELECT * FROM StokResults", you can see all of your data, but you know that they are extracting desperate files.