Software Development

Configure NServiceBus with Sql Server transport

riccardone

In order to use Sql Server as middle-ware transport infrastructure you have to install the following nuget package in your projects (where your publisher and subscribers are located)
NServiceBus.SqlServer

If you use a web-api project to send messages into the bus, you have to configure SqlServer transport to be used.
You can add this configuration into the global.asax App_Start method as in the following example


public static IBus Bus;

protected void Application_Start()
{
  var configuration = new BusConfiguration();
  configuration.UseTransport();
// other configurations .....
Bus = NServiceBus.Bus.Create(configuration).Start();
}

You also have to set the connection string element used by NServiceBus to connect with the transport, in our case a normal Sql Server connection string

Now focus on the project where your handling logic is. Install the NServiceBus.SqlServer nuget package.
In the EndpointConfig class you have to set SqlServerTransport instead of the default MsmqTransport

public class EndpointConfig : IConfigureThisEndpoint, AsA_Server, UsingTransport

and modify the configuration file with the proper connection string

Job done. Now when you start the solution, NServiceBus will automatically initialize the Sql Server database using it as the middleware infrastructure with all the required queues in the form of datatables.
NServiceBust use Sql Server database as an infrastructure to store messages. The queues here are in the form of datatable. NServiceBus is not relying on MSDTC or ServiceBroker. All the magic is done by the C# transport component.

If you want to reprocess messages with errors, you can write a procedure that pick up the message from the dbo.errors table and insert the message into the original destination queue/table in an atomic operation. Here is a good example
https://github.com/jerrosenberg/NServiceBus/blob/develop/src/SqlServer/Scripts/ReturnToSourceQueue.sql

One of the problems that I found using Sql Server as transport with NServiceBus is the fact that when the message is handled in your handler it is within a transaction. If you connect with an Entity Framework DataContext class or an Ado.Net connection to a database to do something and you are using a different connection string compared to the one used for the SqlTransport then NSB automatically promote the transaction as a distributed transaction and if you don’t have configured MSDTC you’ll receive an exception.
To fix this problem, you can suppress with your code the transaction wrapping your data access login with a using or you can disable MSDTC by configuration using the following setting in your endpoint config class:

configuration.Transactions().DisableDistributedTransactions();

using this setting, NSB consider all your transaction as normal atomic Ado.net transaction without escalate them to a distributed transaction.

As a general consideration, when you rely on Sql Server as transport you are in fact using it as a broker. A broker is a central dispatcher for all your messages. The side effect here is that relying on a central broker (the SqlServer Instance that host your NSB messaging infrastructure) and keeping your business Sql Server databases somewhere else force you to configure MSDTC to avoid potential data loss in case of disaster scenario. But unfortunately is not possible using MSDTC with Always On availability group as is documented in that article https://msdn.microsoft.com/en-us/library/ms366279.aspx

An interesting side effect using Sql Transport is that you have the possibility to set up Sql Server Always On feature. Using this feature, you can potentially keep synchronized two geographically separated Sql Server instances with all the messaging queuing infrastructure providing in this way a nice High Available solution. In case of disaster on the primary server you can have an automatic fail over. The applications can use a logic listener instead pointing to the physical database server.

More info about SqlServerTransport
http://docs.particular.net/nservicebus/sqlserver/design
http://docs.particular.net/nservicebus/sqlserver/configuration