Suggestion for Large SQL Server Database Design

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?

Here is my opinion:

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

• Partition the table based on a broad category like year.

• Put these partitions on different filegroups.

StackExchange Database Administrators Q#3087, answer score: 12