WF SqlTrackingService data partitioning

Moustafa Khalil Ahmed (from the Windows Workflow Foundation Team) recently blogged about how the SqlTrackingService included in WF does data partitioning across tables to keep data partitioned to smaller subsets for completed workflows.

Moustafa was kind enough to answer some extra questions about how it worked on the WF forums, and this was his answer, which I believe completements the stuff on his weblog:

"The partition is done on the completion of the workflow instance. That is your records will be in your regular tables until the instance completes and this is when the records are going to move to the added tables. This is designed for applications that have no downtime do not want to incur downtime. We do have an offline scheme as well, so if you have a downtime and want to do partitioning during that time or you want to avoid moving records around when the instances complete or for any other reason you can schedule a task that runs PartitionCompletedWorkflowInstances stored proc which will move all currently completed instances from the live tables to the partitions. Host applications don’t need special privileges to create those tables, the user should be part of the tracking_ roles that are defined in the SqlTrackingService database.

 

SqlTrackingQuery will look at the partitioned tables as well. You can look at the PartitionCompletedWorkflowInstances stored proc as a starting point if you want to have your own Archiving and Purging story other than the one we provide out of box. We are working on documenting the SqlTrackingService schema in WF’s help."