Transcript Developing Message-Based Asynchronous Applications with

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 – [email protected]
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, WellFormed, 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: TSQL Programming Ben-Gan, Roger
Wolter
http://www.sqlservicebroker.com
Questions?

Feel free to email me with questions
after the presentation
[email protected]
Thank you for your participation!!