intro to ssb sql server 2005 service broker l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Intro to SSB SQL Server 2005 Service Broker PowerPoint Presentation
Download Presentation
Intro to SSB SQL Server 2005 Service Broker

Loading in 2 Seconds...

play fullscreen
1 / 28

Intro to SSB SQL Server 2005 Service Broker - PowerPoint PPT Presentation


  • 209 Views
  • Uploaded on

Intro to SSB SQL Server 2005 Service Broker. Brian Jackson Microsoft Consulting Services. 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. What Is SSB?.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Intro to SSB SQL Server 2005 Service Broker' - babu


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
intro to ssb sql server 2005 service broker

Intro to SSBSQL Server 2005 Service Broker

Brian Jackson

Microsoft Consulting Services

agenda
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
what is ssb
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
why add messaging to the database
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
why add messaging to the database5
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
key concepts

Contract

Contract

Key Concepts

Message

Type

Message

Type

Service

Queue

Message

Type

Conversation

Message

Type

Service

key concepts7
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
key concepts8
Key Concepts
  • Service
    • Creation syntax:CREATE 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
key concepts9
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
key concepts10
Key Concepts
  • Queue
    • Creation syntax

CREATE QUEUE ExpenseQueue

WITH STATUS=ON,

ACTIVATION (

PROCEDURE_NAME = expense_procedure,

MAX_QUEUE_READERS = 5,

EXECUTE AS 'ExpenseUser' ) ;

key concepts11
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)
key concepts12
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
key concepts13
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
key concepts14
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
key concepts15
Key Concepts
  • Message types
    • Creation syntax for message type with schema validation:

CREATE XML SCHEMA COLLECTION SampleSchemaCollection AS

N'<?xml version="1.0" encoding="UTF-16" ?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

…schema definition omitted …’

CREATE MESSAGE TYPE

[//SampleSchema]

VALIDATION = VALID_XML WITH SCHEMA COLLECTION SampleSchemaCollection

key concepts16
Key Concepts
  • Dialog / Conversation
    • Synonymous
      • At one point, SSB included a “Monolog” abstraction, but not currently
    • Conversations—not messages—are 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
key concepts17
Key Concepts
  • Dialog / Conversation
    • Initiating a dialog

DECLARE @dialog_handle UNIQUEIDENTIFIER ;

BEGIN DIALOG CONVERSATION @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
key concepts18
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.
key concepts19
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
key concepts20

Contract

Contract

Key Concepts

Message

Type

Message

Type

Service

Queue

Message

Type

Conversation

Message

Type

Service

usage scenarios
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
usage scenarios22
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
architectural positioning
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
architectural positioning25
Architectural Positioning
  • SSB & BizTalk
    • SSB:
      • Can reliably deliver a message to another SQL Server instance with exactly-once in-order assurances
      • If that’s all you need, and you’ve 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
architectural positioning26
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 Server’s 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
resources
Resources
  • Community Site: http://www.sqlservicebroker.com/forums
  • Free book chapter: http://download.microsoft.com/download/3/8/1/38154d73-bc47-4e9f-a7f5-ca9beb118fde/Chapter15_w.pdf
the end

The End

Questions?