Persistence Database Schema
This topic describes the public views supported by the SQL Workflow Instance Store.
Instances view
The Instances view contains general information about all workflow Instances in the Database.
| Column Name | Column Type | Description |
|---|---|---|
| InstanceId | UniqueIdentifier | The ID of a workflow instance. |
| PendingTimer | DateTime | Indicates that the workflow is blocked on a Delay activity and will be resumed after the timer expires. This value can be null if the workflow is not blocked waiting on a timer to expire. |
| CreationTime | DateTime | Indicates when the workflow was created. |
| LastUpdatedTime | DateTime | Indicates the last time that the workflow was persisted to the database. |
| ServiceDeploymentId | BigInt | Acts as a foreign key to the [ServiceDeployments] view. If the current workflow instance is an instance of a web-hosted service, then this column has a value, otherwise it is set to NULL. |
| SuspensionExceptionName | Nvarchar(450) | Indicates the type of exception (e.g. InvalidOperationException) that caused the workflow to suspend. |
| SuspensionReason | Nvarchar(max) | Indicates why the Workflow Instance was suspended. If an exception caused the instance to suspend, then this column contains the message associated with the exception. If the instance was manually suspended, then this column contains the user-specified reason for suspending the instance. |
| ActiveBookmarks | Nvarchar(max) | If the workflow Instance is Idle, this property indicates what bookmarks the instance is blocked on. If the Instance is not idle, then this column is NULL. |
| CurrentMachine | Nvarchar(128) | Indicates the name of the computer currently has the workflow Instance loaded in memory. |
| LastMachine | Nvarchar(450) | Indicates the last computer that loaded the workflow instance. |
| ExecutionStatus | Nvarchar(450) | Indicates the current execution state of the Workflow. Possible states include Executing, Idle, Closed. |
| IsInitialized | Bit | Indicates whether the workflow instance has been initialized. An initialized workflow instance is a workflow instance that has been persisted at least once. |
| IsSuspended | Bit | Indicates whether the workflow instance has been suspended. |
| IsCompleted | Bit | Indicates whether the Workflow Instance has finished executing. Note: If and only if the InstanceCompletionAction property is set to DeleteAll, the instances are removed from the view upon completion. |
| EncodingOption | TinyInt | Describes the encoding used to serialize the data properties. - 0 – No encoding - 1 – GzipStream |
| ReadWritePrimitiveDataProperties | Varbinary(max) | Contains serialized instance data properties that will be provided back to the workflow Runtime when the instance is loaded. Each primitive property is a native CLR type, which means that no special assemblies are needed to deserialize the blob. |
| WriteOnlyPrimitiveDataProperties | Varbinary(max) | Contains serialized instance data properties that are not provided back to the workflow runtime when the instance is loaded. Each primitive property is a native CLR type, which means that no special assemblies are needed to deserialize the blob. |
| ReadWriteComplexDataProperties | Varbinary(max) | Contains serialized instance data properties that will be provided back to the workflow runtime when the instance is loaded. A deserializer would require knowledge of all object types stored in this blob. |
| WriteOnlyComplexDataProperties | Varbinary(max) | Contains serialized instance data properties that are not provided back to the workflow runtime when the instance is loaded. A deserializer would require knowledge of all object types stored in this blob. |
| IdentityName | Nvarchar(max) | The name of the workflow definition. |
| IdentityPackage | Nvarchar(max) | The package information given when the workflow was created (such as the assembly name). |
| Build | BigInt | The build number of the workflow version. |
| Major | BigInt | The major number of the workflow version. |
| Minor | BigInt | The minor number of the workflow version. |
| Revision | BigInt | The revision number of the workflow version. |
Caution
The Instances view also contains a Delete trigger. Users with the appropriate permissions can execute delete statements against this view that will forcefully remove workflow Instances from the Database. We recommend deleting directly from the view only as a last resort because deleting an instance from underneath the workflow runtime could result in unintended consequences. Instead, use the Workflow Instance Management Endpoint to have the workflow runtime terminate the instance. If you want to delete a large number of Instances from the view, make sure there are no active runtimes that could be operating on these instances.
ServiceDeployments view
The ServiceDeployments view contains deployment information for all Web (IIS/WAS) hosted workflow services. Each workflow instance that is Web-hosted will contain a ServiceDeploymentId that refers to a row in this view.
| Column Name | Column Type | Description |
|---|---|---|
| ServiceDeploymentId | BigInt | The primary key for this view. |
| SiteName | Nvarchar(max) | Represents the name of the site that contains the workflow service (e.g. Default Web Site). |
| RelativeServicePath | Nvarchar(max) | Represents the virtual path relative to the site that points to the workflow service. (e.g. /app1/PurchaseOrderService.svc). |
| RelativeApplicationPath | Nvarchar(max) | Represents the virtual path relative to the site that points to an application that contains the workflow service. (e.g. /app1). |
| ServiceName | Nvarchar(max) | Represents the name of the workflow Service. (e.g. PurchaseOrderService). |
| ServiceNamespace | Nvarchar(max) | Represents the namespace of the workflow Service. (e.g. MyCompany). |
The ServiceDeployments View also contains a Delete trigger. Users with the appropriate permissions can execute delete statements against this view to remove ServiceDeployment entries from the Database. Note that:
Deleting entries from this view is costly since the entire Database must be locked prior to performing this operation. This is necessary to avoid the scenario where a workflow Instance could refer to a non-existent ServiceDeployment entry. Delete from this view only during down times / maintenance windows.
Any attempt to delete a ServiceDeployment row which is referenced to by entries in the Instances view will result in a no-op. You can only delete ServiceDeployment rows with zero references.
InstancePromotedProperties view
The InstancePromotedProperties view contains information for all the promoted properties that are specified by the user. A promoted property functions as a first-class property, which a user can use in queries to retrieve instances. For example, a user could add a PurchaseOrder promotion which always stores the cost of an order in the Value1 column. This would enable a user to query for all purchase orders whose cost exceeds a certain value.
| Column Type | Column Type | Description |
|---|---|---|
| InstanceId | UniqueIdentifier | The ID of the Workflow Instance |
| EncodingOption | TinyInt | Describes the encoding used to serialize the promoted binary properties. - 0 – No encoding - 1 – GZipStream |
| PromotionName | Nvarchar(400) | The name of the Promotion associated with this instance. The PromotionName is needed to add context to the generic columns in this row. For example, a PromotionName of PurchaseOrder could indicate that Value1 contains the cost of the order, Value2 contains the name of the customer who placed the order, Value 3 contains the address of the customer, and so on. |
| Value[1-32] | SqlVariant | Value[1-32] contains values that can be stored in a SqlVariant column. A single promotion cannot contain more than 32 SqlVariants. |
| Value[33-64] | Varbinary(max) | Value[33-64] contains serialized values.For instance, Value33 could contain a JPEG of an item being purchased. A single promotion cannot contain more than 32 binary properties |
The InstancePromotedProperties view is schema bound, which means that users can add indices on one or more columns in order to optimize queries against this view.
Note
An indexed view requires more storage and adds additional processing overhead. Please refer to Improving Performance with SQL Server 2008 Indexed Views for more information.
