Cost effective way to archive history data from SQL large tables

Rishabh Patil 161 Reputation points
2021-10-04T11:57:33.797+00:00

Hi all,

I am doing research on how to proceed archive of my large SQL tables.

Currently, we have been doing it in the different archive database of same instance using insert and purge approach. The archived tables has become so big like having more than one billion records in each. We have found that the 1TB hard drive has fully occupied by these archive heap tables and we need to upgrade it more.

Now, the question comes. Do I need to think about other options available for this archiving? Or only way is to put some disk storage.

Mainly we have 3 tables interlinked with each other

My requirement is that :-
1] I should be able to archive all the records from these tables older than 6 months.
2] Incremental archiving
3] Need to run the archive everyday maybe?

Note: We don't make very much use of that archive data.

I am looking for cost effective solution to tackle this once and for all.

Any help would be appreciated.
Thanks

@Erland Sommarskog

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,757 questions
0 comments
Accepted answer
  1. Erland Sommarskog 101.4K Reputation points MVP
    2021-10-04T22:05:23.637+00:00

    Obviously, you will need disk space for that data.

    But there are compression techniques, for instance columnstore, and there is a special COLUMNSTORE_ARCHIVE which gives even better compression.

    It sounds to me like a good thing that you have separate databases for this archive data, because then you can easily move those databases around without affecting the main database.

    Whether you rant to run the archiving daily, weekly or with some other frequence, depends both on the archiving and how the archiving affects the main database, and maybe more the latter.

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rishabh Patil 161 Reputation points
    2021-10-04T14:18:41.257+00:00

    Someone please help on this.
    Not sure if this has been reached to the technet forum.

    0 comments
  2. Rishabh Patil 161 Reputation points
    2021-10-05T11:37:45.853+00:00

    @Erland Sommarskog

    Thanks the compression archival sounds something useful.
    I will implement and see how much space I could save with this. Cheers!

    0 comments
  3. Rishabh Patil 161 Reputation points
    2021-10-05T12:21:49.88+00:00

    @Erland Sommarskog

    Hi, one table has LongDescription column as Varchar(Max) getting below error.

    Msg 35343, Level 16, State 1, Line 3
    The statement failed. Column 'LongDescription' has a data type that cannot participate in a columnstore index. Omit column 'LongDescription'.

    How should I deal now?
    Thanks