Workflow Foundation Sql Scripts

If you install the .Net Framework 4.5 and you look at the folder %systemroot%\Microsoft.NET\Framework\v4.0.30319\SQL\en, or %systemroot%\Microsoft.NET\Framework64\v4.0.30319\SQL\en, you’ll find a number of Sql Script files:

These scripts are all related to Workflow Foundation, and in this post I try to clarify what each script is used for.

Scripts usually come in pair, with a XxxSchema.sql file matched by a XxxLogic.sql file. The XxxSchema.sql file creates tables and views, the XxxLogic.sql file creates stored procedures, functions, triggers etc. The pairs are always meant to be executed in this order: XxxSchema.sql first, then XxxLogic.sql. Scripts in a pair should be run in the same database.

SqlPersistenceService_Schema.sql and SqlPersistenceService_Logic.sql

These scripts are used for WF3 persistence. You may wonder why these scripts, that refer to WF3, are in the folder of the .Net Framework 4.x. This is because WF3 is also in .Net 4.x, as I pointed out in my previous post. If you are using WF3 in .Net 4, use these scripts to create the workflow persistence DB.

The DB schema for WF3 is relatively simple, with only 2 tables: dbo.InstanceState and dbo.CompletedScope. dbo.InstanceState hosts the actual persisted workflow instances, dbo.CompletedScope is used only in advanced scenarios (compensation).

Tracking_Schema.sql and Tracking_Logic.sql

WF3 ships with a tracking service, System.Workflow.Runtime.Tracking.SqlTrackingService, that writes information to a Sql Server database. You create such a database by running Tracking_Schema.sql and Tracking_Logic.sql. You need the tracking database only if you are using SqlTrackingService. It is possible, though not mandatory, to use the same database for persistence and tracking. In other words, you can run SqlPersistenceService_Schema.sql SqlPersistenceService_Logic.sql, Tracking_Schema.sql and Tracking_Logic.sql in the same database.

WF4 has a different tracking architecture. Also, it ships a tracking participant, EtwTrackingParticipant, that tracks information to .etl files, not to Sql Server. Consequently, there is no equivalent of Tracking_Schema.sql and Tracking_Logic.sql in WF4.

SqlPersistenceProviderSchema.sql and SqlPersistenceProviderLogic.sql

WF in the .Net Framework 3.5 (WF3.5) adds support for Workflow Durable Services, and this requires an update to both the schema and the logic of the database. SqlPersistenceProviderSchema.sql and SqlPersistenceProviderLogic.sql update the persistence database accordingly. Note that these scripts should be executed in an existing WF3 persistence database, that is a database where SqlPersistenceService_Schema.sql and SqlPersistenceService_Logic.sql have already been run.

SqlPersistenceProviderSchema.sql adds the dbo.InstanceData table. SqlPersistenceProviderLogic.sql adds the dbo.DeleteInstance, dbo.InsertInstance, dbo.LoadInstance, dbo.UnlockInstance and dbo.UpdateInstance stored procedures.

SqlWorkflowInstanceStoreSchema.sql and SqlWorkflowInstanceStoreLogic.sql

WF4 introduces a completely new object model, and with it comes a different schema of the persistence database. SqlWorkflowInstanceStoreSchema.sql and SqlWorkflowInstanceStoreLogic.sql create a database suitable for persisting WF4 workflows. The script files are named after the class in WF4 that implements workflow persistence in SqlServer:,System.Activities.DurableInstancing.SqlWorkflowInstanceStore, The structure of the WF4 persistence database is more complex than the structure of the WF3 database. SqlWorkflowInstanceStoreSchema.sql creates a number of tables and views:

SqlWorkflowInstanceStoreLogic.sql adds stored procedures and functions:

 

SqlWorkflowInstanceStoreSchemaUpgrade.sql

WF4.5 introduces versioning. SqlWorkflowInstanceStoreSchemaUpgrade.sql updates the database created by SqlWorkflowInstanceStoreSchema.sql and SqlWorkflowInstanceStoreLogic.sql to accommodate for versioning information. The step-by-step procedure is documented here.

Note that you can save and reload WF4.5 workflows in WF4 databases (that is, databases created with SqlWorkflowInstanceStoreSchema.sql and SqlWorkflowInstanceStoreLogic.sql, but without SqlWorkflowInstanceStoreSchemaUpgrade.sql), as long as you do not associate versioning information to them.

