Conversation priority is a new feature with SQL Server 2008. In a previous blog post, I talked about how to simply set one up. But you'd usually not want to set up a priority for all messages. So, lets set up a simple service and then define a "Premier Customer" priority. Before trying this you need to make sure the database is set to honor broker priority.
ALTER DATABASE pubs SET HONOR_BROKER_PRIORITY ON;
Suppose I had a simple (one database) service pair. A service called "sender" (note lower case) using senderq is the initiator and a service called "receiver" using receiverq is the target. They use the [default] contract. So we can set up a "Premier Customer" by contract.
CREATE CONTRACT premier ([DEFAULT] SENT BY ANY);
GO
Now, let's ensure that the target can also use the premier contract in addition to the DEFAULT contract.
ALTER SERVICE receiver ON QUEUE receiveq (ADD CONTRACT premier);
We need to set up the priority.
CREATE BROKER PRIORITY PremierCustomer
FOR CONVERSATION
SET (CONTRACT_NAME = premier,
LOCAL_SERVICE_NAME = sender,
REMOTE_SERVICE_NAME = 'receiver',
PRIORITY_LEVEL = 7);
GO
The interesting thing is, this priority ONLY covers messages sent and received (and get conversation group) by the sender/senderq, IT DOESN'T cover the receives in the receiver/receiveq queue. So we'll set up a mirror image priority, and make the priority level still higher than the default (5) but gratuitously different than the other priority
CREATE BROKER PRIORITY PremierCustomer2
FOR CONVERSATION
SET (CONTRACT_NAME = premier,
LOCAL_SERVICE_NAME = receiver, -- local service is receiver
REMOTE_SERVICE_NAME = 'sender', -- remote service is sender
PRIORITY_LEVEL = 6); -- note level 6, not 7
GO
Now, begin a conversation using sender and receiver and the premier contract and send a message. Note that when you issue "SELECT * FROM sys.conversation_endpoints;" the sender (initiator's) endpoint is priority 7, the receiver's endpoint is priority 6. And, looking at the message in the receiveq its set to priority 6.
If you'd send a return message from the receiver (target) back to the sender (initiator), the return message in the senderq would be priority 7.
So when setting up priorities, if you want all messages on both sides to be affected, don't forget to set up a priority for EACH side. Here's the entire demo.
BrokerPriorityByContractDemo.zip (1.57 KB)