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

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

Using either the System.Data.SqlClient or Microsoft.Data.SqlClient NuGet packages.

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.

Token-credentials

Microsoft Entra ID authentication is supported via the standard connection string options.

Microsoft Entra ID authentication is only supported when using Microsoft.Data.SqlClient

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");

SQL Always Encrypted

The SQL Server dialect has support for SQL Server Always Encrypted.

Always Encrypted support works only with Microsoft.Data.SqlClient.

The steps to use SQL Always Encrypted are:

Make sure SQL Always Encrypted is configured with the correct certificate or key stores on the database engine and the client machines.
Encrypt the Body column for the saga table that encryption is being enabled for. For more information on how to encrypt columns in SQL Server, refer to the Microsoft documentation.
Encrypt the Operations column for the OutboxData table. This contains business data in the form of outgoing messages. There is a separate OutboxData table for every endpoint that uses the outbox feature.
Ensure the connection string for the endpoint includes the Column Encryption Setting = Enabled; connection string parameter.

The Body and Operations columns will now be readable only by clients that have the correct certificate or key stores configured.

Encrypting columns requires a few parameters including the type of encryption, the algorithm and the key. Installers currently do not support encryption. Therefore, installers cannot be enabled in combination with SQL Server Always Encrypted.

SQL Persistence - SQL Server dialect