intro to ssb sql server 2005 service broker n.
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

Intro to SSB SQL Server 2005 Service Broker

227 Vues Download Presentation
Télécharger la présentation

Intro to SSB SQL Server 2005 Service Broker

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

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

  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 STATUS=ON, 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 SampleSchemaCollection AS N'<?xml version="1.0" encoding="UTF-16" ?> <xsd:schema xmlns:xsd="" …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 • 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

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

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

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

  27. Resources • Community Site: • Free book chapter:

  28. The End Questions?