Windows Workflow SQL Persistence Architecture

Question

I like the idea of WF and would like to persist long running workflows to a SQL database. To that end, what is the appropriate architecture at the SQL level for the persistence database? Should the persistence tables exist inside the project's database or is the Persistence datatables project agnositc and only one persistence database be created, a la SSRS's database. Can multiple applications use one persistence database?

Answer 1

The SQL Persistence database is not coupled to your application or project. Each workflow is entirely atomic and is uniquely identified by a GUID. Hence it should be possible to share a common workflow persistence DB across multiple applications.

However there is surely a case for maintaining multiple persistence DBs, one for each application. If the operation of multiple applications become dependent on Workflows you wouldn't want to create potential single point of failure by sharing a common persistence DB.

From a performance and scalability point of view it also makes sense to separate persistence DBs. Otherwise you can create a bottle neck where a heavy application affects the performance of other applications. You can also decide to move the persistence DBs for one or more applications to a different server more easily.

Answer 2

In general I prefer to keep my persistence data and application data together in one database. The main reason against splitting them is that you immediately start creating distributed transactions as soon as you do any transaction database work which is a lot slower.

I never combine different persistence databases into one. In practice each application's WorkflowRuntime will be configured differently so your workflow cannot just pick any host to run in but is bound to a specifically configured one that knows all about the workflow type. And once you start using DelayActivities and they expire you have no control over which runtime will try load the workflow back into memory. Using one persistence database with multiple instances of the SAME workflow type for load balancing purposes is possible though but a bit of a challenge. In fact it is more common to have multiple persistence databases for differently configured WorkflowRuntimes in a single application that that the other way round.