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…

Manual registration of additional persistence stores

1. The first step of course, is to physically create and initialize a persistence database. Start the Configure AppFabric wizard, and select next to go to the Hosting Services config page. Tick the Set persistence configuration checkbox, select sqlStoreProvider as the Persistence provider, and click the Configure button:
   
   
2. In the Persistence Store Configuration window, tick the Initialize persistence store checkbox, specify the Server and Database names for the new persistence database, and then click OK:
   
   
   
   Note: Do no tick the "Register AppFabric persistence store in root web.config" checkbox – we will add the required entries manually.
3. Now, if you open your root web.config, towards the end of the file you will find a section that "declares" the instance stores that AppFabric knows about. Here's an extract with the default config from my machine:
   
   
4. To declare a new persistence store, you should create another <add> element under <instanceStores>. Note that you will also need to define a corresponding connection string under the <connectionStrings> element a bit further down in the file. Your modified web.config file should look similar to:
   
   
5. Save the file. You now have a second persistence store that you can use for your workflow services.

Scripted Version

To perform the above tasks you can also use the AppFabric PowerShell cmdlets as follows:

1. Start a PowerShell cmd window with elevated privileges and import the AppFabric management module:
   
   import-module ApplicationServer
2. Create and initialize a new persistence database using the Initialize-ASPersistenceSqlDatabase cmdlet:
   
   Initialize-ASPersistenceSqlDatabase –Database "NewDB" –Server "NewDBServer\SQLEXPRESS" –Admins "Domain\AppServerAdmins" –Readers "DOMAIN\AppServerReaders" –Users "DOMAIN\AppServerUsers"
   
   Note: The user groups you specify for the –Admins, –Readers, and –Users parameters will be specific to your environment
3. Use the Add-ASAppSqlInstanceStore cmdlet to register the persistence store in the root web.config file:
   
   Add-ASAppSqlInstanceStore -Name secondarySqlPersistenceStore -ConnectionString "Data Source=sqlServerName2\SQLEXPRESS;Initial Catalog=NewDB;Integrated Security=True" –root

Here is the copy/paste-ready script:

import-module ApplicationServer
Initialize-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

Final notes

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.