Transactional Session with SQL Persistence

In order to use the transactional session feature with SQL Persistence, add a reference to the NServiceBus.Persistence.Sql.TransactionalSession NuGet package.

Configuration

To enable the transactional session feature:

var persistence = config.UsePersistence<SqlPersistence>();
persistence.EnableTransactionalSession();

Opening a session

To open a SQL Persistence transactional session:

using var childScope = serviceProvider.CreateScope();
var session = childScope.ServiceProvider.GetService<ITransactionalSession>();
await session.Open(new SqlPersistenceOpenSessionOptions());

// use the session

await session.Commit();

Multi-tenancy support

The specific tenant ID that is used to construct the connection string is retrieved from message headers as configured in the MultiTenantConnectionBuilder-method. This header needs to be set in the options so that the necessary information is available when storing operations and interacting with the outbox.

using var childScope = serviceProvider.CreateScope();
var session = childScope.ServiceProvider.GetService<ITransactionalSession>();
await session.Open(
        new SqlPersistenceOpenSessionOptions((
                     "MyTenantIdHeader", // Name of the header configured in this endpoint to carry the tenant ID
                     "TenantA"))); // The value of the tenant ID header

// use the session

await session.Commit();

Transaction usage

Message and database operations made via the transactional session are committed together once the session is committed:

await session.Open(new SqlPersistenceOpenSessionOptions());

// add messages to the transaction:
await session.Send(new MyMessage());

// access the database:
var sqlSession = session.SynchronizedStorageSession.SqlPersistenceSession();

await session.Commit();

See the SQL shared session documentation for further details about using the transaction.

Remote processor

When the transactional session is configured with a remote processor, the send-only endpoint writes outbox records to the processor endpoint's outbox table, [schema].[{tablePrefix}OutboxData]. The send-only endpoint doesn't create or clean up an outbox table of its own.

The send-only endpoint works out the table name from its own configuration. The table prefix defaults to the processor endpoint name, with periods replaced by underscores, rather than the send-only endpoint name. To make sure the send-only endpoint writes to the table that the processor reads:

• Connect both endpoints to the same database.
• Use the same schema on both endpoints.
• If the processor endpoint has a custom table prefix, configure the same prefix on the send-only endpoint. Otherwise, don't configure a table prefix on the send-only endpoint.