Workflow Tracking Service Database
The SQL tracking service in Windows Workflow Foundation lets you add tracking information about workflows and their associated activities. The SqlTrackingQuery class provides high-level access to the data that is contained in the tracking database. However, you can also use direct queries against the SQL tracking service database views for more detailed information. These views map directly to the underlying SQL tracking service table schemas.
SQL Tracking Service Database Tables
The following information outlines the tables and their associated columns that are used by the SQL tracking service.
Workflow
The Workflow table stores the workflow definition of all workflow types that have been tracked by the SqlTrackingService. It is populated the first time a workflow is instantiated and the SqlTrackingService is enabled for this workflow type.
| Column | Description |
|---|---|
WorkflowTypeId |
Links to the Type table that stores the type name and the assembly name where the workflow type appears. |
WorkflowDefinition |
The definition of the workflow. |
Activity
The Activity table stores all the activity types appearing in the corresponding workflow types.
| Column | Description |
|---|---|
WorkflowTypeId |
Links to the Type table that stores the type name and the assembly name where the workflow type appears. |
QualifiedName |
The qualified name of the activity from the workflow definition. |
ActivityTypeId |
Links to the Type table that stores the type name and the assembly name where the activity type appears. |
ParentQualifiedName |
If the activity is composed in another activity, this field holds the activity-qualified name of the parent activity. |
Type
The Type table stores all the type information and the assembly names in which the types occur. This includes workflow types, activity types, and any other types used within the SqlTrackingService. For example, if you tracked a string as a Tracking Data Item, the string type information is stored in this table.
| Column | Description |
|---|---|
TypeId |
Unique ID for the type. |
TypeFullName |
Fully qualified name of the type. |
AssemblyFullName |
Full name of the assembly in which the type is stored. |
IsInstanceType |
Boolean value that indicates whether the workflow instance is an XOML-only instance (in this case, the type and the assembly full names will be null). |
WorkflowInstance
The WorkflowInstance table maintains the list of all the workflow instances that have been tracking in the tracking database.
| Column | Description |
|---|---|
WorkflowInstanceInternalId |
Unique internal ID that is given to the workflow instance by the database. It is used as a foreign key to join other tables in the database. |
WorkflowInstanceId |
GUID of the workflow instance. |
ContextGuid |
Context information about this workflow instance. |
CallerInstanceId |
ID of the workflow instance that invoked this workflow instance. |
CallPath |
Complete call path that is used in case the workflow instance is called by a workflow instance, which in turn is called by another workflow instance. |
CallerContextGuid |
Context information about the caller. |
CallerParentContextGuid |
Context information about the parent workflow instance of the calling instance. |
WorkflowTypeId |
Link to the workflow table using the workflow type ID. |
InitializedDateTime |
Date and time of the initialization of the workflow instance. |
CreatedDateTime |
Date and time of the creation of the workflow instance. |
DbInitializedDateTime |
Database date and time when the workflow instance record was initialized. |
EndDateTime |
Date and time of the completion/termination of the workflow instance. |
DbEndDateTime |
Database date and time when the workflow instance record was committed. |
ActivityInstance
The ActivityInstance table is used to store the instances of activities that are being tracked. There is a many-to-one relationship from this table to the WorkflowInstance table.
| Column | Description |
|---|---|
WorkflowInstanceInternalId |
Internal database ID of the workflow instance to which the activity belongs. |
ActivityInstanceId |
Unique ID of the activity being tracked for a given workflow instance. |
QualifiedName |
Qualified name of the activity from the workflow definition. |
ContextGuid |
Context of the activity. |
ParentContextGuid |
Context of the parent activity. |
WorkflowInstanceEventId |
Unique ID for the workflow instance event record. |
ActivityExecutionStatusEvent
The ActivityExecutionStatusEvent table stores the different states that an activity has gone through in its life cycle. For a given activity in a given workflow instance, there can be multiple activity states. Only one of them is the current state.
| Column | Description |
|---|---|
ActivityExecutionStatusEventId |
Unique ID for an activity execution status record. |
WorkflowInstanceInternalId |
Internal database ID of the workflow instance to which the activity belongs. |
EventOrder |
Order in which the event occurred. |
ActivityInstanceId |
Unique ID of the activity for which the status is recorded. |
ExecutionStatusId |
Link to the ActivityExecutionStatus table, which stores the different status descriptions. |
EventDateTime |
Date and time when the event occurred. |
DbEventDateTime |
Database date and time when the event occurred. |
ActivityExecutionStatus
The ActivityExecutionStatus table is a reference table that stores the literal values of all the execution states that an activity can go through.
| Column | Description |
|---|---|
ExecutionStatusId |
Unique ID of the status. |
Description |
Literal value of the status. |
TrackingDataItem
For each activity state, the TrackingDataItem table stores a set of data-like variables, properties, and so on, if the SqlTrackingService is tracking these items (as indicated by the tracking profile). The tracking profile can specify which workflow attributes must be tracked when a particular activity reaches a particular status.
| Column | Description |
|---|---|
TrackingDataItemId |
Unique ID that represents the record for a given artifact for a given status of an activity, which belongs to a particular workflowInstance. |
WorkflowInstanceInternalId |
Internal database ID for the workflow instance. |
EventId |
Event ID where the tracking data item was extracted. |
EventTypeId |
Type of the event where the tracking data item was extracted. |
FieldName |
Name of the variable (data item) that is tracked. |
FieldTypeId |
Type ID of the variable (data item) that is tracked. This is referencing the type table. |
Data_Str |
String value of the data that is tracked (if it is a primitive type and can be converted to string). |
Data_Blob |
Binary large object value of the value of the data tracked (if it is a non-primitive type and can be binary serialized). |
DataNonSerializable |
Flag to indicate that there was data, but it could not be converted to a string or binary serialized. |
TrackingDataItemAnnotation
The TrackingDataItemAnnotation table stores any annotation that is made in the workflow for a given tracking data item. The annotations are indicated by the tracking profile.
| Column | Description |
|---|---|
TrackingDataItemId |
ID of the tracking data item for which the annotation is being stored. |
WorkflowInstanceInternalId |
Internal database ID for the workflow instance. |
Annotation |
Literal value of the annotation. |
EventAnnotation
The EventAnnotation table stores any annotation that is associated with workflow events.
| Column | Description |
|---|---|
WorkflowInstanceInternalId |
Internal database ID for the workflow instance. |
EventId |
Internal database ID for the event in which the annotation occurred. |
EventTypeId |
Internal database ID for the type of the event. |
Annotation |
Literal value of the annotation. |
WorkflowInstanceEvent
The WorkflowInstanceEvent table stores the different events that a workflow has gone through in its life cycle. There can be multiple events for a given workflow instance; however, only one of them is the current event.
| Column | Description |
|---|---|
WorkflowInstanceEventId |
Unique ID for the workflow instance event record. |
WorkflowInstanceInternalId |
Internal database ID of the workflow instance to which the activity belongs. |
EventDateTime |
Date and time when the event occurred. |
EventOrder |
Order in which the event occurred. |
EventArgTypeId |
Internal database ID for the data type of the event arguments |
EventArg |
Binary serialized data of the event arguments |
DbEventDateTime |
Database date and time when the event occurred. |
TrackingWorkflowEvent
The TrackingWorkflowEvent table stores the literal values of all the events that a workflow can go through.
| Column | Description |
|---|---|
TrackingWorkflowEventId |
Unique ID of the event. |
Description |
Literal value of the event. |
UserEvent
The UserEvent table stores the different events that are emitted and tracked by custom user track points. A given workflow instance can have multiple user events.
| Column | Description |
|---|---|
UserEventId |
Unique ID for the user event. |
WorkflowInstanceInternalId |
Internal database ID of the workflow instance to which the activity belongs. |
EventOrder |
Order in which the event occurred. |
ActivityInstanceId |
Unique ID of the activity in which the user event occurred (where the code emitting the event occurs in the workflow code). |
EventDateTime |
Date and time when the event occurred. |
UserDataKey |
Key value passed in the user event (when you use the TrackData method). |
UserDataTypeId |
Type ID in the Type table of the user data that is being tracked. |
UserData_Str |
String value of the data that is tracked (if it is a primitive type and can be converted to string). |
UserData_Blob |
Binary large object value of the value of the data tracked (if it is a non-primitive type and can be binary serialized). |
DataNonSerializable |
Flag to indicate that there was data, but it could not be converted to a string or binary serialized. |
DbEventDateTime |
Database date and time when the event occurred. |
AddedActivity
The AddedActivity table stores the activities that are added to the workflow when a workflow change occurs. There can be many activities added for each workflow change.
| Column | Description |
|---|---|
WorkflowInstanceInternalId |
Internal database ID for the workflow instance. |
WorkflowInstanceEventId |
Reference to the event of the workflow instance. |
QualifiedName |
Qualified name of the activity that is added. |
ActivityTypeId |
Link to the Type table to indicate the type and assembly name for the activity that was added as part of the dynamic change. |
ParentQualifiedName |
Qualified name of the parent activity in which this activity is contained. |
AddedActivityAction |
Serialized value of the ActivityChangeAction object created from the dynamic update event |
Order |
Order in which the activity was added to the workflow |
RemovedActivity
The RemovedActivity table stores all the activities that were removed by a workflow change. Many activities can be removed for each workflow change.
| Column | Description |
|---|---|
WorkflowInstanceInternalId |
Internal database ID for the workflow instance. |
WorkflowInstanceEventId |
Reference to the event of the workflow instance. |
QualifiedName |
Qualified name of the activity that was removed. |
ParentQualifiedName |
Qualified name of the parent activity in which this activity is contained. |
RemovedActivityAction |
Serialized value of the ActivityChangeAction object created from the dynamic update event |
Order |
Order in which the activity was added to the workflow |
TrackingProfile
The TrackingProfile table stores the tracking profile for a given workflow type.
| Column | Description |
|---|---|
TrackingProfileId |
Unique ID of the tracking profile. |
Version |
Version of the tracking profile, which should correspond to the version of the Tracking Profile (TrackingProfile.Version or version attribute in the Tracking Profile XML) |
WorkflowTypeId |
Link to the Type table that stores the type name and assembly name of the workflow. |
TrackingProfileXml |
Tracking profile XML. |
InsertDateTime |
Date and time when the profile was inserted. |
TrackingProfileInstance
The TrackingProfileInstance table stores the tracking profiles associated with specific workflow instances. This is used when different profiles are used for different workflow instances.
| Column | Description |
|---|---|
InstanceId |
Workflow instance internal ID for which the tracking profile is being stored. |
TrackingProfileXml |
Tracking profile XML. |
UpdatedDateTime |
Date and time when the update occurred. |
DefaultTrackingProfile
The DefaultTrackingProfile table stores the default tracking profile. If a tracking profile is not associated with a given workflow type, the default tracking profile is used.
| Column | Description |
|---|---|
Version |
Version number of the tracking profile, which should correspond to the version of the Tracking Profile (TrackingProfile.Version or version attribute in the Tracking Profile XML) |
TrackingProfileXml |
Tracking profile XML. |
InsertDateTime |
Date and time when the tracking profile was inserted. |
TrackingPartitionInterval
The TrackingPartitionInterval table stores the partition interval to enable partitioning for data maintenance. This only occurs when the PartitionOnCompletion property of the SqlTrackingService class is set to true. The default is monthly ("m"). You can set it to daily ("d"), or yearly ("y").
| Column | Description |
|---|---|
Interval |
Interval in which you want to partition the tables. |
TrackingPartitionSetName
The TrackingPartitionSetName table holds information about the partition set name.
| Column | Description |
|---|---|
PartitionId |
Unique ID of the partition. |
Name |
Name of the partition. |
CreatedDateTime |
Date and time when the partition was created. |
EndDateTime |
Date and time when the partition ended. |
PartitionInterval |
Partition interval of this specific set. |
SQL Tracking Service Database Roles
- tracking_writer
This is the role that the account running the host process should run under. The role has permissions to retrieve tracking profiles and write tracking data. - tracking_reader
Accounts in this role can view all data but cannot update or modify data. Users in this role also have no access to the underlying tables. - tracking_profilereaderwriter
This role is for user accounts that need to modify profile data.
See Also
Reference
Other Resources
Simple Tracking Example
Windows Workflow Foundation General Reference
Copyright © 2007 by Microsoft Corporation. All rights reserved.