Using SQL Server as Orleans storage

I'm trying to use SQL Server as a data store for Orleans.

I have managed to get my solution working using the Azure Local Storage Emulator, but can't get it to work with a local instance of SQL Server. I've created the database using:

https://github.com/dotnet/orleans/blob/master/src/OrleansSQLUtils/CreateOrleansTables_SqlServer.sql

and made my config file look like the one here:

http://dotnet.github.io/orleans/Documentation/Advanced-Concepts/Configuring-SQL-Tables.html

This is my config file:

<?xml version="1.0" encoding="utf-8"?>
<OrleansConfiguration xmlns="urn:orleans">
  <Globals>
    <StorageProviders>
      <SystemStore SystemStoreType ="SqlServer"
           DeploymentId="OrleansTest"
           DataConnectionString="Data Source=.\SQL2014;Initial Catalog=Orleans;Integrated Security=True;Pooling=False;Max Pool Size=200;Asynchronous Processing=True;MultipleActiveResultSets=True" AdoInvariant="System.Data.SqlClient" />
      <Provider Type="Orleans.SqlUtils.StorageProvider.SqlStorageProvider" Name="SqlServer" />
      <!--<Provider Type="Orleans.Storage.AzureTableStorage" Name="AzureStore" DataConnectionString="UseDevelopmentStorage=true" />-->
    </StorageProviders>
    <SeedNode Address="localhost" Port="11111" />
  </Globals>
  <Defaults>
    <Networking Address="localhost" Port="11111" />
    <ProxyingGateway Address="localhost" Port="30000" />
  </Defaults>
</OrleansConfiguration>

I've added the following attribute to my grains:

[StorageProvider(ProviderName = "SqlServer")]

I then get the following error: Could not locate a state map factory type...

Please could someone let me know what I need to add to the Providers or if I am doing something else wrong? Do I need to create something to do with a StateMapFactoryType for the SQL Provider?

Thanks

Solution

Firstly the <SystemStore> is not the StorageProvider. That node is for the Membership Oracle. like this:

<OrleansConfiguration xmlns="urn:orleans">
  <Globals>
    <SystemStore SystemStoreType ="SqlServer"
       DeploymentId="OrleansTest"
       DataConnectionString="Data Source=.\SQL2014;Initial Catalog=Orleans;Integrated Security=True;Pooling=False;Max Pool Size=200;Asynchronous Processing=True;MultipleActiveResultSets=True" AdoInvariant="System.Data.SqlClient" />        
  </Globals>
  <Defaults>
    <Networking Address="" Port="11111" />
    <ProxyingGateway Address="" Port="30000" />
  </Defaults>
</OrleansConfiguration>

now lets put in your StorageProvider

<OrleansConfiguration xmlns="urn:orleans">
  <Globals>

<StorageProviders>
  <Provider Type="Orleans.SqlUtils.StorageProvider.SqlStorageProvider" Name="SqlServer" />
</StorageProviders>


    <SystemStore SystemStoreType ="SqlServer"
       DeploymentId="OrleansTest"
       DataConnectionString="Data Source=.\SQL2014;Initial Catalog=Orleans;Integrated Security=True;Pooling=False;Max Pool Size=200;Asynchronous Processing=True;MultipleActiveResultSets=True" AdoInvariant="System.Data.SqlClient" />        
  </Globals>
  <Defaults>
    <Networking Address="" Port="11111" />
    <ProxyingGateway Address="" Port="30000" />
  </Defaults>
</OrleansConfiguration>

now to the fun part. Setting up the Orleans.SqlUtils.StorageProvider.SqlStorageProvider

this particular storageprovider is based on the ElasticClient from the P&P group, which uses a special sharded database (e.g. a shard master database, and the shard database(s))

might suggest this lower friction provider (insert shameless plug (if it works I wrote it, if it doesnt then I have no idea who did :D ) https://github.com/OrleansContrib/Orleans.StorageProviders.SimpleSQLServerStorage

ok, back to Orleans.SqlUtils.StorageProvider.SqlStorageProvider you need a few more configuration items:

ConnectionString
MapName
StateMapFactoryType

<Provider Type="Orleans.SqlUtils.StorageProvider.SqlStorageProvider" Name="guts"  ConnectionString = "Server=.;Initial catalog=guts;Integrated Security=SSPI;" MapName="guts" StateMapFactoryType="ClassName, assemblyname" />

You will need to create the StateMapFactory implementing Orleans.SqlUtils.StorageProvider.IGrainStateMapFactory see https://github.com/dotnet/orleans/blob/v1.1.3/src/OrleansSQLUtils/Storage/Provider/IGrainStateMapFactory.cs

you can use https://github.com/dotnet/orleans/blob/v1.1.3/src/TesterInternal/StorageTests/SQLAdapter/SampleGrainStateMapFactory.cs for a reference

BUT!! it looks like release 1.1.3 still has the Orleans.SqlUtils.StorageProvider.IGrainStateMapFactory marked as internal, so you will have to compile from source or get latest from github.