n a previous article
on Windows Workflow, I discussed the basics of workflow and how workflows can be hosted in your Windows and console applications using the WorkflowRuntime class. In this article, I will discuss how you can manage long-running workflow applications by persisting them in databases and then restoring them back into memory.
By default, a workflow hosting application executes the workflow entirely in memoryfrom beginning to end. However, there are times when you need to execute long-running workflowsone which may not conclude until a couple of hours, or even days, later. In such cases, it is necessary to persist the workflow instance to an external medium so that the workflow can be continued later. This is necessary because workflow can be a real resource hog, occupying more than its share of CPU and memory; by persisting them to external medium you can free up valuable resources.
In Windows Workflow, you can use the SqlWorkflowPersistenceService service to persist a workflow instance to a SQL database server. To use the SqlWorkflowPersistenceService service, you first need to manually prepare the database.
Preparing the Database
|Figure 1. Connecting: Launch the SQL Server Management Studio and provide the name of your database server.|
The SqlWorkflowPersistenceService service works with either SQL Server 2000, SQL Server 2005, or SQL Server 2005 Express Edition. For this article, I used the latter.
The first step is to create the tables used for persistence by running a couple of scripts. The two scripts are: SqlPersistenceService_Schema.sql and SqlPersistenceService_Logic.sql.
For SQL Server 2005 Express, you can download the Microsoft SQL Server Management Studio Express to help you do the work. (For other versions of SQL server, you can use the Query Analyzer.) Once installed, launch Microsoft SQL Server Management Studio Express and specify the name of your database server (see Figure 1). If your SQL Server 2005 Express is installed locally, it should be ".\SQLEXPRESS".
Once connected, create a new database by right-clicking on the Databases item (see Figure 2) and select New Database…. Name the database SqlPersistenceService and click OK. You should be able to see the newly created database under the Databases item. Right-click on the new database name and select New Query (see Figure 3).
Figure 2. Creating: You need to create a new database to hold the workflow persistence.
Figure 3. New Query: Create a new query under the database instance you just created.
Now you need to load the script provided by Microsoft to create the tables necessary for persistence. To do this select File>Open>File and open the SqlPersistenceService_Schema.sql file from: C:\WINDOWS\WinFX\v3.0\Windows Workflow Foundation\SQL\EN
. The Connect To Server dialog will appear again.
Click on the Options << button and type SqlPersistenceService in the "Connect to database:" textbox (see Figure 4). Click Connect.
Figure 4. Associate the query with a specific database.
Figure 5. New Tables: You want to check that the scripts have run correctly and that two new tables have been added.
You can now execute the first script by clicking on the Execute button located on the menu bar. Repeat the entire sequence above to load and execute the second script, SqlPersistenceService_Logic.sql.
To verify that the setup is performed correctly, check that the SqlPersistenceService table now has two additional tables (see Figure 5). You may need to refresh the database to see the newly created tables.