SQL Service Broker

1. SQL Service Broker Gerald Hinson Development Lead – SSB team HPTS 2005

2. THESIS: Queues are Databases [1] Jim Gray - December 21, 1995 Abstract: Message-oriented-middleware (MOM) has become an small industry. MOM offers queued transaction processing as an advance over pure client-server transaction processing. This note makes four points: • Queued transaction processing is less general than direct transaction processing. • Queues are interesting databases with interesting concurrency control. • Queue systems need DBMS functionality. • Queue managers are simple TP-monitors managing server pools driven by queues. [1] Acknowledgments: These ideas derive from discussions with Andrea Borr (Oracle), Richard Carr (Tandem), Dieter Gawlick (Oracle), Pat Helland (Microsoft), Franco Putzolu (Oracle), Andreas Reuter (U. Stuttgart) and Bill Highliman (NetWeave).

3. THESIS: Queues are Databases [1] Jim Gray - December 21, 1995 • Just for fun, here’s my favorite quote from the paper: “This position paper was intended to generate controversy at the High Performance Transaction Processing Workshop (HPTS). Amazingly, everyone either agreed or was so disgusted that they left the room. In the end, there was no heated discussion. I was astonished.”

4. SQL Server 2005 - Mild-mannered database becomes App Server • What did we add? • Reliable messaging, but with a new twist… • Application activation • Launching of either stored procs or .EXEs to consume queued messages • CLR hosting for VB, C# based stored procs • Better XML support (XPath / XQuery) • Why? • Customer demand • They weren’t happy with the separate MOM either… • Dieter did it for Oracle • I still remember IMS DB/DC (and DB/2)

5. 1 of everything vs. 2 of everything • When messaging is merged into the database customers get: • 1 programming model • 1 product to install, configure, monitor • 1 backup story, 1 failover story • Because ALL app data (queues, session state, etc.) lives in a single database • 1 data warehousing story • Queued messages and application session state may be queried against • Simplified deployment of applications • Cool replication synergies here It’s a natural pattern. • Customers keep building it themselves (vs. using dedicated queuing products such as MSMQ, MQ-Series, etc.)

6. Rationale for a new communication primitive • Programming Model issues : • This is a typical IT scenario and its typical solution. • This is the obvious, simple solution.. But, it doesn’t work… Send PO Request Loop until EOD msg Recv Line Item Insert into… End-Loop Finalize Bill Recv PO request Select * from PO_tbl Loop until done Fetch Line Item Send Line Item End-Loop Send EOD msg PO Program Billing Program

7. Rationale for a new communication primitive (cont.) Billing Program (Instance 1) • Adding multiple instances to drain the queue faster breaks the app! • Exceptions, deadlocks and contention are all common outcomes. App programmers get burned by this a lot using existing message queuing solutions. • The typical workaround is to force all data into a single message… But, that leads issues with message size, latency, etc. Billing Queue Line Item 1 Billing Program (Instance 2) Line Item 2 Billing Database Line Item 3 Incoming PO data from PO Service EOD msg This one runs first and finalizes the bill early Billing Program (Instance 3) End-of-Q

8. Rationale for a new communication primitive (cont.) • Programming Model issues : • Weak correlation of related messages • No support for relating messages and application state • In the example above, each instance of the Travel service may deadlock another trying to process the responses from the back-end services. Hotel Travel Travel Client Travel Airline Car Rental DB Single Business Transaction

9. Rationale for a new communication primitive (cont.) Travel Program (Instance 1) • Adding multiple instances to drain the queue faster breaks the app here too! • Composition of services becomes too hard for application programmers… (SELECT, followed by app locks, followed by…) Travel Queue Hotel Reply Travel Program (Instance 2) Airline Reply Travel Database Car Reply Incoming replies from back-end services. End-of-Q Each instance of “Travel” tries to work on the same business tran. Travel Program (Instance 3)

10. Rationale for new communication primitive (cont.) • Programming model conclusions: • Building composable message-based services is harder than it should be. • Atomic messages, as a communication primitive, make building composable services hard. • Similar lessons were learned at the network layer • UDP has become a niche solution. TCP’s added value makes like simpler.

11. Dialogs - A new communication primitive • SQL Server apps (read: Services) communicate via formal, reliable sessions known as Dialogs • Services communicate with other services by beginning a dialog and sending / receiving messages on it. Examples of new DML: • BEGIN DIALOG [CONVERSATION] @dialogHandle FROM SERVICE [CustomerService] TO SERVICE ‘TravelService' • SEND ON CONVERSATION @dialogHandle MESSAGE TYPE [BookTravelRequest] … • RECEIVE * from MyQueue WHERE … or if you want to block… WAITFOR (RECEIVE * from MyQueue), TIMEOUT = milliseconds • RECEIVE returns spooled message for one or more dialogs • END CONVERSATION @dialogHandle

12. Dialog Customer Service Travel Service Dialogs – A new communication primitive (cont.) • Dialogs provide two-way messaging between two services • Dialogs offer: • Guaranteed delivery • Full-duplex or half-duplex communication • Exactly-once, in-order delivery • Private / public key based security • Dialogs: • May be long-lived (years) or short-lived (seconds) • Are light-weight • Are sessions (similar to TCP conceptually) Database A Database B

