Edit

Share via

How to: Enable SQL persistence for workflows and workflow services

  • Article

This article describes how to configure the SQL Workflow Instance Store feature to enable persistence for your workflows and workflow services both programmatically and by using a configuration file.

Windows Server App Fabric simplifies the process of configuring persistence. For more information, see App Fabric Persistence Configuration.

Before using the SQL Workflow Instance Store feature, create a database that the feature uses to persist workflow instances. The .NET Framework 4.6.1 setup program copies SQL script files associated with the SQL Workflow Instance Store feature to the %WINDIR%\Microsoft.NET\Framework\v4.xxx\SQL\EN folder. Run these script files against a SQL Server 2005 or SQL Server 2008 database that you want the SQL Workflow Instance Store to use to persist workflow instances. Run the SqlWorkflowInstanceStoreSchema.sql file first and then run the SqlWorkflowInstanceStoreLogic.sql file.

Note

To clean up the persistence database to have a fresh database, run the scripts in %WINDIR%\Microsoft.NET\Framework\v4.xxx\SQL\EN in the following order.

  1. SqlWorkflowInstanceStoreSchema.sql
  2. SqlWorkflowInstanceStoreLogic.sql

Important

If you do not create a persistence database, the SQL Workflow Instance Store feature throws an exception similar to the following one when a host tries to persist workflows.

System.Data.SqlClient.SqlException: Could not find stored procedure 'System.Activities.DurableInstancing.CreateLockOwner'

The following sections describe how to enable persistence for workflows and workflow services using the SQL Workflow Instance Store. For more information about properties of the SQL Workflow Instance Store, see Properties of SQL Workflow Instance Store.

Enable persistence for self-hosted workflows that use WorkflowApplication

You can enable persistence for self-hosted workflows that use WorkflowApplication programmatically by using the SqlWorkflowInstanceStore object model. The following procedure contains steps to do this.

  1. Add a reference to System.Activities.DurableInstancing.dll.

  2. Add the following statement at the top of the source file after the existing "using" statements.

    using System.Activities.DurableInstancing;

  3. Construct a SqlWorkflowInstanceStore and assign it to the InstanceStore of the WorkflowApplication as shown in the following code example.

    SqlWorkflowInstanceStore store =
    new SqlWorkflowInstanceStore("Server=.\\SQLEXPRESS;Initial Catalog=Persistence;Integrated Security=SSPI");

WorkflowApplication wfApp =
    new WorkflowApplication(new Workflow1());

wfApp.InstanceStore = store;

    Note

    Depending on your edition of SQL Server, the connection string server name may be different.

  4. Invoke the Persist method on the WorkflowApplication object to persist a workflow, or Unload method to persist and unload a workflow. You can also handle the PersistableIdle event raised by the WorkflowApplication object and return appropriate (Persist or Unload) member of PersistableIdleAction.

    wfApp.PersistableIdle = delegate(WorkflowApplicationIdleEventArgs e)
{
    return PersistableIdleAction.Persist;
};

Note

See the How to: Create and Run a Long Running Workflow step of the Getting Started Tutorial for step by step instructions.

Enable persistence for self-hosted workflow services that use the WorkflowServiceHost

You can enable persistence for self-hosted workflow services that use WorkflowServiceHost programmatically by using the SqlWorkflowInstanceStoreBehavior class or the DurableInstancingOptions class.

Use the SqlWorkflowInstanceStoreBehavior class

The following procedure contains steps to use the SqlWorkflowInstanceStoreBehavior class to enable persistence for self-hosted workflow services.

  1. Add a reference to System.ServiceModel.dll.

  2. Add the following statement at the top of the source file after the existing "using" statements.

    using System.ServiceModel.Activities.Description;

  3. Create an instance of the WorkflowServiceHost and add endpoints for the workflow service.

    WorkflowServiceHost host = new WorkflowServiceHost(new CountingWorkflow(), new Uri(hostBaseAddress));
host.AddServiceEndpoint("ICountingWorkflow", new BasicHttpBinding(), "");

  4. Construct a SqlWorkflowInstanceStoreBehavior object and to set properties of the behavior object.

    SqlWorkflowInstanceStoreBehavior instanceStoreBehavior = new SqlWorkflowInstanceStoreBehavior(connectionString);
