NHibernate persistence in multi-tenant systems

This sample demonstrates how to configure NHibernate persistence to store tenant-specific data in separate catalogs for each tenant. The tenant-specific information includes saga state and business entities that are accessed using NServiceBus-managed session.

This sample uses Outbox to guarantee consistency between the saga state and the business entity. Outbox and timeout data are stored in a dedicated catalog shared by all tenants.

The sample assumes the tenant information is passed as a custom message header tenant_id.

Prerequisites

Ensure an instance of SQL Server (Version 2016 or above for custom saga finders sample, or Version 2012 or above for other samples) is installed and accessible on localhost and port 1433. A Docker image can be used to accomplish this by running docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d mcr.microsoft.com/mssql/server:latest in a terminal.

Alternatively, change the connection string to point to different SQL Server instance.

At startup each endpoint will create its required SQL assets including databases, tables, and schemas.

The databases created by this sample are:

• NHibernateMultiTenantReceiver
• NHibernateMultiTenantA
• NHibernateMultiTenantB

Running the project

1. Start the Sender project (right-click on the project, select the Debug > Start new instance option).
2. The text Press <enter> to send a message should be displayed in the Sender's console window.
3. Start the Receiver project (right-click on the project, select the Debug > Start new instance option).
4. The Sender should display subscription confirmation Subscribe from Receiver on message type OrderSubmitted.
5. Press A or B on the Sender console to send a new message either to one of the tenants.

Verifying that the sample works correctly

1. The Receiver displays information that an order was submitted.
2. The Sender displays information that the order was accepted.
3. Finally, after a couple of seconds, the Receiver displays confirmation that the timeout message has been received.
4. Open SQL Server Management Studio and go to the tenant databases. Verify that there are rows in saga state table (dbo.OrderLifecycleSagaData) and in the orders table (dbo.Orders) for each message sent.

Code walk-through

This sample contains three projects:

• Shared - A class library containing common code including messages definitions.
• Sender - A console application responsible for sending the initial OrderSubmitted message and processing the follow-up OrderAccepted message.
• Receiver - A console application responsible for processing the OrderSubmitted message, sending OrderAccepted message and randomly generating exceptions.

Sender project

The Sender does not store any data. It mimics the front-end system where orders are submitted by the users and passed via the bus to the back-end.

Receiver project

The Receiver mimics a back-end system. It is configured to use SQL persistence in multi-tenant mode.

Creating the schema

The default NHibernate persistence installers create all schema objects in a single catalog. In a multi-tenant scenario, schema objects need to be created manually. The ScriptGenerator class provides APIs required to generate schema creation scripts.

var outboxScript = ScriptGenerator<MsSql2012Dialect>.GenerateOutboxScript();
var entityScript = ScriptGenerator<MsSql2012Dialect>.GenerateOutboxScript(typeof(OrderMap));
var sagaScript = ScriptGenerator<MsSql2012Dialect>.GenerateSagaScript<OrderLifecycleSaga>();
var timeoutsScript = ScriptGenerator<MsSql2012Dialect>.GenerateTimeoutStoreScript();

SqlHelper.ExecuteSql(Connections.TenantA, sagaScript);
SqlHelper.ExecuteSql(Connections.TenantA, entityScript);

SqlHelper.ExecuteSql(Connections.TenantB, sagaScript);
SqlHelper.ExecuteSql(Connections.TenantB, entityScript);

SqlHelper.ExecuteSql(Connections.Shared, outboxScript);
SqlHelper.ExecuteSql(Connections.Shared, timeoutsScript);

The above code ensures that business entity and saga tables are created in the tenant databases while the timeouts and outbox are in the shared database.

Because the outbox table is stored in the shared catalog, the NHibernate persistence cannot access it when using the tenant connection string. Synonyms (a feature of SQL Server) provide a way to solve this problem. The following code creates synonyms for the OutboxRecord table in both tenant databases. These synonyms instruct the query processor to use the outbox table in the shared database whenever it encounters a reference to OutboxRecord.

var sql = @"
if exists (select * from sys.synonyms where [name] = 'OutboxRecord')
return;

create synonym OutboxRecord FOR [NHibernateMultiTenantReceiver].[dbo].[OutboxRecord]";
SqlHelper.ExecuteSql(Connections.TenantA, sql);
SqlHelper.ExecuteSql(Connections.TenantB, sql);

Configuring NHibernate persistence to recognize business entities

To be able to use NServiceBus-managed session to retrieve and store business entities, the NHibernate configuration used by NServiceBus needs to be appropriately configured.

var config = CreateNHibernateConfig();
var mapper = new ModelMapper();
mapper.AddMapping<OrderMap>();
config.AddMapping(mapper.CompileMappingForAllExplicitlyAddedEntities());

Connecting to the tenant database

To allow database isolation between tenants, the connection to the database needs to be created based on the message being processed. This requires the cooperation of two components:

• A behavior that inspects an incoming message and extracts the tenant's information
• A custom ConnectionProvider for NHibernate

The custom connection provider has to be registered with NHibernate

x.ConnectionProvider<MultiTenantConnectionProvider>();

The behavior retrieves the value of the tenant_id header and builds a connection string based on the header value. Then it stores the connection string in the async context via AsyncLocal.

if (!context.Message.Headers.TryGetValue("tenant_id", out var tenant))
{
    throw new InvalidOperationException("No tenant id");
}
var connectionString = Connections.GetTenant(tenant);

ConnectionStringHolder.Value = connectionString;
try
{
    await next();
}
finally
{
    ConnectionStringHolder.Value = null;
}

The behavior has to be registered in the pipeline configuration

pipeline.Register(
    behavior: typeof(ExtractTenantConnectionStringBehavior),
    description: "Extracts tenant connection string based on tenant ID header.");

When NHibernate needs to open a connection, the custom connection provider retrieves the connection string value from the async context and, if present, opens a connection to the tenant database. Otherwise, it opens a connection to the shared database.

var connectionString = ExtractTenantConnectionStringBehavior.ConnectionStringHolder.Value;
if (connectionString != null)
{
    var connection = Driver.CreateConnection();
    connection.ConnectionString = connectionString;
    connection.Open();
    return connection;
}

Propagating the tenant information downstream

In order to propagate the tenant information to the outgoing messages (including timeouts) this sample uses the same approach as the tenant information propagation sample: a pair of behaviors, one in the incoming pipeline and the other in the outgoing pipeline.