SQL Server Transport Upgrade Version 2 to 3

Namespace changes

The primary namespace is NServiceBus. Advanced APIs have been moved to NServiceBus.Transport.SqlServer. A using NServiceBus directive should be sufficient to find all basic options. A using NServiceBus.Transport.SqlServer statement is needed to access advanced configuration options.

Transactions

The native transaction support has been split into two levels: ReceiveOnly and SendAtomicWithReceive. Both are supported. SendAtomicWithReceive is equivalent to disabling distributed transactions in NServiceBus version 5.

// For SQL Transport version 3.x
var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
transport.Transactions(TransportTransactionMode.SendsAtomicWithReceive);

// For SQL Transport version 2.x
var transactions = busConfiguration.Transactions();
transactions.DisableDistributedTransactions();

As shown in the above snippet, transaction settings are now handled at the transport level configuration.

For more details and examples refer to the transaction configuration API and transaction support pages.

Connection factory

The custom connection factory method is now expected to be async and no parameters are passed to it by the transport:

// For SQL Transport version 3.x
var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
transport.UseCustomSqlConnectionFactory(
    sqlConnectionFactory: async () =>
    {
        var connection = new SqlConnection("SomeConnectionString");
        try
        {
            await connection.OpenAsync();

            // perform custom operations

            return connection;
        }
        catch
        {
            connection.Dispose();
            throw;
        }
    });

// For SQL Transport version 2.x
var transport = busConfiguration.UseTransport<SqlServerTransport>();
transport.UseCustomSqlConnectionFactory(
    connectionString =>
    {
        var connection = new SqlConnection(connectionString);
        try
        {
            connection.Open();

            // custom operations

            return connection;
        }
        catch
        {
            connection.Dispose();
            throw;
        }
    });

Accessing transport connection

Accessing the transport connection can be achived in version 2 of the transport by injecting SqlServerStorageContext into a handler. This way the handler can access the data residing in the same database as the queue tables using the same message receive transaction managed by the transport.

In version 3, this API has been removed. The same goal can be achieved in version 3 by using ambient transaction mode.

var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
transport.Transactions(TransportTransactionMode.TransactionScope);

The handler must open a connection to access the data, but assuming both handler and the transport are configured to use the same connection string, there is no DTC escalation. SQL Server 2008 and later can detect that both connections target the same resource and merge the two transactions into a single lightweight transaction.

Multi-schema support

The configuration API for multi-schema support has now changed. The QueueSchema parameter is no longer supported in the config file and the code configuration API.

The schema for the configured endpoint can be specified using the DefaultSchema method:

// For SQL Transport version 3.x
var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
transport.DefaultSchema("myschema");

// For SQL Transport version 2.x
var transport = busConfiguration.UseTransport<SqlServerTransport>();
transport.DefaultSchema("myschema");

or by defining a custom schema for each endpoint or queue:

var transport = busConfiguration.UseTransport<SqlServerTransport>();
transport.UseSpecificConnectionInformation(
    EndpointConnectionInfo.For("sales")
        .UseSchema("sender"),
    EndpointConnectionInfo.For("error")
        .UseSchema("control")
);

This enables configuring custom schemas both for local the endpoint as well as for other endpoints it communicates with. When using a configuration file to specify schemas for other endpoints, their schemas must be placed in the MessageEndpointMappings section and follow the endpoint-name@schema-name convention:

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

Multi-instance support

The configuration API for multi-instance support has changed. Multiple connection strings must be provided by a connection factory method passed to EnableLegacyMultiInstanceMode method.

Note that EnableLegacyMultiInstanceMode method replaces both pull and push modes from version 2.x.

// For SQL Transport version 3.x
var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
transport.EnableLegacyMultiInstanceMode(
    sqlConnectionFactory: async address =>
    {
        string connectionString;
        if (address == "RemoteEndpoint")
        {
            connectionString = "SomeConnectionString";
        }
        else
        {
            connectionString = "SomeOtherConnectionString";
        }
        var connection = new SqlConnection(connectionString);
        await connection.OpenAsync();
        return connection;
    });

// For SQL Transport version 2.x
var transport = busConfiguration.UseTransport<SqlServerTransport>();
// Option 1
transport
    .UseSpecificConnectionInformation(
        EndpointConnectionInfo.For("RemoteEndpoint")
            .UseConnectionString("SomeConnectionString"),
        EndpointConnectionInfo.For("AnotherEndpoint")
            .UseConnectionString("SomeOtherConnectionString"));

// Option 2
transport
    .UseSpecificConnectionInformation(
        connectionInformationProvider: x =>
        {
            if (x == "RemoteEndpoint")
            {
                var connection = ConnectionInfo.Create();
                return connection.UseConnectionString("SomeConnectionString");
            }
            if (x == "AnotherEndpoint")
            {
                var connection = ConnectionInfo.Create();
                return connection.UseConnectionString("SomeOtherConnectionString");
            }
            return null;
        });

Note that multi-instance mode has been deprecated and is not recommended for new projects.

Multi-instance support and outbox

Version 3 does not support the outbox in multi-instance mode.

Circuit breaker

The method PauseAfterReceiveFailure(TimeSpan) is no longer supported. In version 3, the pause value is hard-coded at one second.

Indexes

Queue tables created by version 2.2.1 or lower require manual creation of a non-clustered index on the [Expires] column. The following SQL statement can be used to create the missing index:

CREATE NONCLUSTERED INDEX [Index_Expires]
ON [schema].[queuename]
(
	[Expires] ASC
)
INCLUDE
(
	[Id],
	[RowVersion]
)

A warning will be logged when a missing index is detected.