Connection Settings

Component: SQL Server Transport
NuGet Package NServiceBus.SqlServer (4.x)
Target NServiceBus Version: 7.x

Using connection pool

The SQL Server transport is built on top of ADO.NET and will use connection pooling. This may result in the connection pool being shared by the transport, as well as other parts of the endpoint process and the business logic.

In scenarios where the concurrent message processing limit is changed, or the database connection is used for other purposes as mentioned above, it is advisable to change the connection pool size to ensure it will not be exhausted. See also SQL Server Connection Pooling and Configuration.

If the maximum pool size (and minimum pool in 3.0.x) is not explicitly set on the connection string a warning message will be logged. See also Tuning endpoint message processing

Connection configuration

Connection string can be configured in several ways:

Via the configuration API

By using the ConnectionString extension method:

var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
transport.ConnectionString(
    "Data Source=instance;Initial Catalog=db;Integrated Security=True;Max Pool Size=80");

Via the App.Config

By adding a connection named NServiceBus/Transport in the connectionStrings node.

<configuration>
  <connectionStrings>
     <add name="NServiceBus/Transport"
          connectionString="Data Source=instance; Initial Catalog=db; Integrated Security=True;Max Pool Size=80"/>
  </connectionStrings>
</configuration>

Custom database schemas

SQL Server transport uses dbo as a default schema. Default schema is used for every queue if no other schema is explicitly provided in transport address. That includes all local queues, error, audit and remote queues of other endpoints.

The default schema can be overridden using DefaultSchema method:

var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
transport.DefaultSchema("myschema");

Custom SQL Server transport connection factory

In some environments it might be necessary to adapt to database server settings, or to perform additional operations. For example, if the NOCOUNT setting is enabled on the server, then it is necessary to send the SET NOCOUNT OFF command right after opening the connection.

That can be done by passing the transport a custom factory method which will provide connection strings at runtime, and which can perform custom actions:

var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
transport.UseCustomSqlConnectionFactory(
    sqlConnectionFactory: async () =>
    {
        var connection = new SqlConnection("SomeConnectionString");
        try
        {
            await connection.OpenAsync()
                .ConfigureAwait(false);

            // perform custom operations

            return connection;
        }
        catch
        {
            connection.Dispose();
            throw;
        }
    });
If opening the connection fails, the custom connection factory must dispose the connection object and rethrow the exception.

Circuit Breaker

A built in circuit breaker is used to handle intermittent SQL Server connectivity problems. When a failure occurs when trying to connect, a circuit breaker enters an armed state. If the failure is not resolved before the configured wait time elapses, the circuit breaker triggers the critical errors handling procedure.

Wait time

Overrides the default time to wait of the circuit breaker. The default value is 2 minutes.

var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
transport.TimeToWaitBeforeTriggeringCircuitBreaker(TimeSpan.FromMinutes(3));

Last modified