13. Conversation Groups ConvGroup1 Dialog1 Dialog2 ConvGroup1 Hotel ConvGroup1 Dialog3 ConvGroup1 Dialog4 Dialog 2 Dialog 1 Client Travel Dialog 3 Airline Dialog 4 Car Dialogs 2,3,4 share a Conversation Group with Dialog 1 because the Travel program chose to relate them at dialog creation time. When a transaction locks one of them (at dequeue time) it locks them all. They are unlocked when the transaction is committed. The Conversation Group is owned and localized to the Travel program. This identity does not travel on the “wire”.

14. Conversation Groups • Conversation Groups: • Formalize the relationship between dialogs • Have a unique identity • Are a serializable unit in SQL Server. Only one transaction may lock a conversation group at a time. • Dialogs are always associated with a conversation group, hence only one transaction can lock a dialog at a time as well. • Provide a great key (identity) to associate with application state • If there are > 1 readers on a given queue, they will never accidentally process related messages, dialogs or (if the developer uses this key correctly) application state. • Conversation Groups are meant to: • Simplify application state management • Enable “business transactions” • Conversations are grouped using an optional expression on the BEGIN DIALOG verb (RELATE TO…).

15. Composed Services via dialogs Travel Program (Instance 1) • Adding multiple instances to drain the queue faster is now safe. • Composition of services is now trivial. • Application state can be stored using the Conversation Group as a key. Travel Queue Dialog2 Hotel ConvGroup1 ConvGroup1 Dialog3 Airline Travel Program (Instance 2) Car ConvGroup1 Dialog4 Travel Database Dialog5 Hotel ConvGroup2 ConvGroup2 Dialog6 Airline Only Instance 1 can dequeue the messages… Travel Program (Instance 3) ConvGroup2 Dialog7 Car End-of-Q

16. Related messages via dialogs Billing Program (Instance 1) • Adding multiple instances to drain the queue faster is now safe. • Related messages are not dequeued by different instances Billing Queue Dialog1 Line 1 ConvGroup1 ConvGroup1 Dialog1 Line 2 Billing Program (Instance 2) Line 3 ConvGroup1 Dialog1 Billing Database Dialog1 EOD ConvGroup1 ConvGroup2 Dialog2 Line 1 Each transaction is isolated from POs already locked by another tran… Billing Program (Instance 3) ConvGroup2 Dialog2 Line 2 End-of-Q

17. Deployment Issues with existing messaging products • “Plumbing” issues: • Weak delivery guarantees: • Most don’t guarantee EOIO “end-to-end” when there are intermediate nodes in the topology. • Topology changes like this break many existing message queuing applications! • This becomes problematic as an organization tries to manage large message traffic • 10,000 workstations  1 server doesn’t work! Msg Broker 2 Msg Broker 1 Messages arrive in order. Messages arrive out-of-order!! Msg Broker 3 Msg Broker 1 Msg Broker 2 Msg Broker 4

18. Travel Service Delivery guarantees via dialogs (ie. Dialogs aren’t just about programming model) Msg Broker 1 (Database) • Dialogs have sessions IDs that are “end-to-end, no matter how many hops. • All routing, retries and ACKs are done per dialog vs. per msg broker. • Security is per dialog as well (Asymmetric/Symmetric key hybrid). Msg Broker 2 (Database) Dialog Customer Service Msg Broker 3 Msg Broker 4

19. Avoiding 2 Phase Commit Messages and data in one store Hardware has come a long way 15,000 RPM drives on desktop computers Sequential writes > 50MB / second 100Mb networks < 12MB bandwidth Reliable messaging in SQL can be VERY fast Databases achieve perf via sequential write speeds (ie. write-ahead logging) Applications can now have EOIO semantics AND high message throughput Performance Gains – subtitled per Helland (faster than greased …) SQL Server Other message store LOB Data Queue Inefficienttransaction commit SQL Server LOB Data Queue Very efficienttransaction commit

20. Scale single-broker service Multiple “worker-bee” exe Consume work off a single broker’s queue Scale multi-broker service Load-balance a service type across multiple brokers Each broker equivalent “Scale-out” brokers This model is great for multi-user, read-only, highly-replicatable data Examples include catalogs, phonebooks, price lists, etc. Broker exe exe . . . exe Broker exe exe exe . . . . . . . . . exe exe exe Broker Broker Broker Two ways to scale using Service Broker

21. SQL Express is free! • Tier 1 • 1000s of machines • SQL Express • Tier 2 • 100s of machines • SQL Express / Workgroup • Tier 3 • 10s of machines • SQL Server Enterprise • This is all about reducing connections and transactions from lots to few by the time you reach the back-end • 10,000 transactions on the front-end cannot equate to 10,000 on the back-end!

22. Database Q Q Rdr Internal Activation of Services • Activation comes in two flavors: • Internal (we built it) • External (we enabled you to build it) • Internal Activation • A stored procedure may be associated with a Service’s queue using the following syntax: • CREATE QUEUE myqueue (WITH ACTIVATION STATUS = ON, PROCEDURE = db.schema.procedurename, EXECUTE_AS = [valid database user], MAX_READERS = n) • NOT a trigger • Separate Transaction, Security, and Thread • New parallel queue readers if needed (and not yet MAX_READERS) • Lots of customers are leveraging this for all sort of async database work

23. External Activation of Services • External Activation • Some customer don’t want to (or cannot) leverage stored procedures • SQL Server can: • Send a reliable message to a specified “notification” queue when a new queue reader is needed • One “notification” queue can serve many application queues • Message only sent when queue readers are lagging • We provide a nice sample of how to leverage this event to build an external activator. • This sample will ship with SQL Server.