SQL Server 2008 Service Broker

  • Article

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 https://sqlblog.com/blogs/greg_low/ and https://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

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 https://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 https://msdn.microsoft.com/en-us/library/ms178528.aspx

Queue

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 https://msdn.microsoft.com/en-us/library/ms187377.aspx
  • Use SEND to send a message, specify the conversation
  • See https://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

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 https://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 https://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 https://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