Using connection pooling
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 mentioned above, change the connection pool size to ensure it will not be exhausted. See SQL Server Connection Pooling and Configuration for more details.
Connection configuration
The 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/
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>
Via a named connection string
By using the ConnectionStringName
extension method:
var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
transport.ConnectionStringName("MyConnectionString");
Combined this with a named connection in the connectionStrings
node of the app.
file:
<configuration>
<connectionStrings>
<add name="MyConnectionString"
connectionString="Data Source=instance; Initial Catalog=db; Integrated Security=True; Queue Schema=nsb; Max Pool Size=80"/>
</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 is configured using following API
var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
transport.EnableLegacyMultiInstanceMode(async address =>
{
var connectionString = address.Equals("RemoteEndpoint") ? "SomeConnectionString" : "SomeOtherConnectionString";
var connection = new SqlConnection(connectionString);
await connection.OpenAsync()
.ConfigureAwait(false);
return connection;
});
address
parameter passed to the callback above is a transport address. It conforms to the queue@[schema]
convention, e.g. MultiInstanceSender@[dbo]
.Custom database schemas
The SQL Server transport uses dbo
as a default schema. It is used for every queue if no other schema is explicitly provided in a transport address. This includes all local queues, error, audit and remote queues of other endpoints.
The default schema can be overridden using the 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 the 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 immediately after opening the connection.
This can be done by passing a custom factory method to the transport 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;
}
});
Circuit breaker
A built-in circuit breaker is used to handle intermittent SQL Server connectivity problems. When a failure occurs while 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
The circuit breaker's default time to wait before triggering is two minutes. Use the TimeToWaitBeforeTriggeringCircuitBreaker
method to change it.
var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
transport.TimeToWaitBeforeTriggeringCircuitBreaker(TimeSpan.FromMinutes(3));