Configuring Workflow Studio to use SQL Server for Tracking and Persistence

Workflow Studio is built on top of Microsoft's Workflow Foundation and leverages the persistence and tracking services. What are persistence and tracking data?

Persistence data

Persistence data is a checkpoint of the state of an entire workflow saved at a point in the workflow. The value of persisting the workflow is that if anything happens to the server or runtime, the workflow can be restarted from the last checkpoint. Persistence is more valuable with long-running workflows, and the typical IT automation workflows created by Workflow Studio are less likely to need persistence. More information on persistence data can be found on the Microsoft MSDN site.

Note: None of the activities provided by Citrix with Workflow Studio create a persistence checkpoint. If you want to add a checkpoint to your workflows, the Delay Activity (provided by Microsoft) that is available in the Workflow Control folder will persist the workflow state.

Tracking data

Tracking data is a log of the workflow and the activities that make up the workflow. This data is a real-time record of what the workflow is doing as it executes. More information on tracking data can be found on the Microsoft MSDN site.

In Workflow Studio you see tracking data when you view a running workflow in the Designer or in the Job Inspection interface. The tracking data are the individual line items in the workflow log:

Modifying the configuration

The default configuration for Workflow Studio is to store both persistence and tracking data in an XML file in the %AllUsersProfile% section of the profile. We store this data in the local file system as XML files so that remote runtimes will not need a connection to SQL Server which simplifies initial deployment configuration. If you have a SQL Server available and want to configure the runtimes to store this data in SQL you can use the services provided by Microsoft.

To modify a Workflow Studio runtime to store persistence and/or tracking data in a SQL Server database you will need to do the following:

1. Stop all runtime services running on the server
2. Create two databases on your SQL Server to store the data (for purposes of this guide we will name them "WFPersistence" and "WFTracking").
3. Locate the .sql files that you will need to create the SQL schema in "%SystemRoot%\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation\SQL\EN"
4. In the WFPersistence databse, run these SQL scripts in order (first SqlPersistenceService_Schema.sql and then SqlPersistenceService_Logic.sql) to setup your persistence database
5. In the WFTracking databse, run these SQL scripts in order (first Tracking_Schema.sql and then Tracking_Logic.sql) to setup your Tracking database
6. Open the runtime config file %ProgramFiles%\Citrix\Workflow Studio\WorkflowRuntimeHostService.exe.config and modify the following values:
   1. Change the "SqlTrackingService0" value from "DontUse" to "Use".
   2. Change the "SqlTrackingDBConnection0" value to a valid connection string for the tracking store (e.g. "Database=WFTracking;Server=localhost\SQLEXPRESS;Integrated Security=SSPI;").
   3. Change the "SqlPersistenceService0" value from "DontUse" to "Use".
   4. Change the "SqlPersistenceDBConnection0" value to a valid connection string for the persistence store (e.g. "Database=WFPersistence;Server=localhost\SQLEXPRESS;Integrated Security=SSPI;").
7. Restart your workflow runtime services

The data will now be saved in SQL Server where you can back it up centrally. Depending on the size of your workflows and the amount of data being generated, you may also see performance benefits with this configuration.