slide1
Download
Skip this Video
Download Presentation
SQL Server Service Broker

Loading in 2 Seconds...

play fullscreen
1 / 32

SQL Server Service Broker - PowerPoint PPT Presentation


  • 139 Views
  • Uploaded on

SQL Server Service Broker. Andrew Novick. Agenda. What is Service Broker Built-in Uses DataMirroring CacheSync (Query Notification) Event Notifications How does it work Communication Infrastructure Security Scenarios for Use. Introduction – Andrew Novick. Novick Software

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL Server Service Broker' - charla


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1
SQL Server Service Broker

Andrew Novick

Service BrokerAndrew Novick

agenda
Agenda
  • What is Service Broker
  • Built-in Uses
    • DataMirroring
    • CacheSync (Query Notification)
    • Event Notifications
  • How does it work
    • Communication Infrastructure
    • Security
  • Scenarios for Use

Service BrokerAndrew Novick

slide3
Introduction – Andrew Novick
  • Novick Software
    • Business Application Development
    • SQL Server and .Net specialization
  • www.NovickSoftware.com
  • Books:
    • Transact-SQL UDFs
    • SQL 2000 XML Distilled

Service BrokerAndrew Novick

imagine
Imagine

What if you wanted to build a robust transport for an e-mail system?

What would you need?

Service BrokerAndrew Novick

robust transport for e mail needs
Robust transport for e-mail needs:
  • Asynchronous Messaging Infrastructure
  • Easy to implement two way protocols
  • Messages transacted with other DB objects
  • Transport independence
  • Real-time transport re-routing

Service BrokerAndrew Novick

service broker is
Service Broker is:
  • Asynchronous messaging infrastructure build into T-SQL
  • Messages and actions on messages are transacted with other database objects.

Service BrokerAndrew Novick

why another messaging facility
Why another messaging facility?
  • Asynchronous messaging is difficult
  • Database integration:
    • Improves performance
    • Simplifies Administration
    • Transaction support and recovery
  • Message ordering can be guaranteed
  • Workloads can be balanced.

Service BrokerAndrew Novick

service broker is off by default it must be enabled

Service Broker is off by default it must be enabled.

Alter database SET enable_broker

Service BrokerAndrew Novick

service broker is built in
Service Broker is Built-in

Service BrokerAndrew Novick

built in uses
Built-in Uses
  • Data Mirroring – In SP1 Now Available!
  • SQL Notifications - CacheSync
  • Event Notifications

Service BrokerAndrew Novick

data mirroring
Data Mirroring
  • Real-time copying of changed pages to another database.
  • Alternative to:
    • Replication
    • Log Shipping
  • Can be asynchronous or synchronous

Service BrokerAndrew Novick

cachesync
CacheSync
  • Dynamic notification of data changes.
  • Relies on the mechanism of Indexed Views

Service BrokerAndrew Novick

event notifications
Event Notifications
  • T-SQL Object
  • Execute in response to:
    • DDL statements
    • Some SQL Trace Events
  • NOT the same as DDL triggers
  • Are outside the context of the transaction.

Service BrokerAndrew Novick

key supporting technologies
Key supporting technologies
  • Encryption/Certificates
  • XML

Service BrokerAndrew Novick

service broker objects
Service Broker Objects
  • Messages
  • Contracts
  • Queues
  • Conversations
  • Conversation Groups
  • Services
  • Routes

Service BrokerAndrew Novick

message
Message
  • Up to 2 Gb of Binary information
  • Contents Usually XML
        • COULD BE BINARY
  • Given a URL as a name
  • Used with statements
    • SEND
    • RECEIVE

Service BrokerAndrew Novick

contract
CONTRACT
  • Specifies which MESSAGE TYPE(S) can be sent in a Conversation (Dialog)
  • 2 parties
    • INITIATOR
    • TARGET

Service BrokerAndrew Novick

queue
QUEUE
  • Holds messages
  • Implemented as a hidden table

Service BrokerAndrew Novick

conversations
Conversations
  • The exchange of messages on a contract
  • Two types:
    • Dialog
    • Monolog – Not included in SQL Server 2005
  • Identified by a Conversation Handle (GUID)

Service BrokerAndrew Novick

dialog
DIALOG
  • The exchange of messages between services
  • Identified by a conversation handle(GUID)

Service BrokerAndrew Novick

conversation groups
Conversation Groups
  • Related conversations
  • The unit of locking
  • So only one conversation in a group is processed at a time.

Service BrokerAndrew Novick

services
Services
  • Direct messages to a destination
  • The code that sends doesn’t know the destination of a service

Service BrokerAndrew Novick

activation
Activation
  • Services can activate programs
    • Stored procedures
    • External programs
  • Arrival on a Queue triggers activation
  • Multiple programs can run simultaneously
  • Conversation or Conversation Group locking guarantees correct in-order processing.

Service BrokerAndrew Novick

communications
Communications
  • Routing of messages between Instances
  • Routing is to a Service
    • Bindings can be changed dynamically
  • TCP/IP transport
  • Other transports in the future
    • Indigo

Service BrokerAndrew Novick

security
Security
  • Dialog Security
    • Authentication
    • Authorization
    • Encryption (between Instances)
  • Transport Security
    • Between Instances
    • Restricts the instances that can connect
    • Supports Encryption on messages between instances

Service BrokerAndrew Novick

master keys
Master Keys
  • Databases require master keys for encrypted communication between instances.

Service BrokerAndrew Novick

scenarios
Scenarios
  • Implementing within database queuing
  • Branch/Machine database feeding a central database:
    • ATM Network
    • Store based Time and Attendence(SQL Server Express includes Service Broker)
  • Scalable SOA
  • Scalable Web Sites

Service BrokerAndrew Novick

gotcha s
Gotcha’s
  • Security
  • Security
  • Security

Service BrokerAndrew Novick

summary
Summary
  • Secure, reliable, asynchronous messaging
  • Built into the database infrastructure
  • Administered as part of the database.
  • Allows the construction of scalable, distributed applications.

Service BrokerAndrew Novick

resources for service broker
Resources for Service Broker
  • SSBAdmin – Niels B.http://www.sqljunkies.com/WebLog/nielsb/archive/2005/12/27/17701.aspx
  • GotDotNet Code Galleryhttp://www.gotdotnet.com/codegallery/codegallery.aspx?id=9f7ae2af-31aa-44dd-9ee8-6b6b6d3d6319

Service BrokerAndrew Novick

new england visual basic pro
New England Visual Basic Pro
  • Visual Basic Focus
  • .Net and SQL Server are frequent topics.
  • First Thursday of every month:
  • Schedule
    • June – Multiple Users Presentations
    • July – Tony Mann – SQL Server Reporting
    • August 2nd – Julie Lerman – Wednesday Mtg.

Service BrokerAndrew Novick

thanks for coming
Thanks for Coming

SQL Server 2005 Service Broker

Andrew Novick

www.NovickSoftware.Com

[email protected]

978-440-8126

Service BrokerAndrew Novick

ad