Suggestion for Large SQL Server Database Design

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

• 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.