Improve Application Performance with SQL Server Service Broker

1
Improve Application Performancewith SQL Server
Service Broker

• Bob Beauchemin
• Director of Developer Resources, SQLskills
• http//www.SQLskills.com/blogs/bobb

2
About Bob Beauchemin

• Independent Consultant/Trainer/Writer/Speaker
• Director of Developer Resources, SQLskills
• Website http//www.SQLskills.com
• Blog http//www.SQLskills.com/blogs/bobb
• SQL Server MVP
• Author of books and numerous resources
• related to SQL Server and data access
• A Developer's Guide to SQL Server 2005
• A First Look at SQL Server 2005 for Developers
• Best Book SQL Server Magazines Readers Choice
  Awards (2005)
• Essential ADO.NET

3
Overview

• What is Service Broker?
• Application decoupling
• The activation program
• Internal activation
• External activation
• Writing an activation program
• Locking a conversation group
• Processing strategies
• Saving state across invocations
• Transactions - retries and rollback

4
What is Service Broker

• Key to scalability through scale out
• Asynchronous, distributed, decoupled programming
  environment
• Fully integrated into the database engine
• Programming Model - DDL and DML
• Integrated Management and Deployment
• Used for SQL Server 2005 features
• Events Notifications, Query Notifications
• Database Mail Engine
• Enables new programming scenarios
• Asynchronous stored procedures
• Distributed multi-db applications

5
Application Decoupling - Original

• Messaging provides application decoupling
• Original application
• Check for customer row, add if not exists
• Generate order id
• Add order and order line items
• Check credit
• Decrement inventory
• ...
• Multiple operations composed into a single
  transaction
• If inventory system on different instance,
  requires distributed transaction
• If inventory system instance is down, the
  ordering application is down

6
Application Decoupling

• Messaging restructures the processing
• Refactored application
• Check for customer row, add if not exists
• Generate order id
• Message to add order and order line items
• Message to check credit
• Message to decrement inventory
• ...
• Database operations and messages composed into a
  single transaction
• Can scale out systems to multiple instances
  without distributed transactions
• If inventory system is down, order entry is not

7
Service Broker Solutions

• SQL Broker can be used in custom solutions
• Generalized asynchronous semantics
• Inter and intra-instance queuing
• Distributed transactions not required
• Multi-threading in stored procedures
• Combined with .NET procedural code
• App server functionality inside database

8
Semantic features

• Service Broker solves several hard application
  problems
• Message ordering even across multiple exchanges
• Once and only once delivery
• Message correlation
• Multiple queue readers for
• Load balancing
• Failover
• Conversation management
• Ability to group conversations for serialized
  behavior

9
Broker Terms

• Broker is service-oriented messaging
• Service - an endpoint for sending and receiving
  messages
• Queue - special table where messages are stored
• Dialog - message exchanges between two services
• Initiator - service that begins the dialog
• Target - service that first message sent to

10
Service Broker Architecture
Application Layer
Target Application
Initiator Application
Message Type
Message Type
Metadata Layer
Contract
Contract
Dialog Conversation
Service
Service
Physical Layer
Queue
Queue
Activation
Transport
Transport
TCP/IP
11
Service Broker Objects

• Broker infrastructure built-in
• defines new set of SQL Server objects
• defines new Transact-SQL statements

CREATE MESSAGE TYPE CREATE CONTRACT CREATE
QUEUE CREATE SERVICE
BEGIN DIALOG CONVERSATION SEND RECEIVE WAITFOR
(expanded) GET CONVERSATION GROUP END CONVERSATION
12
Broker Programming

• Programming consists of
• Sending messages on a conversation
• T-SQL through proc or program
• Processing queues
• Target processes the initial message
• And succeeding messages
• Or messages from multiple conversations
• Initiator may process response
• Both sides process broker-generated msgs

13
Initial Message
BEGIN TRANSACTION -- purchase 1 item INSERT
INTO sales VALUES(1, 42, 3.50) -- queue
removal from inventory EXECUTE
update_inventory 1 COMMIT
CREATE PROCEDURE update_inventory(_at_quantity
INT) AS DECLARE _at_h UNIQUEIDENTIFIER BEGIN
DIALOG CONVERSATION _at_h FROM SERVICE
order_service TO SERVICE 'inventory_service'
WITH ENCRYPTION OFF -- send message to
adjust inventory SEND ON CONVERSATION _at_h
(_at_quantity) END CONVERSATION _at_h
14
Handling Messages