Yesterday someone contacted me in regards to adding a secondary persistence store to their AppFabric installation – they were trying to configure the environment so that workflow service A persists its instances to Store A, while workflow service B persists to Store B. Simple enough, right? Well, I realized that there was no documentation on how exactly to achieve this configuration. So, here you go…
To perform the above tasks you can also use the AppFabric PowerShell cmdlets as follows:
Here is the copy/paste-ready script:
import-module ApplicationServerInitialize-ASPersistenceSqlDatabase –Database "NewDB" –Server "NewDBServer\SQLEXPRESS" –Admins "Domain\AppServerAdmins" –Readers "DOMAIN\AppServerReaders" –Users "DOMAIN\AppServerUsers"add-ASAppSqlInstanceStore -Name secondarySqlPersistenceStore -ConnectionString "Data Source=sqlServerName2\SQLEXPRESS;Initial Catalog=NewDB;Integrated Security=True" –root
The above steps register the new persistence store at the root level (through the root web.config file). This means that the new store will be available for use by all AppFabric applications running on machine. You may also choose to add a persistence store at a specific scope in the IIS application hierarchy. For the scripted version, the only difference will be in the parameters passed to the cmdlet in step 3 – the full list of parameters for the cmdlet is available on its documentation page.
I just tried this out because I wanted to start 2 workflow projects and wanted for second one to use other persistence store. I did all steps, created additonal database with configuration tool of appfabric. This went fine. Then I added additional connection string and instance store in root web.config. I restarted the server and tried to configure persistence for selected site. I found Sql server store but also got following exception:
"The connection string XY used by the current store cannot be found. Workflow instances will not persist. Please select another instance."
But the connection string XY for sure exists in root web config and is used in additional instance store. Did I update some wrong web.config? I found web config under: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config. As you see I have 64 virtual machine but as I saw this config will be used by appfabric.
My update look likes this:
<add connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=XYCatalog;Integrated Security=True;Persist Security=True" name="XY" />
<add name="SqlPersistenceStoreProvider" storeProvider="Microsoft.ApplicationServer.StoreProvider.Sql.SqlWorkflowInstanceStoreProvider, Microsoft.ApplicationServer.StoreProvider, Version=18.104.22.168, Culture=neutral, PublicKeyToken=31bf3856ad364e35" storeControlProvider="Microsoft.ApplicationServer.StoreManagement.Sql.Control.SqlInstanceControlProvider, Microsoft.ApplicationServer.StoreManagement, Version=22.214.171.124, Culture=neutral, PublicKeyToken=31bf3856ad364e35" storeQueryProvider="Microsoft.ApplicationServer.StoreManagement.Sql.Query.SqlInstanceQueryProvider, Microsoft.ApplicationServer.StoreManagement, Version=126.96.36.199, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
<add name="XYSqlPersistenceStore" provider="SqlPersistenceStoreProvider" connectionStringName="XY" />
<serviceManagement endpointConfiguration="ServiceManagementNetPipeEndpoint" enabled="true" authorizedWindowsGroup="AS_Administrators" />
I would appriciate any help!