Using SqlWorkflowPersistenceService 

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 instance 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 in order to deal with situations where a workflow instance 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.

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.

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, follow these 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.

NoteNote

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

Footer image
Send comments about this topic to Microsoft.
Show: