June 21, 2010 at 3:49 pm
I am looking at partitioning a few tables that have a large amount of data in them. This is a production, manufacturing database that can see 8 million records come in, in a couple months. I have looked at several ideas so far and considered archiving using SSIS and possibly replication. I ruled replication out because if for some reason I had to rebuild the replicated database and was unable to have the original snapshot, then I would be out of luck. After looking around and reading about partitioning, it seems to allow me to use replication and the table partition to get what I need. My question is that there are several fields in the tables that I need to use to determine when a record will move from partition to partition. Currently I have a status of the record and a date field that will be used to determine when the record should move. An example would be that the record is still in 'open' status and if the record is in other than 'open' status and is older than the current date going back thirty days; then the record will move. So if I have a record that is 31 days old and is still in 'open' status it will stay in the first partition. if a record is other than 'open' and is more than 30 days it will move to the second partition. Then there will be similar types of criteria on the second partition to the third and etc... The final number of partitions have not been determined and I am only in the testing phase to see if partitioning will do what I need. Thanks
John
June 21, 2010 at 4:04 pm
I know that I can create a column specifically for tracking my criteria and use a sp or function to update that column. But I was trying to avoid that if possible.
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply