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?
R – Windows Workflow SQL Persistence Architecture
Architectureworkflow-foundation
Best Answer
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.