Intro to SSB SQL Server 2005 Service Broker

1
Intro to SSBSQL Server 2005 Service Broker

• Brian Jackson
• Microsoft Consulting Services

2
Agenda

• What is SSB?
• Why Add Messaging to the Database?
• Key Concepts
• Usage Scenarios
• Demo
• Architectural Positioning
• SSB and MSMQ
• SSB and BizTalk
• SSB and Indigo
• Questions

3
What Is SSB?

• SQL Server 2005 Service Broker
• Allows internal or external processes to send and
  receive guaranteed, asynchronous messages using
  extensions to Transact-SQL Data Manipulation
  Language (DML)
• In short Asynchronous messaging technology
  built directly into SQL Server 2005

4
Why Add Messaging to the Database?

• To address reality
• Lots of customers use the database as a message
  queue already
• The hard problems in messaging are difficult to
  implement correctly
• SSB eliminates the need for customers to create
  custom solutions for SQL Server based message
  queuing
• The queue handling code built into the database
  kernel handles the locking, ordering, and
  multithreading issues associated with most
  home-grown database queues

5
Why Add Messaging to the Database?

• To take advantage of transactional support in the
  DBMS
• Service Broker also supports only transactional
  messaging
• Transactional queuing technologies outside the
  DBMS (e.g., MSMQ) require 2 phase commit
  transactions via the DTC
• To integrate backup, recovery and administration
  with SQL server
• To provide near real time failover via database
  mirroring

6
Key Concepts
Message Type
Message Type
Service
Queue
Message Type
Conversation
Message Type
Service
7
Key Concepts

• Service
• A name for a specific task or set of tasks
• Messages are sent to services and stored in the
  queue associated with the service
• Service name used to route messages, deliver
  messages to the correct queue within a database,
  and enforce the contract for a conversation

8
Key Concepts

• Service
• Creation syntaxCREATE SERVICE TestService
  AUTHORIZATION dbo ON QUEUE TestQueue
  (//Contract1,Contract2)
• Specifies name, owner, associated queue name, and
  optionally the contracts on which the service can
  receive messages
• If no contracts are specified, the service can
  only initiate messages

9
Key Concepts

• Queue
• A named container for holding messages while they
  await processing
• Provides loose coupling between sender and
  receiver
• May or may not have a service program associated
  with it

10
Key Concepts

• Queue
• Creation syntax
• CREATE QUEUE ExpenseQueue
• WITH STATUSON,
• ACTIVATION (
• PROCEDURE_NAME expense_procedure,
• MAX_QUEUE_READERS 5,
• EXECUTE AS 'ExpenseUser' )

11
Key Concepts

• Service Program
• Any program that sends or receives messages via
  SSB
• Can be a T-SQL stored procedure, CLR stored
  procedure, or external program that is activated
  when the first message arrives in the queue
• As number of messages grows, additional instances
  of activated service programs may be created, up
  to the number specified in MAX_QUEUE_READERS
• Note the external activator is not available in
  the current public beta (Yukon Beta 2)

12
Key Concepts

• Contract
• Defines the message types used in a conversation
• Determines which side of the conversation can
  send messages of that type
• Each conversation follows a contract that the
  initiating service specifies when the
  conversation begins
• Both sides of a conversation must define the same
  contract

13
Key Concepts

• Contract
• Creation syntax
• CREATE CONTRACT //ContractName (
• //MessageTypeOne SENT BY INITIATOR,
• // MessageTypeTwo SENT BY TARGET,
• // MessageTypeThree SENT BY ANY )
• Sent by
• Initiator the endpoint that starts a
  conversation with BEGIN DIALOG CONVERSATION
• Target the dialog endpoint that accepts a
  conversation that was started by another service
• Any messages of this type can be sent by both
  the initiator and the target

14
Key Concepts

• Message Type
• A named definition of a format for messages
  exchanged between services
• Persisted in the database where the message type
  is created.
• Identical message type created in each database
  that participates in a conversation
• 4 validation options for message type instances
• NONE
• EMPTY
• WELL_FORMED_XML
• VALID_XML WITH SCHEMA COLLECTION

15
Key Concepts

• Message types
• Creation syntax for message type with schema
  validation
• CREATE XML SCHEMA COLLECTION SampleSchemaCollectio
  n AS
• N'lt?xml version"1.0" encoding"UTF-16" ?gt
• ltxsdschema xmlnsxsd"http//www.w3.org/2001/XMLS
  chema"
• schema definition omitted
• CREATE MESSAGE TYPE
• //SampleSchema
• VALIDATION VALID_XML WITH SCHEMA COLLECTION
  SampleSchemaCollection

16
Key Concepts

• Dialog / Conversation
• Synonymous
• At one point, SSB included a Monolog
  abstraction, but not currently
• Conversationsnot messagesare the messaging
  primitive in SSB
• Any program (including a service program) that
  has access to SQL Server can create a
  conversation
• An initiating service must begin a conversation
  with the target service before sending a message
  to the target service
• Conversations are the unit of message correlation
  and ordering

17
Key Concepts

• Dialog / Conversation
• Initiating a dialog
• DECLARE _at_dialog_handle UNIQUEIDENTIFIER
• BEGIN DIALOG CONVERSATION _at_dialog_handle
• FROM SERVICE //InitiatorService
• TO SERVICE '//TargetService'
• ON CONTRACT //ContractName
• WITH LIFETIME 60
• Reliable, in-order, once-only delivery
• Conversations can be long-running

18
Key Concepts

• Conversation Group previously called Service
  Instance
• A user-defined grouping of conversations
• For example All the conversations required to
  process a single order
• Order conversation (Order Header, Order Line)
• Inventory service conversation (Inventory Check,
  Inventory Response)
• Shipping service conversation (Shipping Request,
  Shipping Response)
• Purchasing service conversation (Purchasing
  Request, Purchasing Response)
• Defines state scope and locking scope
• Conversation group lock
• Defines the locking scope for all the
  conversations involved in processing single
  application unit.
• Different parts of the application logic may be
  executing on different threads simultaneously.
  This is one of the things that makes writing
  loosely-coupled asynchronous applications
• A conversation group lock is required for any
  conversation receives or sends.
• In an order-entry application with hundreds of
  active threads, a single order is only processed
  on one thread at a time which greatly simplifies
  asynchronous programming.

19
Key Concepts

• Routes
• Used for conversations between different
  instances of SQL Server
• A route maps a service name to a physical
  location
• Provides location transparency
• Supports load balancing
• Allows delivery to a specific instance of a
  service

20
Key Concepts
Message Type
Message Type
Service
Queue
Message Type
Conversation
Message Type
Service
21
Usage Scenarios

• Asynchronous distributed applications
• Travel Agency Sample
• Booking a trip requires potentially long running
  conversations with hotel, air, and car rental
  systems.
• Send messages via SSB, correlate the results,
  commit the transaction
• Email the confirmation to the customer

22
Usage Scenarios

• Scale out batch processing
• Loan Processing Sample
• Starting from an input file of loans, several
  correlated steps must occur in sequence
• Lifecycle stages import, normalize, validate,
  price, fund
• A loosely coupled, message based architecture
  allows processing to be distributed over one or
  more hardware assets
• Additional workload can be accommodated by adding
  hardware

23
Demo
24
Architectural Positioning

• SSB MSMQ
• SSB
• Service Broker can commit updates to the message
  queue, database data, and application state in a
  simple database transaction. MSMQ requires a
  two-phase commit to do the same thing.
• MSMQ message ordering is assured within a single
  transaction. Service Broker message ordering in
  a dialog is assured across transactions, sending
  applications and receiving applications.
• The maximum MSMQ message size is 4MB. The
  maximum Service Broker message size is 2GB.
• MSMQ
• MSMQ offers express, reliable, and transactional
  message styles while Service Broker is
  transactional only.
• MSMQ can communicate between virtually any pair
  of Windows applications and with the MQ-Series
  bridge can talk to applications on a wide variety
  of hardware and software. Service Broker can
  only communicate between applications connected
  to SQL Server.
• MSMQ offers both a TCP/IP binary protocol and an
  HTTP SOAP protocol for communications. Service
  Broker is binary TCP/IP only for Yukon.
• The scope of a Message Queuing transaction is the
  local computer, and Message Queuing does not
  guarantee end-to-end delivery

25
Architectural Positioning

• SSB BizTalk
• SSB
• Can reliably deliver a message to another SQL
  Server instance with exactly-once in-order
  assurances
• If thats all you need, and youve got SQL Server
  2005, then SSB is a good fit
• BizTalk
• Can reliably deliver a message to another SQL
  Server instance with exactly-once in-order
  assurances
• Can manipulate the contents of messages, map
  message formats, manage message processing,
  manage workflows, manage state, send messages
  over multiple different transports
• If you need these features, you need BizTalk

26
Architectural Positioning

• SSB Indigo
• SSB
• Supports reliable, transactional messaging over
  TCP/IP using a proprietary protocol between SQL
  Server instances
• Crisp failure semantics, tight integration with
  SQL Servers transaction management
• Indigo
• Supports many different messaging styles over a
  variety of standards-based protocols between
  Windows and any OS that implements the standard
  protocols that Indigo supports
• Rich extensibility model based on pipelines
• Less full featured than SSB for SQL-to-SQL
  connections

27
Resources

• Community Site http//www.sqlservicebroker.com/f
  orums
• Free book chapter http//download.microsoft.com/
  download/3/8/1/38154d73-bc47-4e9f-a7f5-ca9beb118fd
  e/Chapter15_w.pdf

28
The End

• Questions?