SQL Persistence

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

Supported SQL Implementations

• SQL Server (Version 2012 and above due to the use of the THROW functionality).
• MySQL (Version 5.7 and above due to the use of JSON Data Type).
• Oracle (Version 11g Release 2 and above).
  • Due to the 30-character limit on table, key, and index names, there are some Oracle-specific caveats to consider.

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:

• Oracle 12.2 and below supports max. 30 characters, see the Oracle Caveats article to learn more.
• MySQL supports max. 64 characters.
• SQL Server supports max. 128 characters.

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

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

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

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.

Script Creation

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".

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