Condividi tramite

Using SqlWorkflowPersistenceService

  • Articolo

The SqlWorkflowPersistenceService class represents a fully functional persistence service. You can use this out-of-box service to persist and retrieve workflow state information about a workflow instance when the workflow runtime engine requests it.

The SqlWorkflowPersistenceService service participates in the workflow transaction batching functionality that is provided by the WorkflowCommitWorkBatchService. That is, it adds objects that represent pending writes to the SQL database to the current batch and implements the IPendingWork interface.

The out-of-box implementation of the SqlWorkflowPersistenceService class implements locking access to workflow state information in its database for use in environments where persistence services running in different processes might have access to a single data store. A column within the SQL database schema is used to mark workflow instances that are being used by a specific workflow runtime as locked. These locks are released by the workflow runtime after a predefined period of inactivity. This period of inactivity is defined in the constructor of the SqlWorkflowPersistenceService class. This functionality is provided to deal with situations where a workflow instance that is being accessed by a workflow runtime has already been loaded by another workflow runtime. The second runtime trying to load the workflow will get a WorkflowOwnershipException.

Aa349366.note(en-us,VS.90).gif
When using the SqlWorkflowPersistenceService, be aware of performance limitations of the service. performance, see Performance Characteristics of Windows Workflow Foundation

Note

Locking will not be implemented if a constructor for the SqlWorkflowPersistenceService is used that does not have a parameter for instanceOwnershipDuration. If any value for this parameter is given (even TimeSpan.MaxValue), locking will be enabled.

Note

  To avoid WorkflowOwnershipException exceptions, set the instanceOwnershipDuration value in the persistence provider's constructor to a value greater than the maximum time anticipated that a workflow instance would need to retain ownership of a persistence record. This value can be determined by experimenting with sample target workflows.

For more information about locking and persistence services in general, see Windows Workflow Persistence Services.

Another piece of workflow state managed by the SqlWorkflowPersistenceService is Timer time spans. This information is configured inside your workflow definition using DelayActivity activities. When a workflow is loaded by the SqlWorkflowPersistenceService for the very first time, all of its timer configuration is registered with the workflow runtime. Using the LoadingInterval property defined in the SqlWorkflowPersistenceService, workflow instances are checked to see if any time events need to be processed on a per-workflow instance.

Note

When using both the SqlTrackingService and the SqlWorkflowPersistenceService in a workflow application, it is recommended that you use a single database for both persistence and tracking.

Note

The SqlWorkflowPersistenceService is tied to a binary formatter; as a result, objects with the Windows Communication Foundation DataContractAttribute attribute cannot be persisted with this service.

Opening TCP Port 135 for Microsoft Distributed Transaction Coordinator

Before using the SqlWorkflowPersistenceService service to persist a workflow to a remote SQL server, you must make sure that Windows Firewall has TCP port 135 open for the Microsoft Distributed Transaction Center (MSDTC). To do this, use the following steps.

To open TCP port 135 using Windows Firewall

  1. Click Start, and then click Control Panel.

  2. Open Windows Firewall.

  3. Click the Exceptions tab.

  4. Click Add Program.

  5. In the Browse dialog box, enter \WINDOWS\system32\msdtc.exe.

  6. Click Open, and then click OK.

  7. Click Add Port.

  8. Enter a name for this port opening and enter 135 in the Port number field.

  9. Select TCP, and then click OK.

Creating the SQL Persistence Database

Windows Workflow Foundation Setup does not install the databases that are required by the SqlWorkflowPersistenceService service; however, Setup installs the SQL scripts for creating and configuring the databases for these services. This section details the steps that you must take to correctly configure the SQL Server database that is used by the SqlWorkflowPersistenceService service.

The SQL services that are installed by Windows Workflow Foundation use SQL Server to store information. You can use Microsoft SQL Server 2005 Express, SQL Server 2000 or later versions, or SQL Server 2000 Desktop Engine (MSDE) for these tasks.

To create the SQL persistence database

  1. Using SQL Server 2005 Express, SQL Server 2000 or later versions, or SQL Server 2000 Desktop Engine (MSDE), create a new database named WorkflowPersistenceStore by using the SQL query statement:

    CREATE DATABASE WorkflowPersistenceStore

  2. In the SQL Query Analyzer workspace, select the database that you created in step 1 from the list of available databases.

  3. On the File menu, click Open, and then open the SQL script %WINDIR%\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation\SQL\<language>\SqlPersistence_Schema.

  4. Run the query by clicking Execute, or by pressing F5 to create the SQL Persistence Service tables.

  5. On the File menu, click Open, and then open the SQL script %WINDIR%\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation\SQL\<language>\SqlPersistence_Logic.

  6. Run the query by clicking Execute, or by pressing F5 to create the SQL Persistence Service stored procedures.

Adding SqlWorkflowPersistenceService to the Runtime Engine

You can add runtime services to the Windows Workflow Foundation runtime engine programmatically or by using an application configuration file.

To modify app.config for the SqlWorkflowPersistenceService

  1. In the Services element in the app.config file, create a new element named add.

  2. Add an attribute named type to the add element that has a value of System.Workflow.Runtime.Hosting.SqlWorkflowPersistenceService, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35.

  3. Add an attribute named connectionString to the add element that has a value of Initial Catalog=WorkflowPersistenceStore;Data Source=localhost;Integrated Security=SSPI.

Note

You might have to modify the connection string, depending on the configuration of your SQL server. The connection string shown here assumes that the database name is WorkflowPersistenceStore and that SQL Server is installed on the same system that you are using for application development.

  1. Configure the SqlWorkflowPersistenceService service by adding attributes that correspond to the configurable properties defined in the SqlWorkflowPersistenceService class.

    For example, to specify that a workflow should be unloaded when it enters an idle state, such as when you use a DelayActivity activity, add an attribute named UnloadOnIdle to the add element and give it the value true.

    <add type="System.Workflow.Runtime.Hosting.SqlWorkflowPersistenceService, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionString="Initial Catalog=WorkflowPersistenceStore;Data Source=localhost;Integrated Security=SSPI;" UnloadOnIdle="true"/>

Adding SqlWorkflowPersistenceService to the runtime engine programmatically

  1. Call the AddService method that is defined in the WorkflowRuntime class, passing a new instance of SqlWorkflowPersistenceService.

    The following example shows how to create a SqlWorkflowPersistenceService service by using the same configuration as the example shown in the previous procedure. In this example, the instanceOwnershipDuration is set to TimeSpan.MaxValue and the loadingInterval is set to 2 minutes. These values are the default values used in the SqlWorkflowPersistenceService class.

  2. [C#]

using (WorkflowRuntime workflowRuntime = new WorkflowRuntime())
{
    // Create the SqlWorkflowPersistenceService.
    string connectionString = ="Initial Catalog=WorkflowPersistenceStore;Data Source=localhost;Integrated Security=SSPI;"
    bool unloadOnIdle = true;
    TimeSpan instanceOwnershipDuration = TimeSpan.MaxValue;
    TimeSpan loadingInterval = new TimeSpan(0, 2, 0);
    SqlWorkflowPersistenceService persistService = new SqlWorkflowPersistenceService(connectionString, unloadOnIdle, instanceOwnershipDuration, loadingInterval);
   
    // Add the SqlWorkflowPersistenceService to the runtime engine.
    workflowRuntime.AddService( persistService );
    
    // ...
}

See Also

Reference

SqlWorkflowPersistenceService

Concepts

Windows Workflow Persistence Services

Other Resources

Persistence Host Sample
Using Persistence Services Sample
Custom Persistence Service Sample
Task 3: Use the Windows Workflow Persistence Service