# SQL Persistence in multi-tenant system


This sample demonstrates how to configure SQL Persistence to store tenant-specific data in separate databases, one for each tenant. The tenant-specific information includes saga state, and business entities that are accessed using [NServiceBus-managed session](/persistence/sql/accessing-data.md). In addition, the [Outbox](/nservicebus/outbox/index.md) is used to guarantee consistency between the saga state and the business entity. Outbox data is also stored in the tenant-specific database.

The sample assumes that 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 --name mssql -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:

 * `SqlMultiTenantA`
 * `SqlMultiTenantB`

## Running the project

 1. Start the Sender project (right-click on the project, select the `Debug > Start new instance` option).
 1. The text `Press <enter> to send a message` should be displayed in the Sender's console window.
 1. Start the Receiver project (right-click on the project, select the `Debug > Start new instance` option).
 1. The Sender should display subscription confirmation `Subscribe from Receiver on message type OrderSubmitted`.
 1. 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.
 1. The Sender displays information that the order was accepted.
 1. Finally, after a couple of seconds, the Receiver displays confirmation that the timeout message has been received.
 1. 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.

> [!WARNING]
> 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 SQL Persistence installers create all schema objects in a single database. In multi-tenant scenarios schema objects need to be created manually. The `ScriptRunner` class provides the required APIs to run schema creation scripts.

This code snippet makes sure that business entity and saga tables are created in the tenant databases.

<!-- snippet: CreateSchema -->

```cs
await ScriptRunner.Install(dialect, tablePrefix, () => new SqlConnection(Connections.TenantA), scriptDirectory,
    shouldInstallOutbox: true,
    shouldInstallSagas: true,
    shouldInstallSubscriptions: false,
    cancellationToken: CancellationToken.None);

await ScriptRunner.Install(dialect, tablePrefix, () => new SqlConnection(Connections.TenantB), scriptDirectory,
    shouldInstallOutbox: true,
    shouldInstallSagas: true,
    shouldInstallSubscriptions: false,
    cancellationToken: CancellationToken.None);
```

<!-- endsnippet -->

Due to the Outbox tables being stored in multiple databases (one per tenant), SQL Persistence is not able to automatically clean Outbox entries. This setting must be confirmed by disabling Outbox cleanup:

<!-- snippet: DisablingOutboxCleanup -->

```cs
var outboxSettings = endpointConfiguration.EnableOutbox();
outboxSettings.DisableCleanup();
```

<!-- endsnippet -->

The Outbox tables on each tenant database must be [cleaned by an outside process like SQL Agent](/persistence/sql/multi-tenant.md#disabling-outbox-cleanup).


#### Connecting to the tenant database

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

 * [Pipeline behaviors](/nservicebus/pipeline/manipulate-with-behaviors.md) to extract the tenant information from a message header and ensures that it is propagated to any outgoing messages generated during processing
 * A [tenant-aware connection factory](/persistence/sql/multi-tenant.md#specifying-connections-per-tenant) for SQL Persistence

The connection factory retrieves the value of the `tenant_id` header and builds a connection string based on the header value.

<!-- snippet: ConnectionFactory -->

```cs
persistence.MultiTenantConnectionBuilder(tenantIdHeaderName: "tenant_id",
    buildConnectionFromTenantData: tenantId =>
    {
        var connectionString = Connections.GetForTenant(tenantId);
        return new SqlConnection(connectionString);
    });
```

<!-- endsnippet -->

When SQL Persistence needs to open a connection, the connection factory is called using the value extracted from the message. As an alternative, other connection factory options exist that allow [consulting multiple headers to extract tenant information](/persistence/sql/multi-tenant.md#specifying-connections-per-tenant).

#### 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](/samples/multi-tenant/propagation/index.md): a pair of behaviors, one in the incoming pipeline and the other in the outgoing pipeline.