Sunday, December 27, 2009

Windows WF Persistence


Workflows consist of various activities. Some of these activities might be blocking which requires waiting for something to happen as part of the workflow process within or outside it. This generally happens when workflows involved human interactions. Windows WF provides this amazing facility of persistence of workflows for these blocking activities.

Benefits of Persistence:

The greatest thing is that it can not only survive a host application environment crash but it could also survive a host machine crash due to persistence. If a host system is crashed workflow should be able to restore to another machine without any issue. Let’s see!

There are many processes running on our machines at the same time. Pushing our workflows to the persistent storage would free up some memory resources to be available to for other processes increasing the overall performance of your systems.

Put it to test! But what test is it exactly?

Workflows can be long running. There might be scenarios in which workflows are waiting of some action to proceed further. This might be any external calls or internal event or anything else. A good workflow engine should support us in these long running scenarios. If Windows WF passes this criterion then we might call it to be a good workflow environment. It supports these scenarios using off-the-shelf Persistence Service provided. Remember that we can always override the default implementation of these services with our custom implementations.

Default WF Persistence Service:

The default persistence service provided with Windows WF is SqlWorkflowPersistenceService. This service keeps the workflow object in SQL Server database for persistence.

The database will obviously have to have a schema that the persistence service knows about. To create this database, you can use some SQL scripts that come with WF. These scripts are placed in the C:\WINDOWS\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation\SQL folder after installing WF on your machine. There are separate SQL scripts for creating the schema and the stored procedures (logic) that need to go into that database.

When to Persist?

  1. Waiting for some Requests:

We might want to free some resources when we are waiting for something to happen and workflow is idle. This can be provided declaratively or through code.

  1. On demand persistence:

In this scenario, workflow is not waiting for some trigger but we just want to load workflow to persistence. This type of unloading generally takes place through code. This might be because of releasing some resource. Specially, on demand rehydration might help in case of failure recovery.


Let us discuss the terminologies used in workflow world related to persistence. We might call them workflow persistence slangsJ. In order to communicate with workflow community, we must know them.


To move workflows to persistence store is called dehydration. As discussed, the default persistence service with WF provides dehydration of workflows in SQL Server database. This is also called “Unloading”.


Hydration is about loading the workflow objects from persistence store back to the workflow runtime. This is also called “Loading”.

Enough Study, Let’s Practice!

You would be amazed to know that using WF Persistence Service is just about executing two scripts (To create database schema. They are already provided by Microsoft) and adding a configuration in app.config file. For supporting developers even more, some events are provided which can be used for housekeeping work for the workflow.

Step # 01 (Create Database)

Let us create a database named WorkflowPersistenceServicePractice.

CREATE DATABASE WorkflowPersistenceServicePractice

Step # 02 (Execute SqlPersistenceService_Schema.sql script)

As discussed above that this script should be available as part of .net 3.0 installation. Find the script in the path specified above and execute it in the database created in Step 1.

This script takes care of creating a role named state_persistence_users to the database. Additionally, it creates two tables and creates indexes on them. The tables’ names are as follows:

  1. InstanceState:

This is to hold the details about the workflow instances currently in persistence store. As soon as a workflow object is rehydrated, the information about workflow instance is removed from this table.

  1. CompletedScope: This is used for workflows that use compensation.

The workflow state is stored in database as image data. Image is a SQL Server datatype which can hold up to 2,147,483,647 bytes.

Step #03 (Execute SqlPersistenceService_Logic.sql script)

This script is also installed with Windows WF installation. Executing this script would create some stored procedures. These stored procedures are used by WF for DML operations involving the persistence of workflow object by WF runtime.

The list of stored procedures created, is as follows:

1. InsertInstanceState

2. RetrieveAllInstanceDescriptions

3. UnlockInstanceState

4. RetrieveInstanceState

5. RetrieveNonblockingInstanceStateIds

6. RetrieveANonblockingInstanceStateId

7. RetrieveExpiredTimerIds

8. InsertCompletedScope

9. DeleteCompletedScope

10. RetrieveCompletedScope

Step # 04: (Adding Service to Runtime)

Declarative Option:

Some configurations are required to provide WF about the details of persistence service to use.

Add reference of System.configuration.dll library and update Services section of app.config file by adding the details of the persistence service to use:

<xml version="1.0" encoding="utf-8" ?>



<section name="HostingWorkflowRuntime" type="System.Workflow.Runtime.Configuration.WorkflowRuntimeSection, System.Workflow.Runtime, Version=3.0.00000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />


