| title | summary | reviewed | component | redirects | |||||
|---|---|---|---|---|---|---|---|---|---|
SQL Transport Design |
The design and implementation details of SQL Server Transport |
2024-10-24 |
SqlTransport |
|
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.
The queue table consists of the following columns.
The Id is a Guid/uniqueidentifier generated by the sending code. It is not used by SQL Server transport itself.
The CorrelationId column contains the value of NServiceBus.CorrelationId header. This value is kept in a separate column to maintain wire-level compatibility with NServiceBus.SqlServer transport Version 1.
The ReplyToAddress column contains the value of the NServiceBus.ReplyToAddress header. This value is kept in a separate column to maintain wire-level compatibility with NServiceBus.SqlServer transport Version 1.
The Recoverable column should always contain the value 1 to ensure wire-level compatibility with NServiceBus.SqlServer transport Version 1.
The CorrelationId, ReplyToAddress and Recoverable columns are required for backwards compatibility with version 1 of the NServiceBus.SqlServer transport.
When receiving messages sent by endpoints that use later versions, the values of correlation ID and reply-to address should be read from the headers (NServiceBus.CorrelationId and NServiceBus.ReplyToAddress) instead. The value Recoverable can be ignored as it is always true/1.
When sending messages to endpoints that use later versions, the values of correlation ID and reply-to address columns could be set to NULL with the actual values provided in the headers (NServiceBus.CorrelationId and NServiceBus.ReplyToAddress). The value Recoverable should always be true/1.
The Expires column contains the optional date and time when the message will 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.
partial: expired-index
The Headers column contains a JSON representation of message headers.
The Body column contains the serialized message body.
partial: messageBodyString-column
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 that new messages are always added at the end of the table.
The following section describes the runtime behavior of SQL Server transport when sending and receiving messages.
Messages are sent by executing an insert command against the queue table.
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.
partial: concurrency