In reviewing the other two parts, I don't think I specifically address the idea of avoiding data movement (data moving from one file or file group to another because of changes to partition boundaries). However, that was one of my primary goals in implementing partitioning.
In the context of the sample database and the partitions that are included in this series, the method to avoid unnecessary data movement is to schedule the scripts to run just prior to the end of the month (in our case, a couple of days before the end of the month).
- SplitPartition.ps1 (covered next week) will create a new "top end" partition. By running it just before the end of the month *before new records are inserted beyond the new boundary date*, you can avoid data movement.
- MergePartition.ps1 (covered on the 28th) will eliminate the lowest trailing edge partition. This can really be done any time (in accordance with business rules and retention policy). MergePartition.ps1 avoids data movement by "switching" the data into a staging table on the same file group as the data that is to be removed, then dropping the partition and finally dropping the staging tables. In addition, it cleans up the files and file groups that are associated with the dropped partition.
By scheduling both scripts to run at the same time, I have a single job to track.
I should really have added this into the introduction or the summary of the series, but I'm glad that you pointed it out.
Regards,
Hugh