Combining MS SQL Server Persistence and Transport

Connection behavior

When combining SQL Server transport and SQL persistence using the Sql dialect, the connection behaves differently based on whether the Outbox is enabled or disabled. This influences where the saga data is stored.

With Outbox

When using the outbox, SQL Persistence always opens its own connection.

Without Outbox

¹ - Requires .NET Framework, or .NET 8 ImplicitDistributedTransactions set to true.

Explicitly opting out of connection sharing when not using the Outbox

When an endpoint uses SQL Persistence combined with the SQL Server Transport with Outbox disabled, the persistence uses the connection and transaction context established by the transport when accessing saga data. This behavior ensures exactly-once message processing behavior as the state change of the saga is committed atomically while consuming the message that triggered it.

In order to force using a separate connection use the following API:

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var dialect = persistence.SqlDialect<SqlDialect.MsSqlServer>();
dialect.DoNotUseSqlServerTransportConnection();