The upgrade of the database consists of these main steps:

• New tables are created to handle versioning: DefinitionIdentityTable and IdentityOwnerTable
• Existing tables are altered with additional columns: InstancesTable.SurrogateIdentityId, RunnableInstancesTable.SurrogateIdentityId, LockOwnersTable.WorkflowIdentityFilter,
• Values are inserted in new tables and in new columns of existing tables, to represent standard versioning information for the workflow instances persisted in the database
• New views and stored procedures are created.

After executing the script, you’ll see the new tables:

The set of stored procedures and functions is the same of the WF4 version.

DropXxx.sql

The DropXxx.sql scripts drop tables and other artifacts. They are useful when a database hosts something else, in addition to persistence or tracking information. In that case, deleting the entire database would cause loss of data.

The versions of SqlWorkflowInstanceStoreXxx.sql

There is one further consideration that applies to SqlWorkflowInstanceStoreSchema.sql and SqlWorkflowInstanceStoreLogic.sql: if you compare these scripts on a machine with the .Net Framework 4.0, and on a machine with the .Net Framework 4.5 installed, you’ll find that they are different. This is because the .Net Framework 4.5 ships with versions of these files that directly create a WF4.5 database. For instance, SqlWorkflowInstanceStoreSchema.sql in the 4.5 version of the .Net Framework creates the  DefinitionIdentityTable, whereas the SqlWorkflowInstanceStoreSchema.sql in the 4.0 version of the .Net Framework does not.

Why is that? Well, if you installed the 4.5 version of the framework and subsequently create a WF Persistence Database, it makes sense to create it with a structure that is suitable for WF4.5 workflows. Note that SqlWorkflowInstanceStoreSchemaUpgrade.sql still has its own place: if you have an existing WF4 persistence database, you should run SqlWorkflowInstanceStoreSchemaUpgrade.sql against it, instead of running the 4.5 version of SqlWorkflowInstanceStoreSchema.sql and SqlWorkflowInstanceStoreLogic.sql: this way, you would preserve the workflow instances currently persisted in the database.

Which scripts to use?

Here is a quick summary of which scripts you need, depending on the version of WF that you are using:

   

	WF3	WF3.5	WF4	WF4.5
SqlPersistenceService_Schema.sql SqlPersistenceService_Logic.sql	X	X
SqlPersistenceProviderSchema.sql SqlPersistenceProviderLogic.sql		X
Tracking_Schema.sql Tracking_Logic.sql	X	X
SqlWorkflowInstanceStoreSchema.sql SqlWorkflowInstanceStoreLogic.sql			X	X
SqlWorkflowInstanceStoreSchemaUpgrade.sql				X

Scripts that are used by WF3 are also found in previous versions of the framework. So it should not come as a surprise to you that, under the folder %systemroot%\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation\SQL\en, you find 4 script files: SqlPersistenceService_Schema.sql, SqlPersistenceService_Logic.sql, Tracking_Schema.sql and Tracking_Logic.sql. Likewise, you’ll find SqlPersistenceProviderSchema.sql and SqlPersistenceProviderLogic.sql in the %systemroot%\Microsoft.NET\Framework\v3.5\SQL\EN folder. And. since WF3 that ships with the .Net Framework 4.x is the same that ships with .Net Framework 3.x, the script files with the same name on different folders are identical, save for references to assemblies: Tracking_Schema.sql in %systemroot%\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation\SQL\en references mscorlib version 2.0.0.0 and System.Workflow.ComponentModel version 3.0.0.0, whereas Tracking_Schema.sql in %systemroot%\Microsoft.NET\Framework64\v4.0.30319\SQL\en references mscorlib version 4.0.0.0 and System.Workflow.ComponentModel version 4.0.0.0.

Comments

• Anonymous
  March 30, 2014
  The comment has been removed
• Anonymous
  March 30, 2014
  The scripts are part of the implementation of System.Activities.DurableInstancing.SqlWorkflowInstanceStore (SWIS) class.You need an InstanceStore-derived class that implements persistence to an Oracle database, which will come with its own scripts.At stackoverflow.com/.../workflow-foundation-4-instance-store-for-oracle some links are provided. I haven't tested them and can't provide any guarantee on their behavior, though.