Message Priorities - SQL Server 2008

In the SQL Server 2005 Service Broker architecture, messages are given equal priority across all conversations.

Most other messaging systems, like Microsoft Message Queuing (MSMQ), provide a way to set priorities on the messages, so more important messages get processed first. This type of message priority was added in SQL Server 2008.

Message priorities in Service Broker are defined at the conversation level. The conversation priority will affect the order in which messages from different conversations will be sent and the order in which they will be received. To illustrate this point and the capability of conversation priority, let’s work through an example.

For this example, create a database called SBTest and configure it for use with Service Broker, as follows:

ALTER DATABASE SBTest SET enable_broker

ALTER DATABASE SBTest SET HONOR_BROKER_PRIORITY ON

CREATE MASTER KEY ENCRYPTION BY PASSWORD='wnMaz5a123123'

As you’ve learned in this chapter, you need to alter the database to enable Service Broker and to provide a master key for use in encrypting the transmission of the message. To tell Service Broker to use conversation priorities, you enable this functionality at the database level through a new SET parameter called HONOR_BROKER_PRIORITY.

Next, create a simplified Service Broker configuration that has two contracts: one for customer orders and the other for internal procurement orders.

CREATE MESSAGE TYPE GenericMessage VALIDATION = NONE

CREATE CONTRACT MyCustomerMessageContract
(GenericMessage SENT BY INITIATOR)

CREATE CONTRACT MyInternalMessageContract
(GenericMessage SENT BY INITIATOR)

CREATE QUEUE SenderQueue

CREATE QUEUE ReceiverQueue

CREATE SERVICE Sender ON QUEUE SenderQueue

CREATE SERVICE Receiver ON QUEUE ReceiverQueue
(MyCustomerMessageContract,MyInternalMessageContract)

At this point, you have configured Service Broker enough to send and receive messages. You have not defined any specific priority, so you expect the behavior to be that of SQL Server 2005.

Now, let’s create a stored procedure that will send a message through either the MyCustomerMessageContract or the MyInternalMessageContract:

CREATE PROCEDURE SendMessageToQueue
@MyType NVARCHAR(10)
AS
DECLARE @conversationHandle UNIQUEIDENTIFIER
DECLARE @message NVARCHAR(100)
BEGIN
IF (@MyType='C')
BEGIN
BEGIN DIALOG @conversationHandle
FROM SERVICE Sender
TO SERVICE 'Receiver'
ON CONTRACT MyCustomerMessageContract
SET @message = 'CustomerOrder';
END
ELSE
BEGIN
BEGIN DIALOG @conversationHandle
FROM SERVICE Sender
TO SERVICE 'Receiver'
ON CONTRACT MyInternalMessageContract
SET @message = 'InternalOrder';
END;
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE GenericMessage (@message);
END

Now you are ready to fill up the queue with some information. Executing the following code will populate our queue with a variety of customer (identified by passing 'C') and internal procurement orders (identified by anything else; in this case, ‘I').

exec SendMessageToQueue 'I'
exec SendMessageToQueue 'I'
exec SendMessageToQueue 'I'
exec SendMessageToQueue 'I'
exec SendMessageToQueue 'C'
exec SendMessageToQueue 'C'
exec SendMessageToQueue 'I'
exec SendMessageToQueue 'I'
exec SendMessageToQueue 'C'
exec SendMessageToQueue 'I'

Now issue the following query to look at the messages on the queue:

SELECT priority, CONVERT(NVARCHAR(max), message_body) AS 'Order Type' FROM ReceiverQueue

This will result in the following result set:

Priority Order Type
---------- ---------------------
5 InternalOrder
5 InternalOrder
5 InternalOrder
5 InternalOrder
5 CustomerOrder
5 CustomerOrder
5 InternalOrder
5 InternalOrder
5 CustomerOrder
5 InternalOrder

This reflects the order in which the messages were submitted. You can see that if you enable the database to honor Service Broker priorities but do not assign any conversation priorities, by default, the conversations will be assigned the priority of 5. Service Broker priorities range from 1 to 10, with 10 being the highest.

Currently, if you went through and submitted the following command ten times:

RECEIVE CONVERT(NVARCHAR(max), message_body) AS 'Message',
priority as 'Priority' FROM ReceiverQueue

you would get the order of messages coming off the queue in the same order as they were returned by the preceding query.

Now, let’s create a priority for each of the two types of contracts. The procurement_order priority will be set to 1, since it is not as important as processing customer orders, which will be set to 10:

CREATE BROKER PRIORITY procurement_order
FOR CONVERSATION
SET (CONTRACT_NAME=MyInternalMessageContract,
PRIORITY_LEVEL=1)
CREATE BROKER PRIORITY customer_order
FOR CONVERSATION
SET (CONTRACT_NAME=MyCustomerMessageContract,
PRIORITY_LEVEL=10)

Let’s put the messages back on the queue:

exec SendMessageToQueue 'I'
exec SendMessageToQueue 'I'
exec SendMessageToQueue 'I'
exec SendMessageToQueue 'I'
exec SendMessageToQueue 'C'
exec SendMessageToQueue 'C'
exec SendMessageToQueue 'I'
exec SendMessageToQueue 'I'
exec SendMessageToQueue 'C'
exec SendMessageToQueue 'I'

Next, query the queue again:

SELECT priority, CONVERT(NVARCHAR(max), message_body) AS 'Order Type' FROM ReceiverQueued

You will see the following output:

Priority Order Type
-------- -------------------
1 InternalOrder
1 InternalOrder
1 InternalOrder
1 InternalOrder
10 CustomerOrder
10 CustomerOrder
1 InternalOrder
1 InternalOrder
10 CustomerOrder
1 InternalOrder

Now when you pick off a message from the queue, you will receive the highest priority messages first. Issuing the following statement ten times:

RECEIVE CONVERT(NVARCHAR(max), message_body) AS 'Message',
priority as 'Priority' FROM ReceiverQueue

yields the following order:

CustomerOrder
CustomerOrder
CustomerOrder
InternalOrder
InternalOrder
InternalOrder
InternalOrder
InternalOrder
InternalOrder
InternalOrder

Setting priorities affects the transmission order of messages from different conversations, as well as the order in which a conversation group is received off a queue.

A new catalog view called sys.conversation_priorities will display which priorities are associated with which conversations.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics