SQL Persistence

Project Hosting
NuGet Package NServiceBus.Persistence.Sql (2.1)
Target NServiceBus Version: 6.x

The SQL Persistence uses Json.NET to serialize data and store in a SQL database.

Supported SQL Implementations

Although this persistence will run on the free version of the above engines, i.e. SQL Server Express, MySQL Community Edition, and Oracle XE, it is strongly recommended to use commercial versions for any production system. It is also recommended to ensure that support agreements are in place from either Microsoft Premier Support, MySQL support, Oracle Support, or another third party support provider.

Supported name lengths

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

In case of Oracle SQL persistence will throw an exception in case the name length is too long. In case of database engines other than Oracle, the SQL persistence will not validate name length, for two reasons. Firstly, the supported name length value is higher and should be sufficient for typical scenarios. Secondly, it is possible to modify the setting locally to support longer names. In case of long names for sagas, etc. the database engine may perform automatic name truncation.

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 MySQL, SQL Server or Oracle documentation for details.

Usage

Install the NServiceBus.Persistence.Sql and NServiceBus.Persistence.Sql.MsBuild NuGet packages.

SQL Server

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
var connection = @"Data Source=.\SqlExpress;Initial Catalog=dbname;Integrated Security=True";
persistence.SqlVariant(SqlVariant.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.

MySQL

Using the MySql.Data NuGet Package.

var connection = "server=localhost;user=root;database=dbname;port=3306;password=pass;AllowUserVariables=True;AutoEnlist=false";
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.SqlVariant(SqlVariant.MySql);
persistence.ConnectionBuilder(
    connectionBuilder: () =>
    {
        return new MySqlConnection(connection);
    });

The following settings are required for MySQL connections string.

  • AllowUserVariables=True: since the Persistence uses user variables.
  • AutoEnlist=false: To prevent auto enlistment in a Distributed Transaction which the MySql .NET connector does not currently support.

Oracle

Using the Oracle.ManagedDataAccess NuGet Package.

var connection = "Data Source=localhost;User Id=username;Password=pass;Enlist=false;";
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.SqlVariant(SqlVariant.Oracle);
persistence.ConnectionBuilder(
    connectionBuilder: () =>
    {
        return new OracleConnection(connection);
    });
The ODP.NET managed driver requires Enlist=false or Enlist=dynamic setting in the Oracle connection string to allow the persister to enlist in a Distributed Transaction at the correct moment.

NuGet Packages

The SQL Persistence consists of several Nuget Packages.

NServiceBus.Persistence.Sql.MsBuild

This packages installs into the MSBuild pipeline and generates all SQL installation scripts at compile time. It does this by interrogating types (in the target assembly) and attributes (from the NServiceBus.Persistence.Sql NuGet package) to infer what scripts to create. It is required for any project where those SQL installation scripts are required. For Saga Scripts it will be any project that contains Saga classes. For Timeouts, Subscriptions and Outbox Scripts it will be the endpoint hosting project. This package has a dependency on the NServiceBus.Persistence.Sql NuGet package

NServiceBus.Persistence.Sql

This package contains several parts

  • APIs for manipulating EndPointConfiguration at configuration time.
  • Runtime implementations of Saga, Timeouts, Subscriptions and Outbox Persisters.
  • Attribute definitions used to define certain compile time configuration settings. These attributes are then interrogated by the NServiceBus.Persistence.Sql.MsBuild NuGet Package when generating SQL installation scripts
  • Optionally runs SQL installation scripts at endpoint startup for development purposes. See Installer Workflow.

NServiceBus.Persistence.Sql.ScriptBuilder

This package contains all APIs that enable the generation of SQL installation scripts using code, i.e. without using the NServiceBus.Persistence.Sql.MsBuild NuGet package.

NServiceBus.Persistence.Sql.ScriptBuilder is currently not ready for general usage. It has been made public, and deployed to NuGet, primarily to enable the generation of documentation in a repeatable way. For example it is used to generate the SQL scripts in the MS SQL Server Scripts, MySql Scripts, and Oracle Scripts pages. In future releases, the API may evolve in ways that do not follow the standard of Release Policy - Semantic Versioning. Raise an issue in the NServiceBus.Persistence.Sql Repository to discuss this in more detail.

Script Creation

Projects using project.json are not supported. The project.json approach was an experiment by Microsoft at a new project system that was not based on MSBuild. Since project.json did not support running MSBuild files shipped inside a NuGet the SQL Persistence Script Creation does not work. This experiment has since been abandoned. To fix this either migrate back to the old Visual Studio 2015 project format (.csproj and packages.config) or migrate to the new Visual Studio 2017 project format. dotnet-migrate can help migrating to the new .csproj format.

SQL installation scripts are created at compile time by the NServiceBus.Persistence.Sql.MsBuild NuGet package.

For example for a project named ClassLibrary build in Debug mode the following directories will be created.

  • ClassLibrary\bin\Debug\NServiceBus.Persistence.Sql\MsSqlServer
  • ClassLibrary\bin\Debug\NServiceBus.Persistence.Sql\MySql
  • ClassLibrary\bin\Debug\NServiceBus.Persistence.Sql\Oracle

Scripts will also be included in the list of project output files. So this means those files produced will be copied to the output directory of any project that references it.

Scripts creation can configured via the use of [SqlPersistenceSettings] applied to the target assembly.

To Produce All scripts

[assembly: SqlPersistenceSettings(
    MsSqlServerScripts = true,
    MySqlScripts = true,
    OracleScripts = true)]

To produce only MS SQL Server scripts

[assembly: SqlPersistenceSettings(MsSqlServerScripts = true)]

To produce only MySQL scripts

[assembly: SqlPersistenceSettings(MySqlScripts = true)]

To produce only Oracle scripts

[assembly: SqlPersistenceSettings(OracleScripts = true)]

Promotion

As stated above, scripts are created in the target project output directory. Generally this directory will be excluded from source control. To add created scripts to source control they can be "promoted".

The target directory will be deleted and recreated as part of each build. So ensure to choose a path that is for script promotion only.

Some token replacement using MSBuild variables is supported.

  • $(SolutionDir): The directory of the solution.
  • $(ProjectDir): The directory of the project

All tokens are drive + path and include the trailing backslash \.

[assembly: SqlPersistenceSettings(
    ScriptPromotionPath = "$(SolutionDir)PromotedSqlScripts")]

The path calculation is performed relative to the current project directory. So, for example, a value of PromotedSqlScripts (with no tokens) would evaluate as $(ProjectDir)PromotedSqlScripts.

Installation

The SQL persistence enables creation of scripts that can be run as a part of a deployment process instead of as part of endpoint startup as with standard installers. See Installer Workflow for more information.

To streamline development SQL persistence installers are, by default, executed at endpoint startup, in the same manner as all other installers. However in higher level environment scenarios, where standard installers are being run, but the SQL persistence installation scripts have been executed as part of a deployment, it may be necessary to explicitly disable the SQL persistence installers executing while leaving standard installers enabled.

endpointConfiguration.EnableInstallers();
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.DisableInstaller();

Table Prefix

Table prefix is the string that is prefixed to every table name, i.e. Saga, Outbox, Subscription and Timeout tables.

The default TablePrefix is Endpoint Name with all periods (.) replaced with underscores (_).

A Table Prefix is required at runtime and install time.

When using the default (execute at startup) approach to installation the value configured in code will be used.

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.TablePrefix("ThePrefix");

Database Schema

When using Microsoft SQL Server, a database schema other than the default dbo can be defined in the configuration API as follows:

var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.Schema("MySchema");

Note that the same value will need to be passed to the SQL installation scripts as a parameter.

Manual installation

When performing a custom script execution the TablePrefix is required. See also Installer Workflow.

Note that scriptDirectory can be either the root directory for all scripts for, alternatively, the specific locations for a given storage type i.e. Saga, Outbox, Subscription and Timeout scripts.

SQL Server

using (var connection = new SqlConnection("ConnectionString"))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        foreach (var createScript in Directory.EnumerateFiles(
            path: scriptDirectory,
            searchPattern: "*_Create.sql",
            searchOption: SearchOption.AllDirectories))
        {
            using (var command = connection.CreateCommand())
            {
                command.Transaction = transaction;
                command.CommandText = File.ReadAllText(createScript);
                var tablePrefixParameter = command.CreateParameter();
                tablePrefixParameter.ParameterName = "tablePrefix";
                tablePrefixParameter.Value = tablePrefix;
                command.Parameters.Add(tablePrefixParameter);
                var schemaParameter = command.CreateParameter();
                schemaParameter.ParameterName = "schema";
                schemaParameter.Value = "dbo";
                command.Parameters.Add(schemaParameter);
                command.ExecuteNonQuery();
            }
        }
        transaction.Commit();
    }
}

MySQL

using (var connection = new MySqlConnection("ConnectionString"))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        foreach (var createScript in Directory.EnumerateFiles(
            path: scriptDirectory,
            searchPattern: "*_Create.sql",
            searchOption: SearchOption.AllDirectories))
        {
            using (var command = connection.CreateCommand())
            {
                command.Transaction = transaction;
                command.CommandText = File.ReadAllText(createScript);
                var parameter = command.CreateParameter();
                parameter.ParameterName = "tablePrefix";
                parameter.Value = tablePrefix;
                command.Parameters.Add(parameter);
                command.ExecuteNonQuery();
            }
        }
        transaction.Commit();
    }
}

Oracle

using (var connection = new OracleConnection("ConnectionString"))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        foreach (var createScript in Directory.EnumerateFiles(
            path: scriptDirectory,
            searchPattern: "*_Create.sql",
            searchOption: SearchOption.AllDirectories))
        {
            using (var command = connection.CreateCommand())
            {
                command.Transaction = transaction;
                command.CommandText = File.ReadAllText(createScript);
                var parameter = command.CreateParameter();
                parameter.ParameterName = "tablePrefix";
                parameter.Value = tablePrefix;
                command.Parameters.Add(parameter);
                command.ExecuteNonQuery();
            }
        }
        transaction.Commit();
    }
}

Samples

Related Articles


Last modified