SQL Transport Design

Component: SQL Server Transport
NuGet Package NServiceBus.SqlServer (1.x)
Target NServiceBus Version: 4.x
Standard support for version 4.x of NServiceBus has expired. For more information see our Support Policy.

In SQL Server Transport each queue is represented as table inside a database. Depending on the endpoint configuration, each endpoint might use multiple queues/tables e.g. for callbacks.

Structure

The queue table consists of following columns

Id

The Id is a Guid/uniqueidentifier generated by the sending code. It is not used by SQL Server transport itself.

CorrelationId

The CorrelationId column contains the value of NServiceBus.CorrelationId header. This value is kept in a separate column to maintain wire-level compatibility with transport Version 2.

ReplyToAddress

The ReplyToAddress column contains the value of NServiceBus.ReplyToAddress header. This value is kept in a separate column to he maintain wire-level compatibility with transport Version 2 and lower.

Recoverable

The Recoverable column should always contain the value 1 to ensure wire-level compatibility with transport Version 2 and lower.

Expires

The Expires column contains the optional date and time when the message is going to expire. An expired message is dropped by the transport. Depending on version, expired messages might be actively purged from the queue. For details see discarding expired messages.

Headers

The Headers column contains a JSON representation of message headers.

Body

The Body column contains the serialized message body.

RowVersion

The RowVersion column is used to define the FIFO order of the queue. It is auto-incremented by SQL Server (identity(1,1)). The receive message T-SQL query returns a message with the lowest value of RowVersion that is not locked by any other concurrent receive operation.

The clustered index of the queue table is based on the RowVersion column to ensure the new messages are always added at the end of the table.

Behavior

The following section describes the runtime behavior of SQL Server transport when sending and receiving messages.

Sending

Messages are sent by executing an insert command against the queue table.

Receiving

Messages are received by executing a delete command against the queue table. The delete is limited to a row with the lowest RowVersion not locked by other concurrent delete. This ensures that multiple threads within an endpoint instance and multiple instances of the same scaled-out endpoint can operate at full speed without conflicts.

Each endpoint running SQL Server transport spins up a fixed number of threads (controlled by MaximumConcurrencyLevel property of TransportConfig section) for each input queue. Each thread runs in a loop, using the delete command to poll the database for messages awaiting processing.

By default, there are 4 input queues created for every endpoint (apart from the main one, there are two for handling timeouts and one for the retries). As a consequence, if MaximumConcurrencyLevel is set to 10, there are 40 threads running and constantly polling the database.

Read more information about tuning endpoint message processing.


Last modified