ADO.NET grain persistence

The Microsoft.Orleans.Persistence.AdoNet package stores grain state using database-specific queries exposed through ADO.NET. Orleans includes persistence scripts for:

Database	Driver package	Invariant	Script
SQL Server	Microsoft.Data.SqlClient	Microsoft.Data.SqlClient	SQLServer-Persistence.sql
MySQL or MariaDB	MySql.Data or MySqlConnector	Driver-specific	MySQL-Persistence.sql
PostgreSQL	Npgsql	Npgsql	PostgreSQL-Persistence.sql
Oracle	Oracle.ManagedDataAccess.Core	Oracle.DataAccess.Client	Oracle-Persistence.sql
SQLite	Microsoft.Data.Sqlite	System.Data.SQLite	Sqlite-Persistence.sql

The invariant is an Orleans provider identifier and doesn’t always match the driver package name. Install the driver package and run both the shared *-Main.sql script and *-Persistence.sql script for the selected database.

Configure a provider

var builder = Host.CreateApplicationBuilder(args);

builder.UseOrleans(siloBuilder =>
{
    siloBuilder.AddAdoNetGrainStorage(
        "stateStore",
        options =>
        {
            options.Invariant = "Npgsql";
            options.ConnectionString =
                builder.Configuration.GetConnectionString("grainState")
                ?? throw new InvalidOperationException(
                    "The grainState connection string isn't configured.");
        });
});

For SQLite:

siloBuilder.AddAdoNetGrainStorage(
    "localState",
    options =>
    {
        options.Invariant = "System.Data.SQLite";
        options.ConnectionString = "Data Source=orleans-state.db";
    });

SQLite is useful for local, single-process scenarios. Its file locking, deployment topology, and availability characteristics generally don’t fit a multi-silo production cluster.

Serialization and schema evolution

Configure GrainStorageSerializer when the default JSON representation doesn’t meet application requirements:

var builder = Host.CreateApplicationBuilder();

builder.Services.AddSingleton<ExampleStorageSerializer>();
builder.UseOrleans(siloBuilder =>
{
    siloBuilder.AddAdoNetGrainStorage(
        "stateStore",
        (OptionsBuilder<AdoNetGrainStorageOptions> optionsBuilder) =>
        {
            optionsBuilder.Configure<ExampleStorageSerializer>((options, serializer) =>
            {
                options.Invariant = "Npgsql";
                options.ConnectionString =
                    builder.Configuration.GetConnectionString("grainState")
                    ?? throw new InvalidOperationException(
                        "Connection string 'grainState' is required.");
                options.GrainStorageSerializer = serializer;
            });
        });
});

Changing the serializer isn’t a database migration. The new serializer must read existing payloads or the application must migrate them separately.

Concurrency contract

ADO.NET persistence scripts implement Orleans’ record-level optimistic concurrency. The database version is exposed to application code as an ETag. Writes and clears compare the expected version and fail on a mismatch.

Provider queries must preserve the parameter names, result names, and types expected by Orleans. Persistence writes run inside a database transaction and must roll back on failure. This transaction covers one grain-state record; it doesn’t make writes to multiple IPersistentState<T> instances atomic.

Customize queries

The OrleansQuery table contains vendor-specific statements used by the provider. Administrators can tune those statements while preserving the Orleans query contract. Keep customized scripts under source control, apply them through the normal database deployment process—for example, using a data-tier application (DACPAC)—and test reads, writes, clears, first-write races, and ETag conflicts after every change.

Operational query shape

The provider supplies the common identity parameters GrainIdHash, GrainIdN0, GrainIdN1, GrainTypeHash, GrainTypeString, GrainIdExtensionString, and ServiceId. WriteToStorageKey also receives GrainStateVersion and PayloadBinary; ClearStorageKey and the optional DeleteStorageKey receive GrainStateVersion.

ReadFromStorageKey must return columns named PayloadBinary and Version. WriteToStorageKey must return the resulting version as NewGrainStateVersion. Clear and delete queries return one version value in their first result column; its name is ignored. A successful operation returns an advanced version, while an unchanged or missing value signals an ETag conflict. Versions must be representable as a signed 32-bit integer.

Each provider instance reads OrleansQuery during silo startup and doesn’t poll it afterward. Changing the table therefore affects newly initialized providers only. A rolling silo restart is appropriate when old and new queries and schemas can coexist; coordinate an outage or staged migration when they can’t.

Database-specific customization can use features such as partitioned tables and indexes, memory-optimized tables, natively compiled modules, PolyBase, or linked servers when those capabilities fit the deployment.