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