Creating Service Broker Queues

A queue holds incoming messages for a service. To simplify processing, applications typically

create one queue per service instead of using the same queue for multiple services.

Setting the retention option for a queue cause messages to be retained after they are

processed. Because retention reduces application performance, specify retention only if the

application requires persistent access to the exact messages sent and received. For more

information about message retention, see

Message Retention.

For applications that don’t use internal activation, don’t specify an activation clause on the

queue definition.

For applications that use internal activation, the queue definition includes the name of the

stored procedure, the maximum number of readers for SQL Server to start, and the name of

the database principal to impersonate before starting the stored procedure.

The name of a queue isn’t included in the network format of a message. Queues are schema-

owned objects. Therefore, queue names follow SQL Server naming conventions. For more

information about naming, see

Naming Service Broker Objects.

A queue can be associated with a stored procedure. SQL Server activates the stored procedure

when there are messages in the queue to be processed. This process of automatic activation

allows a Service Broker application to scale dynamically according to the current processing

load on the application. Each stored procedure activated by Service Broker runs in a separate

thread. When a queue specifies a stored procedure, Service Broker starts new instances of the

stored procedure as required, up to the maximum number of instances specified for the queue.

An activated stored procedure typically processes one or more messages, and returns a

response to the service that originated the messages. When messages arrive faster than the

stored procedure processes messages, Service Broker starts another instance of the stored

procedure, up to the maximum number defined by the queue. An activated stored procedure

typically exits when the procedure finds no messages available on the queue for a while.

Using activation stored procedures is a common way to design Service Broker applications.

However, other designs might better suit the needs of a specific application. Any application

that can run Transact-SQL batches in SQL Server can send and receive messages. Messages