SQL Persistence

Project Hosting | Nuget: NServiceBus.Persistence.Sql (Version: 2.x)
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 and MySQL Community Edition, 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 MySQL support, Microsoft Premier Support or another third party support provider.

Usage

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

SQL Server

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

Edit
var transport = endpointConfiguration.UseTransport<MsmqTransport>();
transport.Transactions(TransportTransactionMode.SendsAtomicWithReceive);

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.

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 both the MS SQL Server Scripts and MySql 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

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

Scripts will be created in the directory format of [CurrentProjectDebugDir]\NServiceBus.Persistence.Sql\[SqlVariant].

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

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

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

To Produce only MS SQL Server scripts

Edit
[assembly: SqlPersistenceSettings(MsSqlServerScripts = true)]

To Produce only MySQL scripts

Edit
[assembly: SqlPersistenceSettings(MySqlScripts = 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 \.

Edit
[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.

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

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

Database Schema

A database schema can be defined in the configuration API as follows:

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

SqlStorageSession

The current DbConnection and DbTransaction can be accessed via the current context.

Using in a Handler

Edit
public class HandlerThatUsesSession :
    IHandleMessages<MyMessage>
{
    static ILog log = LogManager.GetLogger<HandlerThatUsesSession>();

    public Task Handle(MyMessage message, IMessageHandlerContext context)
    {
        var sqlPersistenceSession = context.SynchronizedStorageSession.SqlPersistenceSession();
        log.Info(sqlPersistenceSession.Connection.ConnectionString);
        // use Connection and/or Transaction of ISqlStorageSession to persist or query the database
        return Task.CompletedTask;
    }
}

Using in a Saga

Other than interacting with its own internal state, a saga should not access a database, call out to web services, or access other resources. See Accessing databases and other resources from a saga.

If the situation is special enough to warrant going against this recommendation, the following documentation will describe how to do so.

Edit
public class SagaThatUsesSession :
    SqlSaga<SagaThatUsesSession.SagaData>,
    IHandleMessages<MyMessage>
{
    public Task Handle(MyMessage message, IMessageHandlerContext context)
    {
        var sqlPersistenceSession = context.SynchronizedStorageSession.SqlPersistenceSession();
        log.Info(sqlPersistenceSession.Connection.ConnectionString);
        // use Connection and/or Transaction of ISqlStorageSession to persist or query the database
        return Task.CompletedTask;
    }

Samples

Related Articles


Last modified