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

SqlTrackingService

Other Resources

Simple Tracking Example
Windows Workflow Foundation General Reference

Copyright © 2007 by Microsoft Corporation. All rights reserved.