Installation and deployment

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.

Script execution runs by default at endpoint startup

To streamline development SQL persistence installers are, by default, executed at endpoint startup, in the same manner as all other installers.

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

Note that this is also a valid approach for higher level environments.

Optionally take control of script execution

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

The ScriptRunner class can be used to run the scripts without creating and starting an NServiceBus endpoint

await ScriptRunner.Install(
    sqlDialect: new SqlDialect.MsSqlServer(),
    tablePrefix: "MyEndpoint",
    connectionBuilder: () => new SqlConnection(connectionString), 
    scriptDirectory: @"C:\Scripts",
    shouldInstallOutbox: true,
    shouldInstallSagas: true,
    shouldInstallSubscriptions: true,
    shouldInstallTimeouts: true);

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 used at runtime and install time.

While the above default Table Prefix at runtime can be inferred by code, it cannot be inferred when running deployment scripts manually and as such much be passed in as a parameter.

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 a database that supports schemas, a schema value other than default can be defined in the configuration API. Consult the documentation of selected SQL dialect for details.

The same value will need to be passed to the 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();
    }
}

PostgreSQL

using (var connection = new NpgsqlConnection("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();
    }
}