Workflow Foundation's SQL Persistence Service
Contents
In my last post on Workflow Foundation I introduced the workflow runtime and its default “in-memory” lifecycle. I also presented a lifecycle that saves and unloads idle workflows. This is the lifecycle used when a persistence service is added to the workflow runtime. In this post I will show how to setup a Sql Server database such that it can be used by Workflow Foundation’s Sql Server Persistence service. I will also show how to add the Sql Persistence service to the workflow runtime. Once I have the persistence service added to the workflow runtime I will use the workflow from my last two posts to demonstrate the effects of a persistence service on a workflow’s lifecycle. Finally I will show the various configuration options of this service.
You can download the code for this post at the link below.
To create a SQL Server database for workflow persistence you will need to use the SQL scripts that Workflow Foundation provides. These scripts can be found at the location below assuming the default location was used during installation of Visual Studio and the .NET Framework.
%WINDIR%\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation\SQL\\
These scripts where originally introduced in V3.0 of the .NET Framework (the same version that gave us Workflow Foundation) and they were not modified in V3.5 or V3.5 SP1 of the .NET Framework. Therefore, if you are using V3.5 of the .NET Framework then you will still use the scripts located in the V3.0 directory listed above.
There are two files in this directory that are needed to create a SQL Server persistence store. SqlPersistenceService_Schema.sql will create all the needed tables and SqlPersistenceService_Logic.sql will create all the stored procedures that Workflow Foundation expects to find in a SQL Server based persistence store.
Note: There are two other files located in the directory named above. They are Tracking_Schema.sql and Tracking_Logic.sql. These files are used to create a SQL Server tracking database.
When designing and developing a workflow that uses a persistence service it is helpful to put a database project into your Visual Studio solution and use this project as a container for all database scripts needed to create the persistence database. This allows the database scripts to be maintained right from the Visual Studio environment. It also allows all the database scripts to be versioned alongside any workflow source code. This is always a good idea when you have source code that is dependent on database schemas and database stored procedures.
Figure 1 shows the Add New Project dialog to add a SQL Server Project to a Visual Studio solution. There are many different types of database projects that can be added to a Visual Studio solution – so make sure you choose the correct one. For example, there is a project template for creating assemblies that take advantage of the embedded CLR. There is also a project template that creates a very rich database project for manipulating every aspect of SQL Server 2000 or SQL Server 2005. The database template that I prefer for SQL Persistence services is the simple database project found under the “Other Project Types” category of the Add New Project dialog. This template creates an easy to use project for maintaining the schema scripts, and stored procedure scripts mentioned above.
Figure 1 – Adding a Database Project
Once you have specified the name and location of the new database project, Visual Studio will prompt you for a reference to a database. If you have previously used Visual Studio to manage any database then at this point you will be shown the “Add Database Reference” dialog. This is shown in Figure 2. This dialog is basically showing all the database references that Visual Studio knows about and is the same list of databases that are shown in Visual Studio’s Server Explorer. If you wish to use one of these existing databases as the persistence store for your workflows then select the desired database and click the OK button. If you wish to setup a reference to an existing database that Visual Studio does not currently know about then click the “Add New Reference …” button. Additionally, if you wish to create a new database then click the “Add New Reference …” button as well.
Are you new to Visual Studio?
The Server Explorer is the server management console for Visual Studio. You can use this window to explore system resources on the current machine. You can also log on to other servers and explore their system resources. A common use of the Server Explorer is to open database connections and manage databases. However, you can also explore Event Logs, Message Queues, Performance Counters, Services and more. To get a sneak peak of the Server Explorer check out Figure 6.
Figure 2 – Add a database reference
It is an acceptable design decision to use an existing database as your persistence store. If your workflow updates an application specific database while it executes then it is best to use this application specific database as your persistence store. This will make backup procedures and disaster recovery procedures much easier to perform. For example, if you were to use two separate databases for application specific data and workflow persistence then if one database had to be restored from a previous state (or previous backup) then you would have to make sure you restored both databases from a backup that was taken at the same point in time. If you did not restore both databases from the same point in time then your application data would not be consistent with the persisted state of your workflows. Clearly it is easier to have everything in one database.
In this post I will create a new database. To create a new database, click the “Add New Reference …” button that is shown in Figure 2. Once you click the “Add New Reference …” button you will be shown the dialog shown in Figure 3. This dialog is used to setup a reference to an existing database that you have never used from the Visual Studio environment. This dialog can also be used to create a new database. Figure 3 shows how you would create a new SQL Express database. Notice that I am selecting “Microsoft SQL Server Database File (SqlClient)” as the Data source or type of database. This indicates a SQL Express database. I also have specified the name, location and security settings of the new database.
Figure 3 – Creating a new SQL Express database
When you click the OK button of Figure 3 Visual Studio will create a database project that contains a reference to the newly created database. Once you have a database project setup within your solution you can add the SQL scripts described above to the project. Add them to the Create Scripts folder of the new project. Once all this is done your solution explorer will look like Figure 4.
Figure 4 - Solution Explorer with a Database Project
At this point the database that we just created is an empty database. It contains no tables, stored procedures or data. Therefore, the next step is to run the scripts that were just added to the “Create Scripts” folder. You can run each script by right clicking on it within the project explorer and selecting the run option from the context menu. This is shown in Figure 5. Visual Studio will run these scripts against the database shown in the database reference node of the SqlDB project (refer to Figure 4).
Figure 5 - Running the SQL Persistence Service scripts
Once both SQL scripts are run you can use the Server Explorer to view the tables and stored procedures in the newly created database. Figure 6 shows the newly created database as it appears in Visual Studio’s server explorer. In this figure you can see the Tables node and the Stored Procedures node fully expanded.
Figure 6 - Server Explorer showing the new Persistence Database
The SQL Server database is now ready to be used for workflow persistence. The next step is to configure the workflow runtime to persist workflows to this database when one of the persistence points mentioned in my previous post is encountered. For convenience these persistence points are listed below:
· When a workflow goes idle.
· Before a workflow instance completes. (This removes any instance data from the underlying persistence store.)
· Before a workflow instance terminates. (This removes any instance data from the underlying persistence store.)
· On the completion of activities that are marked with the PersistOnCloseAttribute attribute.
· When a developer calls WorkflowInstance.Unload or WorkflowInstance.TryUnload.
The SqlWorkflowPersistenceService class is used to persist workflows when one of these persistence points is encountered. Figure 7 shows how to instantiate this class passing into its constructor a connection string for the database just created. Figure 7 also shows additional configuration options that can be used to control the behavior of the persistence service. The various configuration options of the SQL persistence service are described in Figure 8. Once the SqlWorkflowPersistenceService class is instantiated and configured it is added to the workflow runtime using the AddService function.
// Create and configure the Sql Persistence service
persistenceService = new SqlWorkflowPersistenceService(
cn, //connection string
true, //unload on idle flag
new TimeSpan(1, 0, 0), //instance ownership duration - set to 1 Hour
new TimeSpan(0, 0, 5)); //loading interval - set to 5 seconds
persistenceService.EnableRetries = false;
// Add the Sql Persistence service to the runtime.
wr.AddService(persistenceService);
Figure 7 – Adding a Persistence Service to the Workflow Runtime using code
|
Configuration Option
|
Description
|
|
ConnectionString
|
A database connection string to a SQL Server batabase.
|
|
UnloadOnIdle
|
If set to true then idle workflows will be unloaded from memory after they are persisted.
|
|
InstanceOwnership
|
If multiple workflow runtimes are using the same persistence database then persisted workflows will need to be locked in order to prevent two runtimes from loading and running the same workflow instance. This parameter controls the length of time that locks are maintained on idle workflows.
|
|
LoadingInterval
|
The frequency at which the persistence service will poll the persistence database for workflows with delay activities whose timeout duration has expired.
|
|
EnableRetries
|
Specifies whether the SQL Persistence Service will retry committing a work batch.
|
Figure 8 – SQL Persistence service configuration options
Persistence services can be added via code or configuration. Figure 9 shows the same information being specified in a configuration file. Notice that all the relevant information is specified in a configuration section named “WorkflowRuntime”. When you use a configuration file to configure a persistence service you cannot use the default constructor of the workflow runtime. Figure 10 shows how to instantiate the workflow runtime passing into its constructor the configuration section that holds the persistence service parameters.
xmlversion="1.0"encoding="utf-8" ?>
<configuration>
<configSections>
<sectionname="WorkflowRuntime"type="System.Workflow.Runtime.Configuration.WorkflowRuntimeSection, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
configSections>
<WorkflowRuntime>
<Services>
<addtype="System.Workflow.Runtime.Hosting.SqlWorkflowPersistenceService, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Users\Keith\Documents\Visual Studio 2008\Projects\WF Sandbox\SqlDB\SqlDB.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True"
UnloadOnIdle ="true"
LoadIntervalSeconds="5"
EnableRetries="false"
/>
Services>
WorkflowRuntime>
configuration>
Figure 9 – Specifying Workflow Runtime parameters in a Configuration File
// Use this code if the persistence service is specified via configuration.
// Notice that the configuration section is passed into the constructor.
WorkflowRuntime wr = new WorkflowRuntime("WorkflowRuntime");
Figure 10 – Adding a Persistence Service to the Workflow Runtime using a configuration file
It is important to note that the default setting for the UnloadOnIdle flag is ‘false’ and that I have explicitly set it to ‘true’ in both my code example and my configuration example. This flag determines if the runtime should automatically unload workflows from memory after being persisted. When set to ‘false’ the workflow runtime will not unload the workflow instance from memory after the instance is persisted. When the workflow is ready to resume execution the runtime does not need to load the workflow from the persistence store. This option is useful when you need your workflow to be a little more performant and you are not concerned with the extra system resources that are consumed while the workflow is in memory and idle.
I would now like to demonstrate what happens to the lifecycle of a workflow with persistence points when it is executed by a workflow runtime that contains a persistence service. Figure 11 shows the workflow from my last post which contains a delay activity (one of the ways to introduce a persistence point into a workflow). Recall that when this workflow was run from a workflow runtime without a persistence service that it idled and remained in memory. This is shown in Figure 12. When the workflow hits the delay activity execution is delayed for the specified timeout duration. In other words when the workflow idles it is not persisted to any form of persistent storage and it is not unloaded from memory.
Figure 11 – Simple Workflow with a Delay Activity
Figure 12 – Lifecycle without a Persistence Service
Before adding a persistence service to the workflow runtime you must make sure that every part of your workflow can be serialized. This includes the workflow itself as well as any other objects that are part of the workflow’s state. The workflow itself is already setup for serialization by the base classes from which it is constructed. However, remember that our workflow contains a property declared as a custom type defined within the workflow project. The property declaration is shown in Figure 13 for clarity. Here we see a property named Expense that is of type ExpenseReport. The ExpenseReport class is not currently setup for serialization. The workflow runtime will throw the error shown in Figure 14 when it tries to persist this workflow. The text of this error is easy to understand. It states, “WorkflowLibrary.ExpenseReport is not marked as serializable”.
public partial class SequentialExpenseReportApproval : SequentialWorkflowActivity
{
// Workflow property - data that will be processed
private ExpenseReport expense;
public ExpenseReport Expense
{
get { return expense; }
set { expense = value; }
}
Figure 13 – Property Declaration
Figure 14 – Serialization Error
To correct this problem merely mark the ExpenseReport class as serializable using the Serializable attribute as shown in Figure 15. When this attribute is placed on a class definition the Common Language Runtime knows that you are aware that objects created from this class will be serialized and the data may be used outside of the Common Language Runtime.
[Serializable]
public class ExpenseReport
{
public decimal Amount;
public string Employee;
public string Title;
}
Figure 15 – ExpenseReport Class marked as Serializable
Figure 16 shows the same workflow when it is run from a workflow runtime that has had a persistence service added to it via either code or configuration (Figures 7 or 8 respectively). When the workflow idles it is persisted to the Sql Persistence database and then it is unloaded from memory. When the delay activity’s duration expires the workflow is loaded back into memory from the state previously persisted in the SQL persistence database and then it continues execution from the point it was idled.
Figure 16 – Lifecycle with a Persistence Service
For fun I will now change the UnloadOnIdle flag to false and rerun the workflow. Figure 17 shows the output when this change is made. Here the workflow is persisted when it is idled; however, it is not unloaded from memory. Rather it stays in memory and when the workflow is ready to resume execution it does not need to be loaded from the SQL persistence database. Why configure the runtime in this fashion? Configuring a workflow to be persisted and not unloaded is useful when you do not have a lot of concurrently executing workflows and the workflows are large – either a lot of activities or they process large messages. Since they remain in memory they do not need to be reloaded. Workflows configured in this fashion are still durable. If the system or the application in which the workflow is running were to crash then the workflow can be reloaded and resumed from the last persistence point once the application is restarted.
Figure 17 – Lifecycle with the UnloadOnIdle flag set to false
In this post I showed how to create a database that can be used as a persistence store for Workflow Foundation workflows. Then I showed how to create and configure a persistence service that uses this database. Next I added the persistence service to the workflow runtime using both code and configuration. Finally I demonstrated the effects of a persistence service on the lifecycle of a workflow.
Persistence services provide durability to workflow execution. Persistence Services can also be configured to allow the workflow runtime to use system resources more efficiently when the workflows it is managing are long running.