Developing Asynchronous Database Applications with SQL Server 2005

							Developing Asynchronous
Database Applications with SQL
Server 2005 Service Broker



                 Noah Subrin
                 Technical Lead
                 SRA International
    Biography

   Technical Lead – SRA International
   Speaker at several Southeast developer
    community events.
   Custom Development – web services,
    SOA, training materials.
   Technical Reviewer – Apress
   Email – support@federalsystems.net
   Blog – http://blogs.federalsystems.net
                 Agenda

   Why Service Broker?
   What Service Broker Is
   What Service Broker Is Not
   Service Broker Architecture
   Code Demos
   Activation
   Security Considerations
   Service Broker Usage Scenarios
   Technology Comparisons
        Why Service Broker?

   How do we build a loosely coupled
    asynchronous application that scales?
   How do we build a message-based
    infrastructure?
   How do we ensure that the sender and
    the receiver of a message maintain
    state?
   How can we ensure reliable, ordered
    messaging?
         What Service Broker Is

   A platform for building asynchronous, scaleable
    database applications.
   Three features that distinguish SSB are reliable,
    ordered, and asynchronous delivery of
    messaging.
   Basic unit of communication is the conversation.
   Supports queuing out of the box which includes
    plumbing features like locking, ordering, and
    multithreading.
   We can use SSB with a single instance or scale
    out across multiple instances.
What Service Broker Is Not

   Not a general purpose messaging
    system – it only works for SQL Server.
   Supports only transactional messaging
    – if transactional messaging is not
    required this is unnecessary overhead.
   Not just a messaging system, we can
    use the queuing features on a single
    SQL Server instance or multiple
    instances.
    Service Architecture
    (Design Time Components)
   Services
   Contracts
   Queues
   Message Types
                    Services

   A Service Broker service is a logical name for
    a specific business task or set of related tasks
    – it is not the name of an executable.
   A service lives (is scoped) inside a database.
   A service is a named endpoint that may
    initiate or receive messages.
   Service Broker uses the name of the service
    to deliver messages to the correct queue
    within a database, to route messages, to
    enforce the contract for a conversation, and
    to determine the remote security for a new
    conversation.
                 Queues
   Queues are internal (hidden) tables that
    store messages.
   They are exposed as views of the
    internal tables.
   Each service is associated with one
    queue where messages are stored until
    they are processed.
   Can peek messages or dequeue them.
                 Contracts
   A contract contains a name, a list of
    what message types can be sent on the
    contract, and which endpoints can send
    the approved messages.
   Initiator is the endpoint that starts the
    conversation, opposite endpoint is
    Target.
   Contract is specified in BEGIN DIALOG.
   Default contract sender is Any.
             Message Types
   A message type object defines a name for
    a message type and defines the type of
    data that the message contains.
   Each message type specifies the
    validation that SQL Server performs for
    messages of that type.
   Validation options: None, Empty, Well-
    Formed, Valid with Schema Collection.
   Catalog view sys.services_message_types.
     Conversation Architecture
     (Run Time Structure)
   Messages
   Dialog Conversations
   Conversation Groups
                 Messages
   Messages are information passed in a
    conversation.
   Messages consist of a message header and a
    message body.
   Message header contains info about the
    message, such as its source, destination,
    sequence number, and security credentials.
   SSB uses conversation identity and sequence
    number to enforce message ordering.
   Message body is used by the app to transport
    data and can contain any data that can be
    converted to varbinary(max) datatype.
        Dialog Conversations
              (Dialogs)
   A reliable, persistent, bidirectional,
    stream of messages between two
    services.
   Exactly-once-in-order message delivery.
   The lifetime of a dialog lasts from the
    time that the local SQL Server instance
    creates the dialog until an application
    either explicitly ends the dialog (END
    DIALOG) or receives an error message
    associated with the dialog.
             Conversations

   May be long lived (years) or short lived
    (seconds).
   Identified by a GUID.
   Initiator is the endpoint that begins the
    dialog, target is the receiver.
   Each conversation is uniquely identified
    by a conversation_handle.
        Types of Conversations

   Currently only dialog is supported –
    conversations and dialogs are
    synonymous.
   A dialog is a conversation between
    exactly two endpoints.
   Monolog conversations were cut early in
    SQL Server 2005 but may be included in
    a future release.
   Monolog is a single endpoint sending to
    a group of receiving endpoints.
          Conversation Group
          (Advanced Topic)
   A conversation group identifies a group of
    related conversations.
   Each conversation belongs to a conversation
    group.
   Every conversation group is associated with a
    specific service, and all conversations in the
    group are conversations to or from that
    service.
   SQL Server uses conversation groups to
    provide exactly-once-in-order (EOIO) access
    to messages that are related to a specific
    business task
