Queues (SQL Server)

When a message arrives for a service, Service Broker places the message on the queue associated with the service. PowerDesigner models queues as extended objects with a stereotype of <<Queue>>.

Creating a Queue

You can create a queue in any of the following ways:

  • Select Model > Queues to access the List of Queues, and click the Add a Row tool.

  • Right-click the model (or a package) in the Browser, and select New > Queue.

Queue Properties

You can modify an object's properties from its property sheet. To open a queue property sheet, double-click its Browser entry.

The following extended attributes are available on the Microsoft tab:

Name

Description

Owner

Specifies the owner of the queue.

Scripting name: Owner

Status

Specifies that the queue is available. This is the default.

If a queue is unavailable, no messages can be added to or removed from it. If you create a queue as unavailable, then no messages can be added to it until it is made available with an ALTER QUEUE statement.

Scripting name: Status

Retention

Specifies that all messages sent or received on conversations using this queue are retained in the queue until the conversations have ended. This allows you to retain messages for auditing purposes, or to perform compensating transactions if an error occurs.

The default is to not retain messages in the queue in this way.

Scripting name: Retention

Activation

Specifies that a stored procedure is required to activate message processing for the queue.

Scripting name: Activation

Status (activation)

Specifies that Service Broker activates the associated stored procedure when the number of procedures currently running is less than MAX_QUEUE_READERS and when messages arrive on the queue faster than the stored procedures receive messages.

This is the default.

Scripting name: ActivationStatus

Procedure

Specifies the name of the stored procedure to activate to process messages in this queue.

Scripting name: ActivationProcedureName

MaxQueueReaders

Specifies the maximum number of instances of the activation stored procedure that the queue can start at the same time. Must be set to between 0 and 32767.

Scripting name: ActivationMaxQueueReaders

Execute as

Specifies the user under which the activation stored procedure runs. SQL Server must be able to check the permissions for this user at the time that the queue activates the stored procedure. You can choose between:

  • SELF - the stored procedure executes as the current user. (The database principal executing this CREATE QUEUE statement.)

  • OWNER - the stored procedure executes as the owner of the queue.

Scripting name: ActivationExecuteAs

File group

Specifies the SQL Server filegroup on which to create the queue.

Scripting name: FileGroup