Installation and deployment

Component: Sql Persistence
NuGet Package NServiceBus.Persistence.Sql (4.x)
Target NServiceBus Version: 7.x

SQL persistence outputs scripts to create necessary database assets. It's recommended to run those scripts as part of the deployment process. See Installer Workflow for more information.

Script execution during development

To streamline development, SQL persistence will execute generated scripts at endpoint startup if installers are enabled.

Copy code|Copy usings|Edit
endpointConfiguration.EnableInstallers();
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
Automatically executing generated scripts is recommended only in development environments.

Script execution in non-development environments

In non-development environments, where the SQL persistence installation scripts have been executed as part of the deployment, it may be necessary to explicitly disable the SQL persistence installers if standard installers need to be used for other purposes.

Copy code|Copy usings|Edit
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

Copy code|Copy usings|Edit
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

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

The default table prefix is Endpoint Name with all periods (.) replaced by underscores (_).

A table prefix is used at runtime and install time.

While the default table prefix can be inferred by code at runtime, 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.

Copy code|Copy usings|Edit
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 the selected SQL dialect for details.

The same value must be passed to the installation scripts as a parameter.

Manual installation

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

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

SQL Server

Copy code|Copy usings|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

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

Oracle

Copy code|Copy usings|Edit
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

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

Related Articles


Last modified