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

Comments

  • Anonymous
    January 01, 2003
    PingBack from http://brokers.linkablez.info/2009/03/31/sql-server-2008-service-broker/
  • Anonymous
    January 01, 2003
    SQLServerServiceBroker为消息和队列应用程序提供SQLServer数据库引擎本机支持。这使开发人员可以轻松地创建使用数据库引擎组件在完全不同的数据库之间进行通信的复杂...
  • Last updated on