I have a table that I partitioned into 6 partitions with each partition residing on its own filegroup. The table is partitioned based on a identity column that is a clustered index as maximum queries run on this column. Though we are supposed to maintain
last 12 months worth data online; our application always hits the latest data. And I also wanted to implement sliding window approach hence I aligned index with data.
Our growth rate of table will be 50GB per month. And on an average we will be archiving 50 million records every month.
I made sure that first partition of table reside on first file group and second partition of table reside on second file group and so on. I implemented this because I know my application only hits latest data so in case of
any disaster if I had to restore from a full backup then just restoring primary followed by the latest file group (which has latest data) should get my application up and running. And I tried this piecewise restore on a test DB.
My test DB has a table called Table1. Table 1 has 1mi
View Complete Post