May 7, 2008 at 3:34 am
Hi,
I'm thinking on a reporting solution with SQL Server 2005.
The requirements are that there must be a database to hold data from the last 2 months, and another database or mechanism to archive and compress the older data (due to the large amount of data).
However, there is a requirement that the reports may retrieve data from the archive, when needed.
The problem is that this archive database can have thousands of GB.
What strategy do you recommend to archive the old data? Is that a way to have a compressed archive database and make a restore only of restricted time data? For example, if I want to retrieve a report with data of the last 2 months of 2007, is that a way for me to decompress or restore only data from that time window?
Thanks.
May 7, 2008 at 8:17 pm
Data Archival is generally a business requirement that is considered during database design. It is only one of many variables that affect good DB design.
Before providing my solution, I have a couple of questions. Are you really needing to archive the whole Database? OR Does most of the archive data reside in only a few tables?
The ideal solution would be...
1. Partition the tables that contain archived data across multiple filegroups.
- Determine the appropriate partition function based on the date column
- You indicate months as the timeframe for reports, so create your partitions based on months where FG1 = Jan, FG2 = Feb, FG3 = Mar, etc.
2. Once all updates (writes) to data within a filegroup are complete, change the file group to read only. This way you can make partial backups of the read/write filegroups using the BACKUP DATABASE dbname READ_WRITE_FILEGROUPS command.
3. Considerations to make....
- Can your infrastructure support a filegroup per month? If not, how long does the monthly archives need to exist?
- Partitioning provides many benefits, the biggest being that it is easy to split/move a whole partition of data within seconds using the Alter Table....Split command.
Given the size of your DB partitioning makes sense. It just depends on how many tables need to be archived. There are many more benefits to table partitioning including reduced maintenance, especially if you implement partitioned indexes.
May 8, 2008 at 3:42 am
Thanks for your post Jared.
I made some researches yesterday, and I think I'm going to implement a similar solution.
I will use partitioned tables in the archive database, with one filegroup by month. Then I will recomend the steps described in http://msdn.microsoft.com/en-us/library/ms190257.aspx to use compressed filegroups, as they will only be used as read-only in the reports.
My only doubt is about the performance of the reports with the compressed files. Do you have any experience on this? Is the performance significantly affected?
Regards.
May 8, 2008 at 4:51 pm
No, I don't have any experience with compression. Surely there is some formula to it though. I imagine performance would be an issue once your tables reached a specific size.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply