You may have noticed in NServiceBus release note that there are a few new transports available on v4.0. This means you no longer need to choose MSMQ as your transport and can choose from other well reputed queue engines such as ActiveMQ and RabbitMQ. But among all, and even more interestingly, there’s another transport called Sql Transport. Yes, you heard it right! It actually uses a Sql Server database to store the messages and it basically mimics the Queue. You may well argue that putting something - here the database - in the middle of everything is more of a broker architecture and not a Bus, wouldn’t that make the name - NServiceBus - irrelevant? What’s going on? The reasoning behind it is that although this “is” a broker architecture, you may still find yourself in situations where you want to use a database to store your messages, your sagas as well as your application required data. Whatever the reason, fret not, NServiceBus got all your bases covered. Let’s see how you use this transport and how it behaves.

Whether you use NServiceBus host or run in in-proc you can use the SqlTransport, but configuration is a bit different. To configure your endpoint when running using the host, you simple need an aditional interface on your EndpointConfig class.

1
public class EndpointConfig : IConfigureThisEndpoint, AsA_Publisher, UsingTransport<SqlServer> { }

To do the same thing when running the bus in-proc you need to use fluent configuration:

1
2
3
4
5
6
7
8
9
public void Init() 
{
    NServiceBus.Configure
               .With()
               .DefaultBuilder()
               .UseTransport<SqlServer>("YourOptionalConnectionStringName") 
               .UnicastBus()
               .CreateBus();
}

Once application is started, if you have auto installation of queues enabled, it will generate required tables for you, including Audit and Error queue/tables.

Queue Tables

Now suppose something has gone wrong, you have fixed the problem, and you need to return the message to the original queue to process it again, what should you do? You probably have used “ReturnToSource” executable or the Powershell CmdLet, but as bad as it sounds, it is not going to help you here, but fret not! Since you use a database, it should be trivial to remove a record from one table and insert it in another table, right? If you don’t think so, this StoredProc should be good enough to get you started but if you are looking for better tooling, ServiceInsight (already pre-release) supports returning the message to the source queue functionality and is transport agnostic.