SQL Saving Workflow SQL Architecture

Question

I like the idea of ​​WF and would like to save long workflows in an SQL database. To this end, what is the appropriate SQL-level architecture for the persistence database? If the save tables are stored inside the project database or represent the "Portability datatables" project agnositc and only one save database is created, the SSRS database. Can multiple applications use the same save database?

Answer 1

In general, I prefer to store save data and application data together in one database. The main reason for separating them is that you immediately start creating distributed transactions as soon as you execute some kind of transactional database, which is much slower.

I never combine different save databases in one. In practice, each WorkflowRuntime application will be configured differently, so your workflow cannot just choose any host to run, but is tied to a specially configured one that knows everything about the type of workflow. And once you start using DelayActivities and they expire, you have no control over which runtime will try to load the workflow back into memory. Using a single persistence database with multiple instances of the same type of workflow for load balancing purposes is possible, but it's a bit of a challenge. In fact, more often there are several persistence databases for different configured WorkflowRuntimes in one application, and vice versa.

Answer 2

Persistence SQL Database is not associated with your application or project. Each workflow is completely atomic and uniquely identified by a GUID. Therefore, it should be possible to provide a common workflow continuity database for multiple applications.

However, of course, there is a case for supporting multiple persistent databases, one for each application. If multiple applications become dependent on Workflows, you won’t want to create a potential single point of failure by sharing a common save database.

From the point of view of performance and scalability, it also makes sense to separate the persistent databases. Otherwise, you can create a bottleneck where a heavy application affects the performance of other applications. You can also more easily move save databases for one or more applications to another server.