Getting Started
NServiceBus
Transports
Persistence
ServiceInsight
ServicePulse
ServiceControl
Monitoring
Previews
Samples

SQL Persistence - SQL Server dialect

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

This persistence will run on the free version of the database engine i.e. SQL Server Express. However, it is strongly recommended to use commercial versions for any production system. It is also recommended to ensure that support agreements are in place. See Microsoft Premier Support for details.

Supported database versions

SQL persistence supports SQL Server Version 2012. It does not work with lower versions due to the use of the THROW functionality.

Usage

Copy code|Copy usings|Edit
var connection = @"Data Source=.\SqlExpress;Initial Catalog=dbname;Integrated Security=True";
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var subscriptions = persistence.SubscriptionSettings();
subscriptions.CacheFor(TimeSpan.FromMinutes(1));
persistence.SqlDialect<SqlDialect.MsSqlServer>();
persistence.ConnectionBuilder(
    connectionBuilder: () =>
    {
        return new SqlConnection(connection);
    });
Microsoft SQL Server does not support DTC transactions in all deployment models (such as database mirroring or Always On configurations) and support differs between versions of SQL Server. See Transactions - Always On availability groups and Database Mirroring for more information.

Unicode support

SQL persistence itself supports Unicode characters, however data may become corrupted during saving if the database settings are incorrect. If Unicode support is required, follow the guidelines for each database engine. In particular set the correct character set and collation for databases storing persistence data.

Refer to the dedicated SQL Server documentation for details.

Supported name lengths

SQL persistence automatically generates names of database objects such as tables, indexes and procedures used internally. Every database engine has its own rules and limitations regarding maximum allowed name length.

SQL Server supports max. 128 characters.

The SQL persistence will not validate name length, for two reasons. Firstly, the supported name length values are higher and should be sufficient for typical scenarios. Secondly, it is possible to modify the database setting locally to support longer names. In case of long names for sagas, etc. the database engine may perform automatic name truncation.

Schema support

The SQL Server dialect supports multiple schemas. By default, when a schema is not specified, it uses the dbo schema when referring to database objects.

Copy code|Copy usings|Edit
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var dialect = persistence.SqlDialect<SqlDialect.MsSqlServer>();
dialect.Schema("MySchema");

Connection sharing

When an endpoint uses SQL Persistence combined with the SQL Server Transport without the Outbox, 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 of the message that triggered it.

When using the outbox, SQL Persistence always opens its own connection. In order to force using a separate connection even when the outbox is disabled, use the following API:

Copy code|Copy usings|Edit
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var dialect = persistence.SqlDialect<SqlDialect.MsSqlServer>();
dialect.DoNotUseSqlServerTransportConnection();
In this mode NServiceBus does not guarantee exactly-once message processing behavior which means that saga message handling logic might be called multiple times for a single incoming message in case the previous processing attempts failed just before consuming the message.

SQL Always Encrypted

The SQL Server Always Encrypted feature is not supported by this version of the persister when using the SQL Server dialect. The feature was added in SQL Persistence version 6.1.


Last modified

Feedback

In this article