Transcript Intro to SSB SQL Server 2005 Service Broker

Intro to SSB
SQL Server 2005 Service Broker
Brian Jackson
Microsoft Consulting Services
Agenda






What is SSB?
Why Add Messaging to the Database?
Key Concepts
Usage Scenarios
Demo
Architectural Positioning




SSB and MSMQ
SSB and BizTalk
SSB and Indigo
Questions
What Is SSB?



SQL Server 2005 Service Broker
Allows internal or external processes to
send and receive guaranteed,
asynchronous messages using
extensions to Transact-SQL Data
Manipulation Language (DML)
In short: Asynchronous messaging
technology built directly into SQL
Server 2005
Why Add Messaging to the
Database?

To address reality:




Lots of customers use the database as a message
queue already
The hard problems in messaging are difficult to
implement correctly
SSB eliminates the need for customers to create
custom solutions for SQL Server based message
queuing
The queue handling code built into the database
kernel handles the locking, ordering, and
multithreading issues associated with most homegrown database queues
Why Add Messaging to the
Database?

To take advantage of transactional
support in the DBMS




Service Broker also supports only
transactional messaging
Transactional queuing technologies
outside the DBMS (e.g., MSMQ) require 2
phase commit transactions via the DTC
To integrate backup, recovery and
administration with SQL server
To provide near real time failover via
database mirroring
Key Concepts
Message
Type
Message
Type
Contract
Message
Type
Message
Type
Contract
Conversation
Service
Service
Queue
Key Concepts

Service



A name for a specific task or set of tasks
Messages are sent to services and stored
in the queue associated with the service
Service name used to route messages,
deliver messages to the correct queue
within a database, and enforce the contract
for a conversation
Key Concepts

Service

