Component: SQL Server Transport
NuGet Package NServiceBus.SqlServer (3.x)
Target NServiceBus Version: 6.x

Primary queue

Each endpoint has a single table representing the primary queue. The name of the primary queue matches the name of the endpoint.

In a scale out scenario this single queue is shared by all instances.

Callback queues

If queue individualization is enabled for callbacks, then SQL Server transport is using an additional table for the instance-specific queue. The name of the table is derived from the endpoint name and the instance ID provided by the user.

Other queues

Each endpoint also has queues required by timeout (the exact names and number of queues created depends on the version of the transport) and retry mechanisms.

Error and audit queues are usually shared among multiple endpoints.

Queue table structure

Following SQL DDL is used to create a table and its index for a queue:

CREATE TABLE [schema].[queuename](
	[Id] [uniqueidentifier] NOT NULL,
	[CorrelationId] [varchar](255),
	[ReplyToAddress] [varchar](255),
	[Recoverable] [bit] NOT NULL,
	[Expires] [datetime],
	[Headers] [varchar](max) NOT NULL,
	[Body] [varbinary](max),
	[RowVersion] [bigint] IDENTITY(1,1) NOT NULL

ON [schema].[queuename]
	[RowVersion] ASC

ON [schema].[queuename]
	[Expires] ASC

Receiving messages is conducted by a DELETE statement from the top of the table (the oldest row according to the [RowVersion] column).

The tables are created by installers when the application is started for the first time. It is required that the user account under which the installation of the host is performed has CREATE TABLE as well as VIEW DEFINITION permissions on the database in which the queues are to be created. The account under which the service runs does not have to have these permissions. Standard read/write/delete permissions (e.g. being member of db_datawriter and db_datareader roles) are enough.

Creating table structure in Production

The scripts above, to generate the queues, do not have queue names. This may cause confusion when reviewed by a DBA. The scripts could, alternatively, be generated off the Development or Staging environment, and then directly executed on Production environment by DBAs to replicate that table structure.

To generate this DDL script, right-click the database and from "Tasks" menu choose "Generate Scripts..." and generate the scripts for relevant tables.


Each queue table has a clustered index on the [RowVersion] column in order to speed up receiving messages from the queue table.

Each queue table also has an additional non-clustered index on the [Expires] column. This index speeds up the purging of expired messages from the queue table. If the SQL Server transport discovers that a required index is missing, it will log an appropriate warning. The following SQL statement can be used to create the missing index:

create nonclustered index [Index_Expires]
on [schema].[queuename]
    [Expires] asc

Last modified