Getting Started
Architecture
NServiceBus
ServiceInsight
ServicePulse
ServiceControl
Monitoring
Samples

Combining MS SQL Server Persistence and Transport

Component: Sql Persistence
Target Version: NServiceBus 8.x

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

SQL Transport
TransactionMode
Connection sharingSaga location
TransactionScopeNot supportedN/A
AtomicSendsWithReceiveNot supportedN/A
ReceiveOnlyConnection sharing via SQL Transport storage contextPersistence DB
NoneNot supportedN/A

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

Without Outbox

SQL Transport
TransactionMode
Connection sharingSaga location
TransactionScopeSQL Transport transaction is promoted to distributed transactionPersistence DB 1
SendsAtomicWithReceiveSQL Transport uses isolated transaction for send and receiveTransport DB
ReceiveOnlySQL Transport uses isolated transaction for receiveTransport DB
NoneNo transactionsPersistence DB

1 - 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();

Related Articles