Creation syntax:
CREATE SERVICE [TestService]
AUTHORIZATION [dbo]
ON QUEUE [TestQueue]
([//Contract1],[Contract2])


Specifies name, owner, associated queue
name, and optionally the contracts on
which the service can receive messages
If no contracts are specified, the service
can only initiate messages
Key Concepts

Queue



A named container for holding messages
while they await processing
Provides loose coupling between sender
and receiver
May or may not have a service program
associated with it
Key Concepts

Queue

Creation syntax
CREATE QUEUE ExpenseQueue
WITH STATUS=ON,
ACTIVATION (
PROCEDURE_NAME = expense_procedure,
MAX_QUEUE_READERS = 5,
EXECUTE AS 'ExpenseUser' ) ;
Key Concepts

Service Program




Any program that sends or receives
messages via SSB
Can be a T-SQL stored procedure, CLR
stored procedure, or external program that
is activated when the first message arrives
in the queue
As number of messages grows, additional
instances of activated service programs
may be created, up to the number specified
in MAX_QUEUE_READERS
Note: the external activator is not available
in the current public beta (Yukon Beta 2)
Key Concepts

Contract




Defines the message types used in a
conversation
Determines which side of the conversation
can send messages of that type
Each conversation follows a contract that
the initiating service specifies when the
conversation begins
Both sides of a conversation must define
the same contract
Key Concepts

Contract

Creation syntax
CREATE CONTRACT [//ContractName] (
[//MessageTypeOne] SENT BY INITIATOR,
[// MessageTypeTwo] SENT BY TARGET,
[// MessageTypeThree] SENT BY ANY ) ;

Sent by



Initiator: the endpoint that starts a conversation
with BEGIN DIALOG CONVERSATION
Target: the dialog endpoint that accepts a
conversation that was started by another
service
Any: messages of this type can be sent by both
the initiator and the target
Key Concepts

Message Type




A named definition of a format for
messages exchanged between services
Persisted in the database where the
message type is created.
Identical message type created in each
database that participates in a
conversation
4 validation options for message type
instances:




NONE
EMPTY
WELL_FORMED_XML
VALID_XML WITH SCHEMA COLLECTION
Key Concepts

Message types

Creation syntax for message type with
schema validation:
CREATE XML SCHEMA COLLECTION SampleSchemaCollection AS
N'<?xml version="1.0" encoding="UTF-16" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
…schema definition omitted …’
CREATE MESSAGE TYPE
[//SampleSchema]
VALIDATION = VALID_XML WITH SCHEMA COLLECTION
SampleSchemaCollection
Key Concepts

Dialog / Conversation

Synonymous





At one point, SSB included a “Monolog”
abstraction, but not currently
Conversations—not messages—are the
messaging primitive in SSB
Any program (including a service program)
that has access to SQL Server can create a
conversation
An initiating service must begin a
conversation with the target service before
sending a message to the target service
Conversations are the unit of message
correlation and ordering
Key Concepts

Dialog / Conversation

Initiating a dialog
DECLARE @dialog_handle UNIQUEIDENTIFIER ;
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [//InitiatorService]
TO SERVICE '//TargetService'
ON CONTRACT [//ContractName]
WITH LIFETIME = 60 ;


Reliable, in-order, once-only delivery
Conversations can be long-running
Key Concepts

Conversation Group [previously called “Service Instance”]


A user-defined grouping of conversations
For example: All the conversations required
to process a single order






Order conversation (Order Header, Order Line)
Inventory service conversation (Inventory Check, Inventory Response)
Shipping service conversation (Shipping Request, Shipping Response)
Purchasing service conversation (Purchasing Request, Purchasing
Response)
Defines state scope and locking scope
Conversation group lock




Defines the locking scope for all the conversations involved in
processing single application unit.
Different parts of the application logic may be executing on different
threads simultaneously. This is one of the things that makes writing
loosely-coupled asynchronous applications
A conversation group lock is required for any conversation receives or
sends.
In an order-entry application with hundreds of active threads, a single
order is only processed on one thread at a time which greatly simplifies
asynchronous programming.
Key Concepts

Routes





Used for conversations between different
instances of SQL Server
A route maps a service name to a physical
location
Provides location transparency
Supports load balancing
Allows delivery to a specific instance of a
service
Key Concepts
Message
Type
Message
Type
Contract
Message
Type
Message
Type
Contract
Conversation
Service
Service
Queue
Usage Scenarios

Asynchronous distributed applications

Travel Agency Sample



Booking a trip requires potentially long running
conversations with hotel, air, and car rental
systems.
Send messages via SSB, correlate the results,
commit the transaction
Email the confirmation to the customer
Usage Scenarios

Scale out batch processing

Loan Processing Sample




Starting from an input file of loans, several
correlated steps must occur in sequence
Lifecycle stages: import, normalize, validate,
price, fund
A loosely coupled, message based architecture
allows processing to be distributed over one or
more hardware assets
Additional workload can be accommodated by
adding hardware
Demo
Architectural Positioning

SSB & MSMQ

SSB:




Service Broker can commit updates to the message queue,
database data, and application state in a simple database
transaction. MSMQ requires a two-phase commit to do the
same thing.
MSMQ message ordering is assured within a single transaction.
Service Broker message ordering in a dialog is assured across
transactions, sending applications and receiving applications.
The maximum MSMQ message size is 4MB. The maximum
Service Broker message size is 2GB.
MSMQ:




MSMQ offers express, reliable, and transactional message
styles while Service Broker is transactional only.
MSMQ can communicate between virtually any pair of Windows
applications and with the MQ-Series bridge can talk to
applications on a wide variety of hardware and software.
Service Broker can only communicate between applications
connected to SQL Server.
MSMQ offers both a TCP/IP binary protocol and an HTTP SOAP
protocol for communications. Service Broker is binary TCP/IP
only for Yukon.
The scope of a Message Queuing transaction is the local
computer, and Message Queuing does not guarantee end-toend delivery
Architectural Positioning

SSB & BizTalk

SSB:



Can reliably deliver a message to another SQL Server
instance with exactly-once in-order assurances
If that’s all you need, and you’ve got SQL Server 2005,
then SSB is a good fit
BizTalk:



Can reliably deliver a message to another SQL Server
instance with exactly-once in-order assurances
Can manipulate the contents of messages, map message
formats, manage message processing, manage
workflows, manage state, send messages over multiple
different transports
If you need these features, you need BizTalk
Architectural Positioning

SSB & Indigo

SSB



Supports reliable, transactional messaging over
TCP/IP using a proprietary protocol between
SQL Server instances
Crisp failure semantics, tight integration with
SQL Server’s transaction management
Indigo



Supports many different messaging styles over
a variety of standards-based protocols between
Windows and any OS that implements the
standard protocols that Indigo supports
Rich extensibility model based on pipelines
Less full featured than SSB for SQL-to-SQL
connections
Resources

Community Site:
http://www.sqlservicebroker.com/forums

Free book chapter:
http://download.microsoft.com/download/3/8/
1/38154d73-bc47-4e9f-a7f5ca9beb118fde/Chapter15_w.pdf
The End
Questions?