archive strategy for large application database SQL 2008

[BoulderRidge]

I’m trying to design an archive strategy for a large SQL Server 2008 application database (that I didn’t create).

The database has two types of tables: static and run-specific. Static tables don’t change frequently but run-specific tables are completely refreshed once per month. Data from previous months is preserved using a Run ID naming convention: MyTableA_Run001, MyTableA_Run002, MyTableA_Run003 represents 3 monthly refreshes of MyTableA.

[ul]
[li]There are about 35 different run-specific tables in the database (like MyTableA), each one replicated for the number of months available.[/li]
[li]The run-specific tables consume about 210 GB per month available.[/li]
[li]There are about 230 different static tables in the database.[/li]
[li]The static tables consume about 400 GB of space.[/li]
[li]I am required to retain a minimum of 13 months of runs in the live application database at all times.[/li]
[/ul]
I am considering a plan to size the database server to hold all the static tables plus at least 16 months of runs and each time it reaches 16 months to archive the oldest 3 months of run-specific tables, taking it back to 13 months of runs. This archive would only need to run quarterly.

My biggest question is: in what format can I archive these tables so that I can
[ol 1]
[li]Archive them in a compact format to store on a network share[/li]
[li]restore them into a copy of the full application database based on selected Run ID(s) when requested[/li]
[/ol]
Feedback and suggestions on an alternate plans are greatly appreciated.

FYI I am not a DBA but will be working with one to implement the solution. Options may be limited due to my environment.

-- BoulderRidge B-)

 

[BoulderRidge]

update: i just learned that the contents of the "static" tables may be updated as often as quarterly. So now it seems like the goal is to save a copy of all "static" tables once per quarter and all run-specific tables monthly.

same questions apply: what is the best format to preserve this data in a way that can be restored and accessed within a couple days when and if needed while keeping the archive volume reasonable?

Thanks,

-- BoulderRidge B-)

 

[fredericofonseca]

standard backup with compression on for the archives would work - split the backup into at least 4 files (try out which number suits better) - means faster backups and restores.
As for naming adding the run id to the filename would be one option, other would be to backup them up to a folder with run id.

As for the run specific tables - do they have compression on all of them? and for the static ones are they occupying all the pages fully or do they have "free space"?

If feasible (which might be) I would separate the static tables from the run specific ones and keep them on a different db - use synonyms so the app won't be affected. easier to backup the run specific more frequently than the static ones as these randomly change and backup is also bigger.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[BoulderRidge]

Thank you Frederico!

When we are talking about archiving a minimum of 34 tables at a time, is it always easier/advisable to do so in some type of database backup as opposed to exporting the contents into flat files via an SSIS package? IF so why?

Thanks,

-- BoulderRidge B-)