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.
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.
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.
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 [PRIMARY]; CREATE CLUSTERED INDEX [Index_RowVersion] ON [schema].[queuename] ( [RowVersion] ASC ) CREATE NONCLUSTERED INDEX [Index_Expires] ON [schema].[queuename] ( [Expires] ASC ) INCLUDE ( [Id], [RowVersion] )
Receiving messages is conducted by a
DELETE statement from the top of the table (the oldest row according to the
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_datareader roles) are enough.
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 ) INCLUDE ( [Id], [RowVersion] )