"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

July 20, 2009

Service Broker Basics

Service Broker
  • Reliable transactional ordered message queue
  • Lives inside the SQL Server databases, can talk to other SQL Server brokers
  • Exposes Inbuilt Queues, Contracts for Asynchronous message processing
  • Managed using DLL, with limited GUI
  • See http://msdn.microsoft.com/en-us/library/ms345108.aspx
  • Logical source or destination of the message. The service itself.
  • Rules of the conversation. Zero or more per service.
  • Define the parties involved: INITATOR, TARGET, ANY
  • Queue of messages a service needs to process
Internal Activation
  • When Messages are dropped in the queue, An Activation procedure can be assigned to pickup the message and process it
  • This helps for asynchronous message processing
  • The number of readers for activation queue can be increased on need basis
  • When messages are dropped in heavy numbers 1000 per min, We can have as much as 50 readers (MAX_QUEUE_READERS) enabled to pickup and process incoming messages in queue 
External Activation
  • External program reading from queue (Windows Service)
How it flows
  • Service (Initator) starts a conversation with another service (target), with a contract.
  • Each side gets a conversation handle.
  • Initiator sends a message, which is routed/placed to the target server queue, ends conversation.
  • Activation triggers a stored procedure in the Target.
  • Stored procedure receives the message, taking out of the queue.
  • Stored procedure processes the message, ends conversation.
Service Broker supports transactional messaging, which means that messages are sent and received as transactions. If a transaction fails, the message sends and receives are rolled back, and don't take effect until the transaction has processed the messages successfully and committed.
Service Broker solves multithreading issues by putting a lock on the conversation group when a message is read, so that no other thread can receive associated messages until the transaction commits. Service Broker makes multithreaded readers work simply and reliably. Because Service Broker runs in the context of the database instance, it can maintain an aggregate view of all messages that are ready to be transmitted from all databases in the instance.

Detailed Notes Here
SQL Server 2005 Service Broker References
Service Broker Example - Creation of a Simple Queue and Posting a Message
TSQL setup Script example here
ServiceBroker oneliners here
Service Broker Troubleshooting here

Architecting Service Broker Applications
Architecting Service Broker applications (part 2)
Service Broker – Bookmarker
Service Broker Solutions - Don't Forget the Basics
Service broker concepts and troubleshooting
SQL Bits - Service Broker: Message in a bottle
ssbdiagnose Utility

Got the below error today 'An exception occurred while enqueueing a message in the target queue. Error: 15404'
1. First step clear messages in transmission queue. Query here
2. Second step link provides rootcause and resolution. use EXEC sp_changedbowner 'AccountName'

Useful Queries
Tip1 . Query to Check Service Broker is Enabled for a Database


Tip 2. Query to Check for Service Broker Queues and their Status

SELECT name as Queue,
CASE WHEN is_receive_enabled = 1 THEN 'Enabled'
ELSE 'Disabled' END AS [Queue Status]

Tip 3.Query to check for MessageTypes defined for ServiceBroker


Tip 4. Query to check for configured Service Contracts


Tip 5. Query to check for Activation Enabled for Queues

SELECT name as Queue,
CASE WHEN is_activation_enabled = 1 THEN 'Enabled'
ELSE 'Disabled' END AS [Activation Procedure Status]

Tip 6. Query to check for Messages in Transmission Queue

SELECT CONVERT(VARCHAR(1000),message_body),* FROM sys.transmission_queue WITH (NOLOCK)

Happy Reading!!

No comments: