You are a database developer on an instance of SQL Server 2008. You are creating a Service Broker application. You execute the Transact-SQL shown in the exhibit. (Click the Exhibit(s) button.) In your application, Service1 needs to be able to communicate with the other two services. When messages are read from ReceiveQueue, messages delivered to Service3 should be read first. Which action should you take?
Exhibit:
A. Create an additional message type and assign it a higher priority.
B. Implement conversation priorities using the CREATE BROKER PRIORITY statement.
C. Create separate physical queues for the Service2 and Service3 services.
D. Use MSMQ instead of Service Broker because the desired functionality is not supported.
Correct Answer: B
Explanation/Reference:
By default, messages are sent and received in order. Service Broker allows you to override the order in which messages are sent and received by defining conversation priorities. You 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 defaultpriorityvalue 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 you 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, you want to prioritize the conversations so that when reading messages from ReceiveQueue, messages delivered to Service3 are read first. To accomplish this, you can create two different conversation priorities. For example, you 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. You should note that if the conversation priority is bi-directional, separate priorities for each direction must be established.
To determine the message with the highest priority without actually receiving it, you can use the GET CONVERSATION GROUP statement. You 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.
You 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.
You 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.
You 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.