<HostingWorkflowRuntime Name="Hosting">



<add type="System.Workflow.Runtime.Hosting.SqlWorkflowPersistenceService, System.Workflow.Runtime,Version=3.0.00000.0,Culture=neutral,PublicKeyToken=31bf3856ad364e35"

connectionString="Initial Catalog=WorkflowPersistenceServicePractice;Data Source=localhost;Integrated Security=SSPI;"

LoadIntervalSeconds="5" />




While creating the workflow runtime object in the hosting application, we have to specify the runtime configuration section to use in our app.config file. You can see that we have provided configuration for persistence service in HostingWorkflowRuntime section of our configuration.

WorkflowRuntime workflowRuntime = new WorkflowRuntime("HostingWorkflowRuntime")

Addition of Service through Code:

In the following code, we are adding a persistence service which unloads the workflow instance when idle to the database as specified by the assigned connection string. This seems more practical because in many cases, workflows might be developed by some other team and you might not want to use the configuration settings in the workflow library created by the workflow team.

Dim myConnectionString = ""//assign connection string to persistence DB

NameValueCollection args = new NameValueCollection();

args.Add("ConnectionString", myConnectionString);

args.Add("UnloadOnIdle", "true");

SqlWorkflowPersistenceService persistenceService = new SqlWorkflowPersistenceService(args);


Step #05 (Housekeeping Operations in Events)

WorkflowRuntime provides various events for providing developers mechanism to write application specific logic before or after hydration / dehydration of workflow objects. Most of them are not persistence specific but they are generally used with persistence. The following is the details of such events:

  1. WorkflowPersisted
  2. WorkflowLoaded
  3. WorkflowUnLoaded
  4. WorkflowIdled

Wednesday, December 23, 2009

String.Format for padding Leading Zeros in LINQ To SQL

As most of you might know that LINQ to SQL does not support many .net string manipulation functions. Basically, it seems that it mostly the limitation of T-SQL and not just LINQ to SQL. All LINQ to SQL queries are parsed into T-SQL statements and they are sent to the database and the result is provided to the caller code.

The list of string manipualation functions not supported in LINQ to SQL can be found in the following location.

Today I had the requirement of using one Of the many string manipulation functions that LINQ to SQL does not support. This is String.Format. I don't want to discuss in detail about String.Format function because it might take the discussion in a different direction. The requirement was to add leading zeros to the value of variable and make it a fixed length value of certain length.

If we just want to do it in .net we write it like this: String.Format("{0:00000}", myVariable). Here myVariable is the name of variable whose value we want to have as a fixed length string with leading zeros if required.

Dim myValues = From myTable in myTables Select New with {.myVal = String.Format("{0:00000}", myTable.myVariable)}

Even If I have not made any syntactic mistake, this code would not work. It would rather result in an exception in runtime explaining the limitation of LINQ to SQL parsing the Format function. Now it is a genuine requirement to add leading zeros. What should we do?

Well the answer is another string manipulation function provided, which is PadLeft. It is used to pad a character to the left of a string. We can make it a fixed length string by specifying the length of resulting string. It should be used like this:

Dim myValues = From myTable in myTables Select New with {.myVal = myTable.myVariable.PadLeft(5, "0"c)}

Here we have requested a string with length = 5. It would result in appending character "0" to the left of the string if the length of its value is lesser than 5. And this did the work for me.

Remember that there is another method PadRight() which you might guess that it could be used for padding any special character to the right of a string.

Tuesday, December 15, 2009

Workflow Tracking (Windows WF)


Tracking is one of the features provided in WF to gain visibility to the execution of workflow to the administrator. The other features are performance counters, tracing etc.

Though WF provides default implementation of these services. They are all replaceable. They can be replaced by custom implementation of services by hosting applications. Different implementation can be provided by various hosts like WPF, SharePoint etc.

Workflows in Windows WF are defined in terms of a group of activities. WF provides necessary interfaces, for workflow as a whole or activities in particular, to provide tracking data. This data can be collected by registering components.


Tracking Profile:

Tracking profile helps runtime filter events and forwarding tracking service only those tracking records which are based on these filters. This results in tracking only relevant workflow details. So tracking profile is used to control tracking information passed to any monitoring application.

Theoretically, Tracking profile sits between Tracking Service and Tracking Runtime Engine. It helps runtime in providing data and events information to the tracking service from the workflow instance.

