Here are some notes on “SQL Server 2008 Service Broker” I took while attending an advanced class on SQL Server taught by Greg Low (from http://sqlblog.com/blogs/greg_low/ and http://www.sqldownunder.com/).
Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without some pre-requisite knowledge and the right context. Reading the post from top to bottom will help.
- Reliable transactional ordered message queue
- Lives inside the SQL Server databases, can talk to other SQL Server brokers
- Managed using DLL, with limited GUI
- See http://msdn.microsoft.com/en-us/library/ms345108.aspx
What is a queue?
- Traditional apps: tight coupling, everything done at once, right there and then
- Example: Add new product – Need to get an e-mail after that happens
- Example: Implemented as a trigger, e-mail is down, cannot insert product (?!)
- Example: E-mail is not really in the context of the insert transaction
- Lots of complex process operations bundled together
- Example: Is the person serving burgers responsible for re-ordering the buns?
- The idea is that you put the request to send the e-mail in a queue and forget it
- Need to have a guarantee that it will happen, we need to trust it
- Need to have a mechanism to handle the asynchronous response
- You are effectively building a workflow, using a message-based architecture
- It is a different mindset
- Discussion: How the old MSMQ worked. How is Service Broker better?
- When to use it: Async programming, data push, queueing, asynchronous triggers
- When to use it: Workflows, cross-DB business process, distributed server-side processing
- When to use it: Data consolidation for client apps, large scale batch processing
- Example: Order processing, consolidating data from branches
- Built on top of it: Notifications, Mail, Query Notifications
- When not to use: Just moving table from here to there. Use replication instead
- Built into the database, works with transactions
- Management – Backup/Restore, Mirroring
- Description of what I will put in the message
- Types: NONE (opaque) , EMPTY (no body), WELL_FORMED_XML, VALID_XML (schemas)
- VALID_XML WITH SCHEMA COLLECTION : multiple schemas, validated, performance impact
- SQL Server 2008: DEFAULT message type: NONE
- See http://msdn.microsoft.com/en-us/library/ms187744.aspx
- Logical source or destination of the message. The service itself.
- Has an address, but the location is abstract. You can route to get to it.
- Rules of the conversation. Zero or more per service.
- Here are the conversations that you can be a target of.
- What you are allowed to talk about.
- Who can send messages: INITATOR, TARGET, ANY
- SQL Server 2008: DEFAULT contract is ANY
- See http://msdn.microsoft.com/en-us/library/ms178528.aspx
- Queue of messages a service needs to process
- Phisical end point - Storage associated with a service
- It’s an internal table, you can see at sys.internal_tables
- No INSERT/UPDATE/DELETE
- From the service perspective: You can SELECT (peek) or RECEIVE (read and take it out of the queue)
- See http://msdn.microsoft.com/en-us/library/ms190495.aspx
- See http://msdn.microsoft.com/en-us/library/ms186963.aspx
Dialogs and Conversations
- Dialog is a form of two-way conversation, guaranteed message order, can be prioritized
- Who I want to talk to (target), what about (contract)
- Nothing happens until a message is sent
- Conversation can start before the target even exists
- Careful – Always correctly finalize all conversations. It only ends when both services say so
- Conversation Groups: Related conversations, unit of locking, unit of receive
- Default is one conversation per group
- See http://msdn.microsoft.com/en-us/library/ms187377.aspx
- Use SEND to send a message, specify the conversation
- See http://msdn.microsoft.com/en-us/library/ms188407.aspx
- Activation Execution Context: Use “EXECUTE AS” to control this
- Activation Execution Context: Can also use signed activation procedures
- Troubleshoot: You can use PRINT or RAISERROR to create log entries
- Activation – SQL calls “something” to process Queued messages automatically.
- Internal - Stored Procedure associated with a queue – Multiple readers possible
- Avoid architecture that relies on message order
- Avoid procedures that handle just one message, get a set
- After processing the set, don't just exit. Wait and repeat in the end.
- Only exit if there are no messages in the queue after a delay.
- Call a program or Windows service to receive from the queue
- Available as a Feature pack for download
- Microsoft SQL Server 2008 Feature Pack, October 2008
- After installing MSI, look for file SSBEA.doc under C:Program FilesService Broker
- See http://blogs.msdn.com/sql_service_broker/archive/2008/11/21/announcing-service-broker-external-activator.aspx
How it flows
- Service wants to send messages to another server.
- Service (Initator) starts a conversation with another server (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.
- Books Online: Service Broker Tutorial (17 lessons)
- See http://msdn.microsoft.com/en-us/library/bb839489.aspx
Demo – Creating a “Message Log” queue
- ALTER DATABASE … SET ENABLE_BROKER
- CREATE MESSAGE TYPE … VALIDATION=WELL_FORMED_XML
- CREATE CONTRACT … SENT BY INITIATOR
- SELECT * FROM sys.service_message_types – there’s a bunch already there…
- CREATE PROCEDURE … -- Inside, there’s: RECEIVE TOP(1) … END CONVERSATION
- CREATE QUEUE … WITH STATUS=ON, ACTIVATION ( STATUS=ON, PROCEDURE_NAME=…)
- CREATE SERVICE … ON QUEUE …
- In calling code…
- BEGIN DIALOG CONVERSATION … FROM SERVICE… TO SERVICE… ON CONTRACT…
- SEND ON CONVERSATION … MESSAGE TYPE …
- END CONVERSATION …
- Send message… Nothing happened. Rats!
- In the application log: “Service Broker need to access the master key in the database …”
- CREATE MASTER KEY ENCRYPTION …
- Send message again… I get two messages!
- The first one was there in sys.transmission_queue. Always look there…
- Avoid message by message processing
- Anti-pattern : Non batchable, same transaction, triggers, copying data
- Payload type? Binary, XML without schema, XML with schema
- How many services? SOA principle: autonomous in terms of both logic and data
- How many conversations? It depends 🙂
- Should I SELECT first then RECEIVE? No. Just RECEIVE.
- Do not re-implement messages that are already pre-defined
- sys.transmission_queue is always there.
- Can I use rollback for retries? No! Just process the message…
- How to handle bad messages? Persist in side table or send to another queue
- Poison message handling – Can’t process the message (i.e., PK violation), no point retrying
- Track the scenario that causes the problem, like constraint violation, message format changed.
- Demo: Include a UNIQUE constraint on the log, send duplicates, watch results
- Demo: ALTER the activation procedure to handle the situation
- Intra or inter-instance communications possible.
- Routing tables in MSDB, per database, to locate other service.
- For inter-instance, requires endpoint setup (also used for Mirroring).
- Router: Service name, Broker instance identifier, network address
- Broker ID: Find with “SELECT service_broker_guid FROM sys.databases”
- Careful - Some of this is case-sensitive!
- For conversations from local DB, find with “SELECT * FROM sys.routes”
- For incoming external conversations, find with “SELECT * FROM msdb.sys.routes”
- There might be multiple routes to get to something.
- Forwarder is a server willing to accept messages on behalf of another server
- Forwarder can provide some fault tolerance (you can have multiples)
- Forwarder authentication, cross-domain – Certificates… Make sure everyone has the right copy
- Case: Message asking “how busy are you?” goes to the back of the queue… 🙂
- Need a mechanism to have a message that jumps to the front of the queue
- Priority: Ranges from 1 to 10, 5 is the default. DDL used to CREATE/ALTER.
- Priority on contract, local service name, remote service name. Applies only to one end.
- Backwards compatibility: In sys.database, there’s an “is_honor_broker_priority_on”
- Transport security defined at the endpoint
- Specified on CREATE ENDPOINT: Windows or Certificate authentication. No more anonymous.
- Windows authentication uses SQL Server service account.
- Dialog security is end-to-end.
- Two options: one per “far” service (anonymous binding) or one per conversation.
- Message retention is defined at the queue level
- Important to keep messages for auditing, compensating transactions
- If enable, messages retained until the end of the conversation, can reduce performance
- SSMS has a Service broker node under the database
- You can see lots of items, properties.
- Unfortunately, it will just throw you in “Query” dialogue with a template at times
- What is there and is not moving, and why?
- Error Handling: Must be part of the design (there are system-provided message types for that)
- Monitor the transmission queue
- Patterns: Use end conversation, recycling long lived dialogs, create and “end steam message”
- DMV: sys.dm_broker%
- Performance monitor: SQL Server: Broker %
- Trace events, XEvents
- SSBDiagnose: verify broker setup
- See http://msdn.microsoft.com/en-us/library/bb934450.aspx
- Key: Number of messages/second, speed of messaging processing
- Avoid message retention, avoid long transactions, end conversations
- Favor larger messages over multiple smaller messages
- White Paper: Service Broker: Performance and Scalability Techniques
- See http://msdn.microsoft.com/en-us/library/dd576261.aspx
Comparing with other technologies
- Discussion: Service Broker and WCF (Windows Communication Foundation)
- Discussion: Service Broker and BizTalk Server
- Discussion: Service Broker and MSMQ
- Discussion: Service Broker and WWF (Windows Workflow Foundation)
- Discussion: Service Broker and Replication
"The Rational Guide to SQL Server 2005 Service Broker" by Roger Wolter
- "Pro SQL Server 2008 Service Broker" by Klaus Aschenbrenner