Multi-tenant support

Component: Sql Persistence
NuGet Package NServiceBus.Persistence.Sql (4.x)
Target NServiceBus Version: 7.x

When working in a multi-tenant system, the data for each customer/client (tenant) is stored in an independent database identified by one or more message headers passed along with each message flowing through the system. With SQL Persistence running in multi-tenant mode, saga data and outbox data are both stored in the same database as the tenant data, requiring only one database connection and transaction for the duration of the message handler.

Timeout data and subscription data do not belong to any specific tenant and are stored in a shared database in cases where the message transport does not provide native timeouts or native publish/subscribe capabilities.

Specifying connections per tenant

If the tenant information is propagated in a single header that does not change, multi-tenancy can be enabled by specifying the header name and a callback to create a connection given a tenant id:

4.5 NServiceBus.Persistence.Sql
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.MultiTenantConnectionBuilder(tenantIdHeaderName: "TenantHeaderName",
    buildConnectionFromTenantData: tenantId =>
    {
        var connection = $@"Data Source=.\SqlExpress;Initial Catalog=DatabaseForTenant_{tenantId};Integrated Security=True";
        return new SqlConnection(connection);
    });

In more complex situations, where the tenant id must be calculated by consulting multiple headers, or where a transition from an old header name to a new header name is occurring, a callback can be provided that captures the tenant id from the incoming message.

In this example, the header NewTenantHeaderName is consulted first, with OldTenantHeaderName as a backup.

4.5 NServiceBus.Persistence.Sql
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.MultiTenantConnectionBuilder(captureTenantId: incomingMessage =>
    {
        if (incomingMessage.Headers.TryGetValue("NewTenantHeaderName", out var tenantId) || incomingMessage.Headers.TryGetValue("OldTenantHeaderName", out tenantId))
        {
            return tenantId;
        }

        return null;
    },
    buildConnectionFromTenantData: tenantId =>
    {
        var connection = $@"Data Source=.\SqlExpress;Initial Catalog=DatabaseForTenant_{tenantId};Integrated Security=True";
        return new SqlConnection(connection);
    });
A null tenant id indicates a failure to propagate the tenant id from a previous message, rendering the message invalid. However, it is safe to return null in this callback because SQL Persistence will throw an exception if a null tenant id is encountered, rather than trying to process a message without a tenant id.

Disabling Outbox cleanup

When using the Outbox feature with a single database, the endpoint will clean up its own deduplication data. When using multi-tenant mode, it's impossible for the endpoint to know all the possible tenant databases it must clean up. If using the Outbox with multi-tenant mode, the cleanup process must be disabled and implemented as a SQL Agent (or similar) task, otherwise the following exception will be thrown at runtime:

MultiTenantConnectionBuilder can only be used with the Outbox feature if Outbox cleanup is handled by an external process (i.e. SQL Agent) and the endpoint is configured to disable Outbox cleanup using endpointConfiguration.EnableOutbox().DisableCleanup(). See the SQL Persistence documentation for more information on how to clean up Outbox tables from a scheduled task.

This opt-in approach ensures the user is not taken by surprise by the need to self-clean the Outbox tables. This approach also gives the advantage of being able to schedule the cleanup process to a slow time of day for that customer, or to optimize performance by running Outbox cleanup right before rebuilding database indexes.

To disable the Outbox cleanup so that multi-tenant mode can be used with the Outbox enabled, add this configuration:

4.5 NServiceBus.Persistence.Sql
var outboxSettings = endpointConfiguration.EnableOutbox();
outboxSettings.DisableCleanup();

In order to perform the cleanup on each database, a script similar to the following Microsoft SQL Server example should be run:

declare @BatchSize int = 5000
declare @DispatchedBefore datetime = dateadd(day, -7, getutcdate())

while 1=1
begin

	set rowcount @BatchSize
	delete from ENDPOINTNAME_OutboxData
	where Dispatched = 'true' and
		  DispatchedAt < @DispatchedBefore

	if @@ROWCOUNT < @BatchSize
		break;
end

Since each endpoint uses its own Outbox table on the database, a database cursor over Outbox table names can be used to clean all endpoints' outbox tables at once:

declare @SchemaAndName varchar(256)
declare @sql nvarchar(max)
declare @BatchSize int = 5000

declare OutboxTableCursor cursor for
select '[' + schema_name(schema_id) + '].[' + name + ']' as SchemaAndName
from sys.tables
where name like '%_OutboxData'

open OutboxTableCursor

fetch next from OutboxTableCursor into @SchemaAndName

while @@FETCH_STATUS = 0
begin

	print 'Cleaning ' + @SchemaAndName

	set @sql = N'

		declare @DispatchedBefore datetime = dateadd(day, -7, getutcdate())

		while 1=1
		begin

			delete top(' + cast(@BatchSize as varchar(50)) + ') from ' + @SchemaAndName + '
			where Dispatched = ''true'' and
				  DispatchedAt < @DispatchedBefore

			if @@ROWCOUNT < ' + cast(@BatchSize as varchar(50)) + '	
				break;
		end'

	execute sp_executesql @sql

	fetch next from OutboxTableCursor into @SchemaAndName
end

close OutboxTableCursor
deallocate OutboxTableCursor

The cleanup script would be similar on other database engines. Refer to the default Outbox cleanup scripts for MySQL, PostgreSQL, and Oracle to get an idea of the operation that needs to be scripted.

Propagating tenant id headers

In order for a system to be multi-tenant, every endpoint must use an NServiceBus pipeline behavior so that every message handler will copy the tenant id header(s) from each incoming message to any outgoing message that message handler creates.

If such a behavior does not exist, it will result in the endpoint being unable to determine the tenant id from an incoming message, and this exception will be thrown:

This endpoint attempted to process a message in multi-tenant mode and was unable to determine the tenant id from the incoming message. As a result SQL Persistence cannot determine which tenant database to use. Either: 1) The message lacks a tenant id and is invalid. 2) The lambda provided to determine the tenant id from an incoming message contains a bug. 3) Either this endpoint or another upstream endpoint is not configured to use a custom behavior for relaying tenant information from incoming to outgoing messages, or that behavior contains a bug.

Refer to the Propagating Tenant Information to Downstream Endpoints sample to see how to create and register pipeline behaviors to propagate the tenant id to downstream endpoints.

Connections for timeouts and subscriptions

When using multi-tenant mode, storage for timeouts and subscriptions are still stored in a single database if the message transport does not provide those features (delayed delivery and publish/subscribe) natively.

If these persistence features are used, but a connection builder is not specified, the following exception will be thrown:

Couldn't find connection string for . The connection to the database must be specified using the ConnectionBuilder method. When in multi-tenant mode with MultiTenantConnectionBuilder, you must still use ConnectionBuilder to provide a database connection for subscriptions/timeouts on message transports that don't support those features natively.

To specify the connection builder for timeouts or subscriptions, refer to the usage documentation for Microsoft SQL, MySQL, PostgreSQL, or Oracle.

When using a transport with both native delayed delivery and native timeouts, this is not required and no exception will be thrown.

Cannot use installers

Because tenant databases are not known at endpoint startup, it's impossible for NServiceBus to run installers to create table structures in each tenant database.

When using multi-tenant endpoints it's advisable to:

  1. Use script promotion to copy DDL scripts outside of the runtime directory and commit them to source control.
  2. Create a process to execute the DDL scripts against all required databases as part of each deployment.

Samples

Related Articles


Last modified