Transcript Table-Valued Functions and Cross Apply

SQL Server
Service Broker
The South East Michigan SQL Server Users Group
http://semssug.sqlpass.org
Tom Groszko
[email protected]
• http://www.sqlpass.org/24hours/Fall2010/
• September 15-16, 2010
`
• http://www.sqlpass.org/summit/na2010/
• November 8-11, 2010
• Seattle, Washington
Purpose and Objectives
Provide an introduction to Service Broker
What Service Broker can do
Define Service Broker Terms
Sample code
What Service Broker Can Do
• Native support for messaging and
queuing applications
– Conversations
– Message ordering and coordination
• Build distributed applications
– Workload balancing
– N-tier applications
• Transactional asynchronous
processing
What Service Broker Can Do
• Take advantage of Service Oriented
Architecture (SOA)
• Workflow (WF)
• BizTalk
• Microsoft Message Queuing (MSMQ)
Service Broker Terms – Message Type
• A MESSAGE TYPE defines the name
of a message and the validation that
Service Broker performs on this
message. Both sides of a
conversation must define the same
message types.
– Validation
• NONE
Whatever you want in the
message body (stored as varbinary(max))
• EMPTY
The message body must be null.
• WELL_FORMED_XML The body must
contain well formed xml.
Service Broker Terms - Initiator
• An Initiator is the service that starts
a conversation. (BEGIN DIALOG)
• A service can be both an Initiator
and a Target but on different
conversations.
Service Broker Terms - Target
• A Target is a service that accepts a
conversation that was started by
another service.
• A service can be both an Initiator
and a Target but on different
conversations.
Service Broker Terms - Contract
• A CONTRACT defines the message
types used in a broker conversation
and which party in the conversation
can send them.
Service Broker Terms - Queue
• A QUEUE is where Service Broker
stores messages.
• A QUEUE is a ‘hidden table’ and is a
schema-owned object.
• Like any other table it can be placed
on a file group.
• A QUEUE has a status that is
– ON
messages can be added and
removed
– OFF messages cannot be added or
removed.
Service Broker Terms - Queue
• A QUEUE has an Activation Status
that may identify the stored
procedure to process messages in
the queue and how many instances
of the procedure may run
concurrently.
• When a QUEUE is unavailable
messages are held in a transmission
queue for the database,
sys.transmission_queue.
Service Broker Terms - Queue
• POISON_MESSAGE_HANDLING
– ON
The default. The QUEUE will be
disabled, STATUS = OFF, when there
are five consecutive transaction
rollbacks.
– OFF The QUEUE will not be disabled
by service broker because of
transaction rollbacks.
Service Broker Terms - Queue
• A QUEUE can be queried with
SELECT.
– To prevent locking the queue
SELECT * FROM schema.queue
WITH(NOLOCK);
• It cannot be modified with INSERT,
UPDATE or DELETE statements.
Service Broker Terms - Service
• A SERVICE is a name for a specific
task or set of tasks and is used to
route messages to the correct
QUEUE and enforce CONTRACT
constraints.
• A SERVICE identifies a QUEUE and a
list of CONTRACTs that the service
uses.
Service Broker Terms - Route
• A ROUTE identifies which network
address a Service Broker service is
located on.
• Whenever you want your messages
to leave the database they originate
in, you need to provide a ROUTE.
• ROUTE is not covered in this
presentation.
Environment
• Automatically installed beginning with SQL2005
• Service Broker External Activator is available
with SQL Server 2008 Feature Pack
– Lets you move logic to receive and process messages from
the server engine to an executable.
http://www.microsoft.com/downloads/details.aspx?FamilyId=228DE03F-3B5A-428A-923F58A033D316E1&displaylang=en
• The model database has Broker Enabled = false
– CREATE DATABASE creates a database with Broker
Enabled = true despite the model setting.
Message Performance
•
The techniques you use for tuning other applications apply to
tuning Service Broker applications
Source Pro SQL Server 2008 Service Broker by Klause Aschenbrenner APress
Demo
•
SQL Server event notifications
–
–
Sample code to process SQL Server event notifications
The code will provide a history record for all DDL changes made on an SQL
Server. (Quite a bit less than source code management)
Demonstrate how to RECEIVE messages
•
•
•
Sending and Receiving messages
–
•
•
Set processing
Row by agonizing row
A explanation of the basic code to SEND and RECEIVE a message.
An order application that sends a message to three different
services which process them asynchronously and send back a
reply.
The Service Broker Application Template.
Additional Information
•
•
•
•
•
•
•
•
•
•
Pro SQL Server 2008 Service Broker
Klaus Aschenbrenner
http://www.apress.com/book/view/1590599993
A brief guide to SQL Server Service Broker
http://blogs.msdn.com/b/kangmo/archive/2008/12/17/a-brief-guide-on-sql-server-servicebroker.aspx
SQL Server: Service Broker Team Blog
http://blogs.msdn.com/b/sql_service_broker/rss.aspx
A simple secure dialog with transport certificates
http://blogs.msdn.com/b/sql_service_broker/archive/2008/06/10/a-simple-secure-dialog-withcertificates.aspx
Announcing Service Broker External Activator
http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/36a58004-dbef-46ad85a2-93563f3f8f0e
Sample activated application
http://blogs.msdn.com/b/sql_service_broker/archive/2010/03/10/sample-activated-application.aspx
Get Started With Using External Activator
http://blogs.msdn.com/b/sql_service_broker/archive/2009/05/18/get-started-with-using-externalactivator.aspx
Service Broker: Performance and Scalability Techniques
http://technet.microsoft.com/en-us/library/dd576261(SQL.100).aspx
Service Broker Tutorials
http://msdn.microsoft.com/en-us/library/bb839489.aspx
Building a Distributed Service Broker Application
http://www.sqlservercentral.com/articles/Service+Broker/2797/
Additional Information
•
•
•
•
•
•
•
The Service Broker Alphabet Part 1
http://www.sqlservercentral.com/articles/Service+Broker/2807/
The Service Broker Alphabet Part 2
http://www.sqlservercentral.com/articles/Service+Broker/2808/
Async Lifestyle: Manage Your Tasks With Service Broker
http://technet.microsoft.com/en-us/magazine/2005.05.servicebroker.aspx
SQL Server Service Broker - An Introduction
http://www.databasejournal.com/features/mssql/article.php/3880191/SQL-Server-Service-Broker--An-Introduction.htm
Writing an SSBS App When the Initiator and Target are on the Same Database
http://www.databasejournal.com/features/mssql/article.php/3896201/Writing-an-SSBS-App-Whenthe-Initiator-and-Target-are-on-the-Same-Database.htm
Service Broker Troubleshooting
http://www.mssqltips.com/tip.asp?tip=1197
Service Broker Wait Types
http://blogs.msdn.com/b/sql_service_broker/archive/2008/12/01/service-broker-wait-types.aspx
Questions