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

Service

• Logical source or destination of the message. The service itself.

Contracts

• Rules of the conversation. Zero or more per service.
• Define the parties involved: INITATOR, TARGET, ANY

Queue

• 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

SELECT IS_BROKER_ENABLED, NAME FROM SYS.DATABASES WITH (NOLOCK)

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]
FROM SYS.SERVICE_QUEUES WITH (NOLOCK)

Tip 3.Query to check for MessageTypes defined for ServiceBroker

SELECT * FROM SYS.SERVICE_MESSAGE_TYPES WITH (NOLOCK)

Tip 4. Query to check for configured Service Contracts

SELECT * FROM SYS.SERVICE_CONTRACTS WITH (NOLOCK)

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]
FROM SYS.SERVICE_QUEUES WITH (NOLOCK)

Tip 6. Query to check for Messages in Transmission Queue

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


Happy Reading!!