Post-it notes: Chapter 12 – Service Broker

April 8, 2013

Flirting with the idea of taking exam [70-432: Microsoft SQL Server 2008, Implementation and Maintenance] I created loads of post-it notes based on the book: “The Real MCTS SQL Server 2008 Exam 70-432 Prep kit”. I’ve decided to re-visit these notes (before the stickiness wears off and I lose them) and transfer them onto this blog. This will ultimately be a great knowledge refresher and quick reference guide for me. Hopefully you’ll find them useful too. Thanks for reading.


SQL Service Broker (SSB)

  • Collection of objects in a db that when configured can be used to send msg’s from one process to another
  • Used for Async Tasks
  • SQL 2005 introduced SSB
  • Msg delivery guaranteed in order sent
  • Msg can only be received once
  • Msg always processed in correct order
  • SSB can only be used by SQL Server. Have to connect to SQL to use SSB.
  • Any process can send with correct rights
  • Any database (incl system) can use SSB
  • SQL Mail uses SSB
  • Two ways to receive a message
    • RECEIVE on demand: SQL job can call this command or a win app could log into db and then initiate the receive command.
    • Activated Stored Procedure: msg’s received an/processed automatically. It’s a normal SP which is started by receiving queue when msg received

SSB – Send messages scenarios

  • Send a msg within a DB
  • Send msg to another DB in same instance
  • Send msg to another DB on another server
  • Send msg to another DB on a SQL server hosted at 3rd party

Enabling and Resetting SSB

  • SSB comes ready in all databases
  • However it must be enabled (ALTER DATABASE SET NEW_BROKER)
  • If a DB is restored, have to reset broker (SET ENABLE_BROKER)
  • If you set NEW_BROKER on existing SB – loose in flight msg’s
  • SELECT is_broker_enabled from sys.databases

SSB conversation

  • Conversation > Messages
  • Conversation can contain multiple messages
  • Conversation exists between two services (queues)
  • Re-use conversation for many messages = increase performance (x4)
  • Receive on many messages single time = increase performance (x10)
  • Message body (single value, binary, xml, blank message)

SSB Message types

  • Conversation > Messages > Message Type
  • Definition of data within message
  • Definition of type of validation by SSB
  • 4 types of validation
    • NON E (default) – any data (text, num, xml, binary)
    • EMPTY – used for NULL/empty – cannot use ‘NONE’
    • WELL FORMED XML – correctly formed empty, saves writing validation routines
    • VALID XML WITH SCHEMA COLLECTION – create schema beforehand to use – (create XML schema collection)

SSB Contracts

  • Conversation > Messages > Message Type>Contract
  • 1 contract per conversation
  • Contract defines the Message type
  • Which end of conversations uses which msg type
  • CREATE CONTRACT – there is no ALTER or DROP

SSB Message and Contract views

  • Sys.service_message_types – row per msg type
  • Sys.service_contracts – row per contract
  • Sys.service_contract_message_usages – row per contract/msg pair


  • Queue is where messages are sent too
  • Physical object in a DB
  • Specify filegroup
  • Queue STATUS on/off – enable or disable queue from receiving messages
  • Queue RETENTION on/off – keep records after being received
  • Queue ACTIVATION – activated stored proc – allow SQl to start SP when msg arrives in queue.
  • 3 step
    • SET SP as activate procedure (ALTER QUEUE)


  • Bind contract to queue
  • Msg’s are sent from service to service
  • Service binds single queue to many contracts
  • Service names are case sensitive

Endpoints (TCP/IP)

  • SQL Server can listen for endpoints on http or tcp
  • SSB requires that listener be a TCP endpoint
  • 1 end point per SQL Server SSB (server wide) – all db’s use one endpoint
  • Tell SSB about remote endpoints (routes)
  • End point allows access to a SQL SSB
  • Cannot share SSB endpoint between 2 instances
  • Same server, then you need two different TCP/IP ports
  • Endpoint traffic is encrypted
  • ENDPOINT can be in 3 states
    • Started – Accepts remote connections
    • Stopped – Listening for removed connections + rejects them
    • Disabled – tcp port closed, connection fails, could not be found


  • Used to inform SSB that msg is for another server
  • Sys.routes
  • DROP ROUTE/CREATE ROUTE – run on both server so know about each other
  • Routes can expire or be deleted
  • Require the IP, PORT and GUID of SSB

Sending a Message

    • BEGIN DIALOG xxx
    • FROM SERVICE xxx
    • TO SERVICE xxx
    • ON CONTRACT xxx
    • SET @msg = (select X …)
    • MESSAGE TYPE @msg
  • RECEIVE from queue
    • End conversation (sends ack back to sender)


  • Sys.conversation_endpoints
    • Info about inflight conversations
    • Info about recent conversations
    • Single row for each conversation
    • State column – identify if source or dest has issue
    • Info on number of msg/conversations
    • When conversations will expire
  • Sys.transmissions_queue
    • Info on stalled msg
    • Single record for each stalled msg
    • Single record for each in-flight msg
    • Transmission_status column – text what issue may be. No value in the column indicates OK (in-flight)
    • Resolve/error msg will move to new record
  • Sys.service_message_types – meta data msg types
  • Sys.service_contracts – metadata contracts
  • Sys.service_contract_msg_usages – link contract to msg types
  • Sys.service_queues – meta data queues
  • – meta data services
  • Sys.service_contract_usages – service bound to contracts
  • Sys.service_queues_usages – queues bound to services
  • Sys.service_broker_endpoints
  • Sys.tcp.endpoints
  • Sys.routes


 Thanks for reading.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: