SQL Server 2008 Service Broker

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.

Service Broker

• 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?

Service Broker

• 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

Message types

• 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

Service

• 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.

Contracts

• 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

• 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

Internal Activation

• 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.

External Activation

• 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
• http://www.microsoft.com/downloads/details.aspx?familyid=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en
• After installing MSI, look for file SSBEA.doc under C:\Program Files\Service 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…

Design

• 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.

Bad messages

• 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

Routing

• 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”

Forwarder

• 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

Conversation Priority

• 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”

Security

• 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

• 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

Management

• 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

Monitoring

• 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

Performance

• 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

Books

• "The Rational Guide to SQL Server 2005 Service Broker" by Roger Wolter
  http://www.amazon.com/Rational-Server-Service-Broker-Preview/dp/1932577203
• "Pro SQL Server 2008 Service Broker" by Klaus Aschenbrenner
  http://www.amazon.com/Pro-Server-2008-Service-Broker/dp/1590599993