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. 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="http://www.w3.org/2001/XMLSchema" …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: http://www.sqlservicebroker.com/forums • Free book chapter: http://download.microsoft.com/download/3/8/1/38154d73-bc47-4e9f-a7f5-ca9beb118fde/Chapter15_w.pdf

28. The End Questions?