Service Broker Data Flow
Example Data Flow
             Code Demos

   How To Tasks
   Simple messaging within one instance
   System Catalog Views
                  Activation

   Activation uses Service Broker to start
    an application when there is work for
    the program to do.
   An application uses activation if traffic to
    the service varies unpredictably or if the
    service must dynamically scale to match
    the traffic the service receives.
   External and internal activation.
   Not all SSB applications use activation.
          Internal Activation

   Allows a stored procedure to be started
    on a separate task when a message is
    placed in a queue.
   Configured as a property of the queue
    via the ‘Create Queue’ t-sql command
    with optional parms:
    procedure_name, max_queue_readers,
    user to execute as (principal)
          External Activation

   External activation works with programs
    that run independently of SQL Server.
   For external activation, Service Broker
    produces a SQL Server event indicating
    that the external program should start
    another queue reader.
              Code Demos

   Internal Activation of a stored
    procedure with sp_send_dbmail
      Networking and Remote
      Security Architecture
   Remote Service Bindings
   Routes
   Service Broker Endpoints
      Remote Service Bindings

   A remote service binding establishes a
    relationship between a local database
    user, the certificate for the user, and
    the name of a remote service.
   A remote service binding specifies the
    identity to be used to authenticate to
    the remote service.
   CREATE REMOTE SERVICE BINDING
    (TSQL)
                  Routes
   Service Broker uses routes to determine
    where to deliver messages.
   A route specifies the location of a
    remote service.
   By default, each database contains a
    route that specifies that messages for
    any service which does not have an
    explicit route are delivered within the
    SQL Server instance.
   Create Route TSQL command
     Service Broker Endpoints

   A Service Broker endpoint configures
    SQL Server to send and receive Service
    Broker messages over the network.
   Endpoints are specified using service
    names.
   Listens on a specific port.
   Options for transport security and
    message forwarding.
       Security Considerations

   Not all SSB apps require all security
    features for example apps running on a
    single instance .
   By default, transport connections are
    authenticated, all messages are
    encrypted and signed.
   Security across multiple instances may
    also vary, i.e. secure LAN vs. insecure
    WAN.
              Certificates

   SSB remote security is dependent on
    certificates.
   Certificates are containers for public
    key/private key pairs.
   Certificates establish the credentials of
    a remote database, and then map
    operations from the remote database to
    a local user.
           Transport Security

   Secures the TCP/IP connection between
    two SSB’s connected on the same
    network.
   Transport security allows database
    administrators to restrict network
    connections to a database and can
    encrypt messages on the network.
   Transport security applies to the network
    connection between two instances.
    Transport Security Continued

   Transport security controls which instances
    can communicate and provides encryption
    between two instances.
   Transport security applies to the instance as a
    whole. Transport security does not secure the
    contents of individual messages, nor does
    transport security control access to individual
    services within an instance.
                Dialog Security
   Secures each individual dialog between two
    dialog endpoints, regardless of how many
    networks the messages traverse as the travel
    between the conversation endpoints.

   Dialog security establishes authenticated and
    encrypted communication between two services.

   Dialog security provides encryption, remote
    authentication, and remote authorization for a
    specific conversation.
        Service Broker Usage
              Scenarios
   Asynchronous triggers
   Reliable Data Processing
   Distributed Order Processing
   Data Consolidation for Client Apps
   Message Forwarding
         Asynchronous Triggers
   A trigger queues a message that requests work
    from a Service Broker service.
   Trigger creates a message that contains
    information about the work to be done and
    sends this message to a service that performs
    the work.
   When the original transaction commits, Service
    Broker delivers the message to the destination
    service.
   The program that implements the service
    performs the work in a separate transaction.
      Reliable Data Processing

   Some applications must reliably process
    queries, without regard to computer failures,
    power outages, or similar problems.
   An application that needs reliable query
    processing can submit queries by sending
    messages to a Service Broker service.
   The application that implements the service
    reads the message, runs the query, and
    returns the results.
   All three of these operations take place in the
    same transaction.
    Distributed Order Processing

   Scenario where an order does not need
    to be fulfilled immediately but does
    need to be delivered.
   Example local order system receives
    order and routes to remote warehouse
    for fulfillment.
   Fulfillment systems do not have to fill
    order immediately.
    Data Consolidation for Client
               Apps
   Example, customer service application
    that consolidates data from multiple
    locations.
   Requests are executed in parallel using
    several services.
   Customer service app collects the
    responses and displays the results.
     Technology Comparisons

   MSMQ
   BizTalk Server
   WCF
   SQL Service Broker
               SSB vs. MSMQ
   SSB can commit updates to queues in a simple
    transaction; MSMQ uses two phase commit.
   SSB can handle much larger messages.
   SSB messages can be processed by any app
    that can connect to the database. MSMQ must
    run on the same physical machine.
   MSMQ provides for TCP/IP and SOAP
    messages. At this time SSB is TCP/IP only.
       SSB vs. Biztalk Server
   Very little in common except for reliable
    messaging and use of database queues.
   BizTalk can also manipulate the
    contents of messages over multiple
    transports, map message formats,
    manage workflows etc.
   If your app just requires reliable
    messaging from one SQL Server
    instance to another then SSB is
    probably a better choice.
               SSB vs. WCF
   SSB supports reliable messaging by using a
    proprietary format, WCF is standards based.
   WCF supports multiple transports.
   SSB is more suitable for SQL Server apps.
   WCF offers a richer feature set and
    connectivity options beyond what SSB has.
   We can combine SSB’s asynchronous reliable
    database platform capabilities with WCF’s
    interoperability to build heterogeneous,
    reliable apps.
     Service Broker Resources

   SQL Server BOL.
   SQL Server 2005 Service Broker by
    Roger Wolter
   Inside Microsoft SQL Server 2005: T-
    SQL Programming Ben-Gan, Roger
    Wolter
   http://www.sqlservicebroker.com
Questions?
   Feel free to email me with questions
    after the presentation
    support@federalsystems.net

Thank you for your participation!!