SQL Server Service Broker (SSBS) is a new architecture (introduced with SQL Server 2005 and enhanced further in SQL Server 2008 and SQL Server 2008 R2) that allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable and secure queuing/message-based applications within the database itself.
In a recent article I talked about creating Service Broker application and communicating between Initiator and Target. Service Broker ensures Exactly Once in Order delivery for messages inside a conversation. But what if you want some important messages/conversations (with higher priority) to be processed earlier than messages/conversations with lower priority? Is there any way to define priority in sending or receiving messages in Service Broker?
Understanding Conversation PriorityStarting with SQL Server 2008, you can define the priority for your conversation to be either lower or higher than the default. In essence, conversation priority in SQL Server 2008 and later versions is a way of defining a set of rules, have priority attached to it and criteria to determine which conversation will use that priority. Normally priority level determines the order in which messages are sent or receive by Service Broker. In other words, high priority conversation messages are sent or received before the lower priority conversation's messages.
Enabling Conversation PriorityThe first thing that you need to do to have conversation priority in use is to enable a database for Service Broker priority as you can see in the scripts below:
Enabling Service Broker Priority on the database
--By default a database will have service broker priority disabled, which --you can verify with is_honor_broker_priority_on column of the below resultset SELECT name, service_broker_guid, is_broker_enabled, is_honor_broker_priority_on FROM sys.databases WHERE name = 'SSBSInitiatorDB' GO USE master --If your database is not enabled for Service Broker priority, you can enable --service broker priority for a database with this statement ALTER DATABASE SSBSInitiatorDB SET HONOR_BROKER_PRIORITY ON --WITH ROLLBACK IMMEDIATE GO USE master --You can disable service broker priority for a database with this statement ALTER DATABASE SSBSInitiatorDB SET HONOR_BROKER_PRIORITY OFF --WITH ROLLBACK IMMEDIATE GO
Please note, HONOR_BROKER_PRIORITY must be set to ON for priority to work. Even though you might see messages with different priority levels in the sys.transmission_queue, they will be sent with the default priority level of 5 if you have HONOR_BROKER_PRIORITY set to OFF.
Getting started with Conversation PriorityInstead of creating a whole article one more time here, I will use the same sample which I created in an earlier article. But this time I will be creating two sender services, one with lower priority and one with higher priority than the default one, and will use the same contract or target service as before, as you can see below in the script block.
We use the CREATE BROKER PRIORITY T-SQL command to create priority object, which expects:
- Name of the conversation priority.
- Priority level default is 5, though you can have it from 1 (lowest) to 10 (highest) priority
- The criteria that determine which conversation will use that priority level and which includes:
- A contract name or ANY.
- A local service name or ANY.
- A remote service name or ANY.
Please note priority objects are applicable on the side (Initiator or Target) where you have created them. If you want all messages to be prioritized on both sides, you need to set it up on both sides.
Next in the script block below I am creating Service Broker's priority objects with Contract name, Local service name and Remote service name. You can have either one of them or a combination of these Contract name, Local service name and Remote service name in your Service Broker Priority object; for more information, click here.
Creating Service Broker Priority Objects
USE SSBSInitiatorDB; GO CREATE SERVICE [//SSBSLearning/ProductStockStatusCheck/InitiatorServiceWithLowPriority] ON QUEUE dbo.SSBSLearningInitiatorQueue; GO CREATE SERVICE [//SSBSLearning/ProductStockStatusCheck/InitiatorServiceWithHighPriority] ON QUEUE dbo.SSBSLearningInitiatorQueue; GO CREATE BROKER PRIORITY InitiatorWithLowPriorityToTarget FOR CONVERSATION SET ( CONTRACT_NAME = [//SSBSLearning/ProductStockStatusCheckContract], LOCAL_SERVICE_NAME = [//SSBSLearning/ProductStockStatusCheck/InitiatorServiceWithLowPriority], REMOTE_SERVICE_NAME = N'//SSBSLearning/ProductStockStatusCheck/TargetService', PRIORITY_LEVEL = 4 ) GO CREATE BROKER PRIORITY InitiatorWithHighPriorityToTarget FOR CONVERSATION SET ( CONTRACT_NAME = [//SSBSLearning/ProductStockStatusCheckContract], LOCAL_SERVICE_NAME = [//SSBSLearning/ProductStockStatusCheck/InitiatorServiceWithHighPriority], REMOTE_SERVICE_NAME = N'//SSBSLearning/ProductStockStatusCheck/TargetService', PRIORITY_LEVEL = 6 ) GO
When you send or receive a message, Service Broker matches the conversation (conversation endpoint) your message belongs to, with any defined priority on the basis of local service name, remote service name or contract name defined in the priority object, and depending on the best match, it uses the priority defined with that priority object for that message of that conversation. In the example below, Service Broker matches the first conversation with the definition InitiatorWithLowPriorityToTarget conversation priority object and hence priority of messages will be set to 4 for all the messages belonging to this conversation. Likewise, the Service Broker matches the second conversation with definition with InitiatorWithHighPriorityToTarget conversation priority object and hence priority of messages will be set to 6 for all the messages belonging to this conversation.
Now suppose messages from these conversations are in the sys.transmission_queue; when Service Broker tries to send these messages it picks up the messages with priority 6 (as these are messages with higher priority available) and sends them first and then it picks up the messages with priority 4.
Please note, messages go to sys.transmission_queue first in case of across instances communication or if there is any exception during transmission on the same instance communication.