Building Distributed Asynchronous Applications With SQL Service Broker

Building Distributed Asynchronous Applications With SQL Service Broker Gerald Hinson, Roger Wolter DAT 303 Dev Lead Microsoft Corporation 1

Agenda What is Service Broker? Service Broker Basics CLR Services Conversation Group Locking WCF Channel Remote Service Broker 2

SQL Service Broker Platform for building asynchronous, loosely coupled database applications Queues are database objects Queue locking reduces conflicts and deadlocks Dialogs give unprecedented message ordering assurance Ordered across transactions, input applications and output applications New DDL and DML for messaging Use the same APIs and tools as SQL Reliable messaging for scale out 3

Dialogs provide two-way messaging between two services Dialogs offer Guaranteed delivery Exactly-once delivery In-order delivery Secure communications Dialogs May be long-lived (years) or short-lived (seconds) Are lightweight Are persistent sessions Customer Service Travel Service Dialog Database A Database B 4

Services A Service is a named endpoint that may be targeted with messages Its interface is defined by the messages it consumes and emits Services are scoped by (live in) databases It is responsibility of a service’s Service Broker to 1) Deliver messages sent from that service to other services 2) Store messages that are sent to that service until the service can process them Every service is mapped to a Queue. Messages sent to a service are stored in its associated queue Queues are a new table-like object introduced in SS 2005 Messages are dequeued using a new verb: RECEIVE Messages may be “peeked” using SELECT 5

Internal Activation QUEUE Stored Procedure 6

Service Broker Basics Gerald Hinson Dev Lead SQL Server 7

External Activation Queue Service Event Queue External Activator 8

CLR Services Gerald Hinson Dev Lead SQL Server 9

Conversation Group Locking QUEUE Dialog 1 Dialog 2 Queue Reader X Queue Reader 10

Conversation Group Locking Gerald Hinson Dev Lead SQL Server 11

WCF (“Indigo”) Channel For SSB Client Service Typed Proxy Dispatcher SSB Channel ADO. NET TDS SQL Server TDS Service Broker Routing SQL Server 12

WCF Channel Gerald Hinson Dev Lead SQL Server 13

Dialog Security Initiator FROM Service Target DB User Certificate Authorization Public Key Certificate Private Key Certificate Message Public Key Header TO Service Certificate Private Key Remote Service Binding DB User 14

Routing SEND sys. routes Classifier 15

Remote Service Broker Gerald Hinson Dev Lead SQL Server 16

Service Broker Scenarios Asynchronous DB “Stuff” Asynchronous Triggers and Events Parallel Stored Procedures Batches and scheduled tasks Data Warehouse population Delayed actions in different transactions Reliable Messaging Smart Clients Point of Sale Shop Floor Scaling out asynchronous stuff Reliable Service Oriented Architecture 17

Resources http: //blogs. msdn. com/ rushidesai/ http: //forums. microsoft. com/ msdn/Show. Forum. aspx? Forum. ID=91 http: //www. sqlservicebroker. com/forums/ http: //msdn. microsoft. com/ sql/2005/ rwolter@microsoft. com 18