Tracking profiles can be designed using Tracking Profile Designer.exe. This is basically a sample application available as part of Windows SDK. We can also create and manipulate tracking profiles using TrackingProfile class. TrackingProfileSerializer class allows us to author these profiles in declarative fashion. Both of these classes are available in System.Workflow.Tracking namespace.

This consists of Workflow trackpoints, activity trackpoints and user trackpoints which in turn are consisted of annotations, conditions and extracts.

Based on the above discussion, you might already have developed this idea that there are three types of tracking events. They are as follows:

  1. Workflow status events: These are defined for the whole workflow like Created, Started, and Terminated etc.

  1. Activity status events: These events are related to individual activities like Executing, Canceling etc.

  1. User events: These are defined to get user defined data from activities. They can be fired by using the Activity.TrackData() method.

There are classes available in System.Workflow.Runtime.Tracking namespace to define these trackpoints. They are WorkflowTrackpoint, ActivityTrackpoint and UserTrackpoint.

Filtered Locations: They specify the matching / excluding locations i.e. which events must be tracked. Excluded locations can not be specified for workflow trackpoints.

Annotations: They allow us to specify collection of string before an event is passed to the Tracking Service. This is used for tagging events so that their execution might be recorded accordingly.

Extracts: They are used to provide workflow / activity data during an event. We may specify the properties which might be passed with the event. Extracts can not be specified for workflow trackpoints.


In order to audit the evaluation of rules, we can register RuleActionTrackingEvent. This event can be registered to track the output of rules evaluation. We can use this to audit the rule evaluations.

Tracking Channel:

It is used to for communication of data from tracking runtime to tracking service. This is passed in the form of tracking records. This is in the form of the objects of following classes in the same System.Workflow.Runtime.Tracking namespace: WorkflowTrackingRecord, ActivityTrackingRecord and UserTrackingRecord.

Tracking channel knows about the instance being tracked and the WF runtime. It waits for specific message and passes it to the Tracking Service. Tracking records are persisted by calling Send() method on Tracking Channel be Tracking Service.

Tracking Runtime:

This is responsible for initializing tracking infrastructure. It is passed with Tracking Profile and channel information during initialization. It uses them to pass selected tracking information to tracking service.

Tracking Service:

It has central significance in the whole tracking infrastructure. During initialization, it passes tracking profile and channel information to the tracking runtime. Instead of directly storing tracking records to the tracking database using tracking channel, tracking runtime passes them to the service which in-turn is responsible for storing them to persistence store.

Its responsibility is to provide tracking channel and profile information to Tracking runtime. After a workflow starts running, it receives events information from Tracking runtime and stores them to Tracking Store using Tracking Channel.

It is possible that more than once service is registered with the runtime. In that case each service will have to provide its own tracking profile and channel.It can be added to the workflow both programmatically and declaratively. For programmatic support, it is directly added to the workflow runtime. Alternatively, it can be specified in the section of config file.

The partial implementation of this service is provided by default in Windows WF.

Tracking Store:

Tracking Service can use a tracking store for persistence of tracking records received from Tracking runtime. It can be any persistent storage like files, databases. As discussed, the default implementation for tracking service stores data in SQL Server database.

I think it would be a great implementation of Inversion of control (Unity) to define dependency between tracking service, channel and profile.

Performance Impacts of Tracking:

One of the feature to improve performance in the cases of very large tables and very large databases in partitioning database objects. In the same lines, if we partition tables used for tracking WF data, we might be improving performance of the workflow instance. WF provides out of the box support for partitioning tracking tables. This is basically a windowing technique which, by default, moves data to partitioned tables after completion of workflow instance. This can be set using the TrackingService class. You can also move data on demand using PartitionCompletedWorkflowInstances stored procedure.

If you want to study further about Partitioning and its support in SQL Server. You can read my article on

Supported Applications and Utilities:

In this discussion, we have discussed about two sample utilities provided with Windows SDK for tracking. Let me mention their names again:

  1. Tracking Profile Designer: This can be used to create custom tracking profiles.
  2. Workflow Monitoring Tool: To monitor workflows currently executing.

Tracking information can be made available for business users by creating SQL Server Reporting Services based reports from the data created by WF tracking service in the tracking store.

We can use BizTalk Server 2006 R2 BAM Tracking Interceptor for Windows WF to intercept tracking information from Windows WF to Biztalk server. We can make use of Business Activity monitoring (BAM) features of Biztalk Server.