I'm investigating using partitions in 2005 and need to know about compacting, using read only, best practices for managing large (200 mil rows) tables. I have set up a table with datetime partition in quarterly buckets. This table will have arround 10-20 million rows in each partition. each partition has it's own file and it's own file group. I'm currently thinking of keeping about 3-4 years fully available, and maybe dumping the oldest partition to a flat file quarterly while creating a new partition for a future quarter to keep the table going. The table will only ever need data inserted, nothing will be updated.
I want to know what options I have for archiving data as it gets older.
I know you can set a file group to read only, if anyone has experience with this please help me with how it might apply
I've read a bit about compacting but don't really understand it's application or how it would apply to this situation.
Is 10-20 million rows too much for one partition, file, filegroup?
How do I remove a partion and replace it with a new one for future data?
Is it better to write old data to a flat file or to compact it?
What exactly does compacting it do? Can a partition be compacted but sill be part of the same table and queryable?
lota questions I know but any help would be much appreciated.
I want to know what options I have for archiving data as it gets older.
I know you can set a file group to read only, if anyone has experience with this please help me with how it might apply
I've read a bit about compacting but don't really understand it's application or how it would apply to this situation.
Is 10-20 million rows too much for one partition, file, filegroup?
How do I remove a partion and replace it with a new one for future data?
Is it better to write old data to a flat file or to compact it?
What exactly does compacting it do? Can a partition be compacted but sill be part of the same table and queryable?
lota questions I know but any help would be much appreciated.