Thanks Hugh for the article which came on the right time for me.

I am about start working on a partitioning a "continuous loading" event table. The requiremnt is to have 12 months of data online and the data aged "gracefully".

I will have 12 data partitions + the lower partition. Each partiton will have its own file group, to ensure the most important aspect of recoverability. In case of DR, I can restore the filegroup which contains the current partion and the system is ready to go. It is about 10GB data in each partition, so no need to wait for 120 GB data to be restored to have the system available. IMHO partition/filegroup alignment is very important from point of system availability.

Sliding window will be implemented to switch in and switch out partitions.

Few thoughts/questions below.

1. Rather than sliding the window that moves forward in time by creating new partitions, can we use a "rotating window" which uses partitions in a ring structure? In this scenario we have a partition scheme with fixed names for each month. The script identities the new month and decides which partition will be used within the "ring". Then switches out data using a temp table on that filegroup to archive, truncate the table and switch back in for the new month. The advantage is that the partition number could be easily identified with current month. Any thoughts on this ?

2. I would love to to keep current quarter data on a filegroup on faster (RAID 10) disk (which handles the loading and most 80 percent of user queries) and previous quarters on slower disks until it get archived to very very slow disk. This means I have to switch out data between quarters which involves data movement. This would have ensured that the data is "aged gracefully". Any thoughts on this ?

Regards

Jimmy