How do I change the Saga data persistence location when outbox is not enabled?

NServiceBus Version: 8

Hello,

We are looking to enable Outbox on our services, but before that we are looking into some pre-outbox preparation first.

Specifically, we want to migrate all our Saga data to our application databases (SQL) and remove it from our SQL message broker, which exists in a separate db server.

However, we noticed that the database connectionstring for the Saga data persistence is not used when Outbox is not enabled. We configured it using:

var persistence = configuration.UsePersistence<SqlPersistence>()
persistence.ConnectionBuilder(connectionBuilder: () => new SqlConnection("app_db_connection"));

It does use the correct connectionstring to the app_db when Outbox is enabled. Upon further investigation, we found the relevant code (line:145) in the SagaPersistenceBehavior from the NServiceBus github repository.

It appears to use the context.SynchronizedStorageSession when calling sagaPersister.Save, which connects using the database connectionstring for the transport layer. This would store the data in the SQL message broker database in our case and not the application database.

Question: Is it possible to change the connectionstring for the Saga data persistence to the application database when Outbox is not enabled?

And is NServiceBus designed to allow this or must we store the saga data in the SQL message broker until Outbox is enabled? What are the design guidelines for this situation?

Hi DevJ,

To achieve what you are trying to do, you would need to set the TransportTransactionMode to TransactionScope and have DTC enabled.

The other alternative (if you are not able to use DTC) is to set the TransportTransactionMode to None, but that is dangerous and not recommended.

You are also able to enable the outbox. Can you elaborate on the reason why you want to migrate the saga data first before doing that?

Hi,

We are running our systems in .NET 6 and 8. Does NServiceBus.Transport.SqlServer (7.x) support TransportTransactionMode.TransactionScope for .NET? The documentation is a bit unclear on this, as it is not supported for .NET Core.

In our case, we also receive an exception, containing:

The version of the SqlClient in use does not support enlisting SQL connections in distributed transactions. […] In case the problem is related to distributed transactions you can still use SQL Server transport but should specify a different transaction mode […]

Regarding the other question:

[…] Can you elaborate on the reason why you want to migrate the saga data first before doing that?

We use NServiceBus for a large volume of communication that may span between service-boundaries, which are maintained by multiple separate dedicated teams. We need a minimal window of time where outbox and non-outbox services are in communication, to minimize the possibility of duplicate messages and erroneous states of business data.

Because of that, our intention was to toggle and enable outbox for all endpoints at the same time and, in the event of system failure, rollback outbox and start crash recovery processes.

The location of Saga data persistence is one of the concerns that we want to address to simplify the transition to outbox. When enabling Outbox, the Saga data will no longer be persisted in the SQL message broker (Transport layer) but in the application database (Persistence layer). This poses a risk, because Saga messages may then no longer be able to map Saga data to an incoming command. After all, a different database will be queried for Saga data.

This introduces a problem. There may be tools that allow us to migrate Saga data from the SQL broker database to an application database. And we could then manually retry one of the commands in the Saga. However, in the event of a rollback (disable outbox), we would then need to migrate the Saga data back which is non-feasible. We also can not restart a Saga, as business entities may already have been altered.

Because of this, we believe the easier solution would be to perform the outbox transition in phases rather than performing the transition in a single large ‘big bang’. And one of the phases is to first migrate Saga data to the application database and then handle any problems that may occur. We would then enable outbox at a later date, and handle any problems that may occur there.

Above was our reasoning for the outbox transition. We did read the rollout strategy in the docs, however there are many endpoints in our systems that fall under point 3 as a large amount of endpoints are sending endpoints and some sending endpoints even communicate with each other. It would also be difficult to have the dev teams coordinate with each other and determine which endpoints can operate under outbox without posing undesirable risks to non-outbox services.

If you have any tips, we would love to hear it!

It is supported in .NET 8, but not .NET 6.

You would also need to enable implicit distributed transactions

System.Transactions.TransactionManager.ImplicitDistributedTransactions = true;

I’m not sure which version introduced it, but it works with Microsoft.Data.SqlClient 5.2.1.

Which TransportTransactionMode are you currently using? The persistence behavior you’re describing is likely unintended and we are investigating.

We are using Microsoft.Data.SqlClient major version 3 for NServiceBus.Persistence.Sql v7. I could try to upgrade the SqlClient to major version 5. However, our services must remain on .NET 6 for the near future so I don’t think TransportTransactionMode.TransactionScope would work if it does not support .NET 6.

Which TransportTransactionMode are you currently using? […]

We got the exception above when we tried to configure the .NET 6 endpoint to use TransportTransactionMode.TransactionScope. We normally configure the endpoint (for .NET 6) to use TransportTransactionMode.SendsAtomicWithReceive.

@DevJ

To achieve what you want with TransportTransactionMode.SendsAtomicWithReceive, you can add the following code:

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

Reference: SQL Persistence - SQL Server dialect • Sql Persistence • Particular Docs