NHibernate persistence in multi-tenant systems

Component: NHibernate Persistence
NuGet Package NServiceBus.NHibernate (8.x)
Target NServiceBus Version: 7.x

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 data as well as timeout data are stored in a dedicated catalog shared by all tenants.

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

Prerequisites

An instance of SQL Server Express installed and accessible as .\SqlExpress.

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.
If used with a message transport that does not support native timeouts, timeout data is stored in a shared database so make sure to not include any sensitive information. Keep such information in saga data and only use timeouts as notifications.

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 makes sure that business entity and saga tables are created in the tenant databases while the timeouts and outbox -- in the shared database.

Because the outbox table is stored in the shared catalog, the NHibernate persistence is not able to 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

In order to be able to use NServiceBus-managed session to retrieve and store business entities, NHibernate configuration used by NServiceBus needs to be configured properly.

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 cooperation of two components:

  • A behavior that inspects an incoming message and extracts the tenant 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()
        .ConfigureAwait(false);
}
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.

Related Articles


Last modified