SQL Server: Creating a Service Broker Application

By default, messages are sent and received in order. Service Broker allows DBA/Developer to override the order in which messages are sent and received by defining conversation priorities. DBA/Developer can use the CREATE BROKER PRIORITY statement to assign different numeric priority levels to different conversations. The syntax of the CREATE BROKER PRIORITY statement is as follows:

CREATE BROKER PRIORITY
priorityname
FOR CONVERSATION
[SET
([CONTRACT_NAME = {contractname | ANY}]
[[,] LOCAL_SERVICE_NAME =
{localservicename | ANY}]
[[,] REMOTE_SERVICE_NAME = {'remoteservicename' | ANY}]
[[,] PRIORITY_LEVEL ={priorityvalue | DEFAULT}])];

The priorityname specifies the conversation priority's name. The priorityvalue specifies an integer between 1 and 10 representing the priority value of the conversation. Conversations with a higher priority value have higher priority. The default priorityvalue is 5. The SET clause specifies the information that Service Broker uses to decide if a conversation should be prioritized. The SET clause may contain one or more of the following criteria:

CONTRACT_NAME: The name of the contract used in the conversation that was specified in the ON CONTRACT clause of the BEGIN DIALOG statement. This criterion allows DBA/Developer to prioritize conversations that use different contracts. LOCAL_SERVICE_NAME: The local service name criterion used to prioritize conversations. REMOTE_SERVICE_NAME: The remote service name criterion used to prioritize conversations.

PRIORITY_LEVEL: The integer value representing the conversation priority. Each criterion in the SET clause defaults to ANY if not specified. When an application sends or receives a message, Service Broker examines the criteria to determine the conversation priority. If Service Broker determines that the conversation meets more than one criterion, it will assign the conversation priority based on the best match. In this scenario, the Service2 and Service3 services share the same queue. However, DBA/Developer want to prioritize the conversations so that when reading messages from ReceiveQueue, messages delivered to Service3 are read first. To accomplish this, DBA/Developer can create two different conversation priorities. For example, DBA/Developer could use the following Transact-SQL to assign a higher priority to Service3's conversations:

CREATE BROKER PRIORITY
LowPriority
FOR CONVERSATION
SET
(CONTRACT_NAME = Contract1,
LOCAL_SERVICE_NAME = Service2,
REMOTE_SERVICE_NAME = N'Service1',
PRIORITY_LEVEL = 1);
CREATE BROKER PRIORITY HighPriority
FOR CONVERSATION
SET
(CONTRACT_NAME = Contract1,
LOCAL_SERVICE_NAME = Service3,
REMOTE_SERVICE_NAME = N'Service1',
PRIORITY_LEVEL = 10);

An application could then send a message from Service1 to Service2 using Contract1 and another message from Service1 to Service3. Using the conversation priorities defined by these statements, Service Broker will receive the higher-priority message sent to Service3 first. Without conversation priorities, the messages would be retrieved from ReceiveQueue in the order they were originally sent. DBA/Developer should note that if the conversation priority is bidirectional, separate priorities for each direction must be established. To determine the message with the highest priority without actually receiving it, DBA/Developer can use the GET CONVERSATION GROUP statement. DBA/Developer should also note that although conversations for received messages can implement priorities by default, the HONOR_BROKER_PRIORITY database option must be set to ON to implement conversation priorities for sent messages. DBA/Developer should not create an additional message type and assign it a higher priority because Service Broker does not prioritize messages based on the message type. DBA/Developer should not create separate physical queues for the Service2 and Service3 services. In this scenario, it is not necessary to create separate queues for the services. Both services can use the same physical message queue with the conversations for each prioritized differently. DBA/Developer should not use MSMQ instead of Service Broker because the desired functionality is not supported. Service Broker does support the required functionality with conversation priorities.