As shown previously it isn’t hard to run Workflow Services on Windows Azure. In fact all we need to do is add a bit of extra configuration and we can work as normal. However normally when I am hosting long running workflows in IIS I always add a SqlWorkflowInstanceStore to store the workflow state when it is not running so we can survive the inevitable IIS AppDomain restarts. Unfortunately this isn’t quite as straightforward as I had hoped.
SQL Azure != SQL Server
The important thing to remember is that SQL Azure might be similar to SQL Server but has different capabilities. Normally you can create a SQL Server database, run 2 SQL scripts provided by the workflow team from Microsoft and use the database as the workflow instance store. The problem here is that SQL Azure doesn’t support a number of features use in the provided SQL scripts. One of these features is the use of allow_page_locks = off in the SqlWorkflowInstanceStoreSchema.sql script used to create the database store. A second not supported is setting the maximum degree of parallelization using option (maxdop 1) in SqlWorkflowInstanceStoreLogic.sql.
The first, allow_page_locks = off, causes SQL Azure to throw errors and has to be removed from the SqlWorkflowInstanceStoreSchema script before it can run. The second, option (maxdop 1), doesn’t cause any errors and is actually the value SQL Azure always uses so there is no problem there. Making the first change is trivial but the problem is that we are now using stored procedures that have not been tested and are not supported. Still when I tried, in a test environment, this seemed to work fine and I have heard similar reports from others.
The case of the retrying connection
There is however a second potential problem as SQL Azure behaves different under load. The problem is that executing SQL commands may fail even after a connection has been opened successfully and the official solution it retry executing the command several times. See here for more details.
So far this problem hasn’t surfaced for me but I haven’t used this under heavy load so take care.
Adding the SqlWorkflowInstanceStore in Windows Azure
Once the database has been created configuring the SqlWorkflowInstanceStore is really no different from a regular workfow serrvice. Just add the sqlWorkflowInstanceStore element to the <serviceBehaviors><behavior> section with the required connection string and you are good to go. Adding the <workflowIdle> element to unload the workflow as soon as it is idle is also advisable as having multiple web roles is the norm and the next request for the workflow service could equally well be serviced another server.
<system.serviceModel>
<serviceHostingEnvironment aspNetCompatibilityEnabled="true"
multipleSiteBindingsEnabled="true">
</serviceHostingEnvironment>
<behaviors>
<serviceBehaviors>
<behavior>
<serviceMetadata httpGetEnabled="true" />
<serviceDebug includeExceptionDetailInFaults="true" />
<sqlWorkflowInstanceStore connectionStringName="WorkflowInstanceStore" />
<workflowIdle timeToUnload="00:00:00" />
</behavior>
</serviceBehaviors>
</behaviors>
</system.serviceModel>
With a connection string section like this:
<connectionStrings>
<add name="WorkflowInstanceStoreConnectionString"
connectionString="[[your SQL Azure connection string]]"
providerName="System.Data.SqlClient" />
</connectionStrings>
Conclusion
While it isn’t very hard to do this it is unfortunate that we have to go in a change SQL scripts to get this running and as a result be in a, at least for the moment, not supported scenario. After all hosting workflow services in IIS without using the SqlWorkflowInstanceStore is asking for problems as sooner or later IIS is going to recycle the AppDomain and abort our workflows
www.TheProblemSolver.nl
www.dotnetevents.nl