DaveWentzel.com            All Things Data

Service Broker

(More of my posts on Service Broker can be found here)

Service Broker

...is a new scalability technology introduced that allows you to build reliable, asynch q'd database applications.  All you worry about is sending and receiving messages using TSQL, the rest of the messaging framework, including routing and security, is built into the database engine. 
 
This guy has an entire website devoted to Service Broker.  I've found this to be invaluable in getting past the little quirks.  
 
Service Broker Catalog Views
sys.service_message_types:  one row per message type registered
 
sys.service_contracts:  each contract in the db
 
sys.services: 
 
sys.service_broker_endpoints:
 
sys.remote_service_bindings:
 
sys.transmission_queue:  one row for each message in the transmission q
 
sys.routes: 
 
sys.service_queue_usages:  one row per service/q pair
 
sys.service_contract_message_usages
 
sys.service_contract_usages
 
sys.conversation_groups
 
sys.conversation_endpoints
 
sys.dm_broker_activated_tasks:  a row for each sp activated by SB
 
sys.dm_broker_forwarded_messages:  each message that the instance is in the process of forwarding. 
 
if you want to force-remove all messages from the transmission q,
ALTER DATABASE name SET NEW_BROKER
 
How to troubleshoot:
 
You can debug an activated sp by writing PRINT statements in it.  SB writes the output of the PRINT into the sql server error log file. 
 
If a sp is not activated, you can use the sys.service_queues catalog view to review fields such as is_activation_enabled, activation_procedure, is_receive_enabled, execute_as_principal_id, etc.  You need to confirm that the secuirty principal has EXECUTE permission on the sp. 
 
If messages remain in the transmission q (sys.transmission_queue) you can use the is_broker_enabled column of the sys.databases catalog view to check whether SB is enabled for the db.  You can also check the transmission_status col in sys.transmission_queue to see error text for the last error that occurred while trying to deliver messages for a specific dialog. 
 
ssbdiagnose is helpful from the commandline.  
 
Leaving retention on in a production environment is a bad idea since there is no easy way to purge.  
 
consider using 2 q's, one for the messages and one for acknowledgements.  In this way the acks don't block the messages.   
 
Asynchronous Procedure Execution
This is the feature I probably like the best.  Here are some scripts I started hacking together to test things, just haven't implemented them yet.  
 
Denali New Features - Contained Databases has some add'l information on ENABLE_BROKER vs NEW_BROKER
AttachmentSize
Binary Data sb_queries.sql3.69 KB

Add new comment