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? 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? 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 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 Message Type Message Contract Type Service Queue Message Type Contract Conversation Message Type Service 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 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 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 Concepts Queue Creation syntax CREATE QUEUE ExpenseQueue WITH STATUS=ON, ACTIVATION ( PROCEDURE_NAME = expense_procedure, MAX_QUEUE_READERS = 5, EXECUTE AS 'ExpenseUser' ) ; 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 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 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 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 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 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 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 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 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 Concepts Message Type Message Contract Type Service Queue Message Type Contract Conversation Message Type Service 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 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 Demo 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 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 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 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 Questions?
Pages to are hidden for
"Intro to SSB SQL Server 2005 Service Broker - PowerPoint"Please download to view full document