sql service broker l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL Service Broker PowerPoint Presentation
Download Presentation
SQL Service Broker

Loading in 2 Seconds...

play fullscreen
1 / 23

SQL Service Broker - PowerPoint PPT Presentation


  • 307 Views
  • Uploaded on

SQL Service Broker. Gerald Hinson Development Lead – SSB team HPTS 2005. THESIS: Queues are Databases [1] Jim Gray - December 21, 1995 Abstract :

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL Service Broker' - mili


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql service broker

SQL Service Broker

Gerald Hinson

Development Lead – SSB team

HPTS 2005

slide2
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).

slide3
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.”

sql server 2005 mild mannered database becomes app server
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)
1 of everything vs 2 of everything
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.)
rationale for a new communication primitive
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

rationale for a new communication primitive cont
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

rationale for a new communication primitive cont8
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

rationale for a new communication primitive cont9
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)

rationale for new communication primitive cont
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.
dialogs a new communication primitive
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
dialogs a new communication primitive cont

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

conversation groups
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”.

conversation groups14
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…).
composed services via dialogs
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

related messages via dialogs
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

deployment issues with existing messaging products
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

delivery guarantees via dialogs ie dialogs aren t just about programming model

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

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 > 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

two ways to scale using service broker
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
sql express is free
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!
internal activation of services

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
external activation of services
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.