Gerald Hinson

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, heres 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 werent 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
• Its 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
  doesnt 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
Billing Program
PO Program
7
Rationale for a new communication primitive
(cont.)

• 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 Program (Instance 1)
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.)

• 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 Program (Instance 1)
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. TCPs 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 _at_dialogHandle
• FROM SERVICE CustomerService
• TO SERVICE TravelService'
• SEND ON CONVERSATION _at_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

12
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 B
Database A
13
Conversation Groups
ConvGroup1
Dialog1
Dialog2
ConvGroup1
Hotel
Dialog3
ConvGroup1
Dialog 2
Dialog4
ConvGroup1
Dialog 1
Travel
Client
Airline
Dialog 3
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 gt 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

• 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 Program (Instance 1)
Travel Queue
Hotel
Dialog2
ConvGroup1
ConvGroup1
Airline
Dialog3

Travel Program (Instance 2)
Car
Dialog4
ConvGroup1
Travel Database
Hotel
Dialog5
ConvGroup2
ConvGroup2
Airline
Dialog6
Only Instance 1 can dequeue the messages
Travel Program (Instance 3)
ConvGroup2
Car
Dialog7
End-of-Q
16
Related messages via dialogs

• Adding multiple instances to drain the queue
  faster is now safe.
• Related messages are not dequeued by different
  instances

Billing Program (Instance 1)
Billing Queue
Line 1
Dialog1
ConvGroup1
ConvGroup1
Line 2
Dialog1

Billing Program (Instance 2)
Line 3
Dialog1
ConvGroup1
Billing Database
EOD
Dialog1
ConvGroup1
ConvGroup2
Line 1
Dialog2
Each transaction is isolated from POs already
locked by another tran
Billing Program (Instance 3)
ConvGroup2
Line 2
Dialog2
End-of-Q
17
Deployment Issues with existing messaging products

• Plumbing issues
• Weak delivery guarantees
• Most dont 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 doesnt 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
Delivery guarantees via dialogs (ie. Dialogs
arent just about programming model)
Msg Broker 2 (Database)
Msg Broker 1 (Database)
Dialog
Customer Service

Msg Broker 3

Msg Broker 4

• 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/Symmetr
  ic key hybrid).

19
Performance Gains subtitled per Helland (faster
than greased )

• 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 gt 50MB / second
• 100Mb networks lt 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

SQL Server
Other message store
LOB Data
Queue
Inefficienttransaction commit
SQL Server
LOB Data
Queue
Very efficienttransaction commit
20
Two ways to scale using Service Broker

• Scale single-broker service
• Multiple worker-bee exe
• Consume work off a single brokers 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.

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
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
  Services 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 dont 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.