Connection Settings

Component: SQL Server Transport
NuGet Package NServiceBus.SqlServer (2.x)
Target NServiceBus Version: 5.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.

Connection configuration

Connection string can be configured in several ways:

Via the configuration API

By using the ConnectionString extension method:

var transport = busConfiguration.UseTransport<SqlServerTransport>();
transport.ConnectionString("Data Source=INSTANCE_NAME;Initial Catalog=some_database");

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_NAME; Initial Catalog=some_database; Integrated Security=True"/>
  </connectionStrings>
</configuration>

Via a named connection string

By using the ConnectionStringName extension method:

var transport = busConfiguration.UseTransport<SqlServerTransport>();
transport.ConnectionStringName("MyConnectionString");

Combined this with a named connection in the connectionStrings node of the app.config file:

<configuration>
  <connectionStrings>
    <add name="MyConnectionString"
         connectionString="Data Source=INSTANCE_NAME; Initial Catalog=some_database; Integrated Security=True; Queue Schema=nsb"/>
  </connectionStrings>
</configuration>

Multiple connection strings

In multi-catalog and multi-instance modes additional configuration is required for proper message routing:

  • The sending endpoint needs to know the connection string of the receiving endpoint.
  • The replying endpoint needs to know the connection string of the originator of the message for which the reply is being sent
  • The subscribing endpoint needs to know the connection string of the publishing endpoint, in order to send subscription request.
  • The publishing endpoint needs to know the connection strings or all the subscribed endpoints

Connection strings for the remote endpoint can be configured in several ways:

Via the configuration API - Push mode

In push mode the whole collection of endpoint connection information objects is passed during configuration time.

2.1 NServiceBus.SqlServer
var transport = busConfiguration.UseTransport<SqlServerTransport>();
transport.UseSpecificConnectionInformation(
    EndpointConnectionInfo.For("RemoteEndpoint")
        .UseSchema("schema1")
        .UseConnectionString("SomeConnectionString"),
    EndpointConnectionInfo.For("AnotherEndpoint")
        .UseSchema("schema2")
        .UseConnectionString("SomeOtherConnectionString")
    );

Via the configuration API - Pull mode

Pull mode can be used when specific information is not available at configuration time. One can pass a function that will be used by the SQL Server transport to resolve connection information at runtime.

2.1 NServiceBus.SqlServer
var transport = busConfiguration.UseTransport<SqlServerTransport>();
transport.UseSpecificConnectionInformation(x =>
{
    if (x == "RemoteEndpoint")
    {
        return ConnectionInfo.Create()
            .UseConnectionString("SomeConnectionString")
            .UseSchema("schema1");
    }
    if (x == "AnotherEndpoint")
    {
        return ConnectionInfo.Create()
            .UseConnectionString("SomeOtherConnectionString")
            .UseSchema("schema2");
    }
    throw new Exception($"Connection string not found for transport address {x}");
});

Note that in Version 3 the EnableLegacyMultiInstanceMode method passes transport address parameter. Transport address conforms to the endpoint_name@schema_name convention, e.g. could be equal to Samples.SqlServer.MultiInstanceSender@[dbo].

Via the App.Config

The endpoint-specific connection information can be discovered by reading the connection strings from the configuration file with NServiceBus/Transport/{name of the endpoint in the message mappings} naming convention.

Given the following mappings:

<UnicastBusConfig>
  <MessageEndpointMappings>
    <add Assembly="Billing.Contract"
         Endpoint="billing"/>
    <add Assembly="Sales.Contract"
         Endpoint="sales"/>
  </MessageEndpointMappings>
</UnicastBusConfig>

and the following connection strings:

<connectionStrings>
  <add name="NServiceBus/Transport"
       connectionString="Server=DbServerA;Database=MyDefaultDB;"/>
  <add name="NServiceBus/Transport/Billing"
       connectionString="Server=DbServerB;Database=Billing;"/>
</connectionStrings>

The messages sent to the endpoint called billing will be dispatched to the database catalog Billing on the server instance DbServerB. Because the endpoint configuration isn't specified for sales, any messages sent to the sales endpoint will be dispatched to the default database catalog and database server instance. In this example that will be MyDefaultDB on server DbServerA.

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:

2.1 NServiceBus.SqlServer
var transport = busConfiguration.UseTransport<SqlServerTransport>();
transport.DefaultSchema("myschema");

For backward compatibility reasons it can be also set via the connection string, using Queue Schema parameter:

var transport = busConfiguration.UseTransport<SqlServerTransport>();
transport.ConnectionString("Data Source=INSTANCE_NAME;Initial Catalog=some_database; Queue Schema=myschema");
<connectionStrings>
  <add name="NServiceBus/Transport"
       connectionString="Data Source=INSTANCE_NAME; Initial Catalog=some_database; Integrated Security=True; Queue Schema=myschema"/>
</connectionStrings>

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 = busConfiguration.UseTransport<SqlServerTransport>();
transport.UseCustomSqlConnectionFactory(
    connectionString =>
    {
        var connection = new SqlConnection(connectionString);
        try
        {
            connection.Open();

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

Wait time

Overrides the default time to wait before triggering a circuit breaker that initiates the endpoint shutdown procedure in case of repeated critical errors.

The default value is 2 minutes.

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

Pause Time

Overrides the default time to pause after a failure while trying to receive a message. The default value is 10 seconds.

var transport = busConfiguration.UseTransport<SqlServerTransport>();
transport.PauseAfterReceiveFailure(TimeSpan.FromSeconds(15));

Last modified