แก้ไข

แชร์ผ่าน


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:

  1. 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.

  2. 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.