8

We are creating a database in MSSQL 2008 R2 Standard where we will be storing a large number of records. We estimate 200 million+ records in one table annually and we are primarily INSERTing with very few UPDATEs or DELETEs on the data. Its a data archival system where we insert historic records on a daily basis. We will generate different sort of reports on this historic record on user request so we've some concerns and require technical input and advice.

  • What is the best way to manage this kind of archival tables and database?
1
  • 1
    If you are designing a large database (or one large to you) then it is critical to get the deisgn right from the start and the best way to do that is to hire a database specialist who has worked with datbases inteh range you are talking about. This is more critical than hiring application developers.
    – HLGEM
    Mar 9, 2012 at 16:12

3 Answers 3

12

Here is my opinion:

  1. If you are having very few updates/deletes you can increase the pagefill factor to 95%. This will save on space and reads. Do some testing though.
  2. Partition the table based on a broad category like year.
  3. Put these partitions on different filegroups.
7

200 million rows per year is not especially large (unless the rows are unusually large). You need to pay attention to sound database design principles (normalization) and make use of standard features like indexing and partitioning. Obviously the right hardware is important too.

There isn't enough information here to give specific advice. Consider hiring someone if you feel you need help with the detailed design and implementation.

1
  • Thanks for ur input. we've applied the design principles which you're referring but will work on indexing once the development part is complete. I guess for partitioning you need Enterprise license and we've Standard edition license at the moment.
    – kodvavi
    Aug 18, 2011 at 0:25
6
  • Make sure that your design makes it possible for your inserts always to be at the end of the table. Hint Clustered Index.

  • Only have very few nonclustered index supporting the reports you need to do to keep maintening them to minimum. Are these reports pregenerated? if yes then consider this question: Is it ok if the report takes 2 hours to generate?(without index) or 1mins(with index). Maybe it is ok to let the report take 2 hours to have one index less? or maybe not? If the report isn't pregenerated well that is another question then since users don't like to wait and you might need to implement more indexes to support your reports.

  • From how you describe this database it sounds like you are expecting lots of rows and the data will add and grow alot. Did you consider how to back this system up? I meen most data will be the same and just adding new? I don't know the business requirmets of this system but to me it seems like in a year or two this might be a database of considerable size and you might have trouble making to many full backups. Consider making one full back up with periodically(weekly?) and differential(daily?) and transaction loggs(hourly?).Ofcourse as I said I don't know the business requirments maybe you do not need all backups all the time? Size can be an issue in archival systems.

2
  • 1
    Thanks martin for your input. Actually the db contain statistics and historic record about agriculture products. The growth is substantial and your input about backup is useful. We've already planned the backup routine and your input added some great value. Our existing backup process for different database has somewhat same approach. Daily differential and weekly full backup.
    – kodvavi
    Aug 18, 2011 at 0:19
  • 1
    btw design is almost final and we're using SSRS for reporting requirements and its work great but still we're fine tuning and giving performance boost before going into production.
    – kodvavi
    Aug 18, 2011 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.