instanceStoreBehavior.HostLockRenewalPeriod = new TimeSpan(0, 0, 5);
instanceStoreBehavior.InstanceCompletionAction = InstanceCompletionAction.DeleteAll;
instanceStoreBehavior.InstanceLockedExceptionAction = InstanceLockedExceptionAction.AggressiveRetry;
instanceStoreBehavior.InstanceEncodingOption = InstanceEncodingOption.GZip;
instanceStoreBehavior.RunnableInstancesDetectionPeriod = new TimeSpan("00:00:02");
host.Description.Behaviors.Add(instanceStoreBehavior);

  5. Open the workflow service host.

    host.Open();

Use the DurableInstancingOptions property

When the SqlWorkflowInstanceStoreBehavior is applied, the DurableInstancingOptions.InstanceStore on the WorkflowServiceHost is set to the SqlWorkflowInstanceStore object created using the configuration values. You can do the same programmatically to set the DurableInstancingOptions property of the WorkflowServiceHost without using the SqlWorkflowInstanceStoreBehavior class as shown in the following code example.

workflowServiceHost.DurableInstancingOptions.InstanceStore = sqlInstanceStoreObject;

Enable persistence for WAS-hosted workflow services that use the WorkflowServiceHost using a configuration file

You can enable persistence for self-hosted or Windows Process Activation Service (WAS)-hosted workflow services by using a configuration file. A WAS-hosted workflow service uses the WorkflowServiceHost as the self-hosted workflow services do.

The SqlWorkflowInstanceStoreBehavior, a service behavior that allows you to conveniently change the SQL Workflow Instance Store properties through XML configuration. For WAS-hosted workflow services, use the Web.config file. The following configuration example shows how to configure the SQL Workflow Instance Store by using the sqlWorkflowInstanceStore behavior element in a configuration file.

<serviceBehaviors>
    <behavior name="">
        <sqlWorkflowInstanceStore
                    connectionString="Data Source=(local);Initial Catalog=DefaultPersistenceProviderDb;Integrated Security=True;Async=true"
                    instanceEncodingOption="GZip | None"
                    instanceCompletionAction="DeleteAll | DeleteNothing"
                    instanceLockedExceptionAction="NoRetry | BasicRetry |AggressiveRetry"
                    hostLockRenewalPeriod="00:00:30"
                    runnableInstancesDetectionPeriod="00:00:05" />

    </behavior>
</serviceBehaviors>

Important

Microsoft recommends that you use the most secure authentication flow available. If you're connecting to Azure SQL, Managed Identities for Azure resources is the recommended authentication method.

If you do not set values for the connectionString or the connectionStringName property, the SQL Workflow Instance Store uses the default named connection string DefaultSqlWorkflowInstanceStoreConnectionString.

When the SqlWorkflowInstanceStoreBehavior is applied, the DurableInstancingOptions.InstanceStore on the WorkflowServiceHost is set to the SqlWorkflowInstanceStore object created using the configuration values. You can do the same programmatically to use the SqlWorkflowInstanceStore with WorkflowServiceHost without using the service behavior element.

workflowServiceHost.DurableInstancingOptions.InstanceStore = sqlInstanceStoreObject;

Important

It is recommended that you do not store sensitive information such as user names and passwords in the Web.config file. If you do store sensitive information in the Web.config file, you should secure access to the Web.config file by using file system Access Control Lists (ACLs). In addition, you can also secure the configuration values within a configuration file as mentioned in Encrypting Configuration Information Using Protected Configuration.

The .NET Framework 4.6.1 installation adds the following behavior extension element to the Machine.config file so that you can use the <sqlWorkflowInstanceStore> service behavior element in the configuration file to configure persistence for your services.

<configuration>
    <system.serviceModel>
        <extensions>
            <behaviorExtensions>
                <add name="sqlWorkflowInstanceStore" type="System.Activities.DurableInstancing.SqlWorkflowInstanceStoreElement, System.Activities.DurableInstancing, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
            </behaviorExtensions>
        </extensions>
    </system.serviceModel>
</configuration>