SQL Server 2008 Service Broker - Conversation Priorities

March 10, 2011

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 Priority

Enabling Conversation Priority

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 Priority

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.

Resources

Conversation Priorities

See all articles by Arshad Ali