1 / 32

SQL Server Service Broker

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

charla
Download Presentation

SQL Server Service Broker

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server Service Broker Andrew Novick Service BrokerAndrew Novick

  2. 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

  3. 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

  4. Imagine What if you wanted to build a robust transport for an e-mail system? What would you need? Service BrokerAndrew Novick

  5. 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

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

  7. 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

  8. Service Broker is off by default it must be enabled. Alter database <db> SET enable_broker Service BrokerAndrew Novick

  9. Service Broker is Built-in Service BrokerAndrew Novick

  10. Built-in Uses • Data Mirroring – In SP1 Now Available! • SQL Notifications - CacheSync • Event Notifications Service BrokerAndrew Novick

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

  12. CacheSync • Dynamic notification of data changes. • Relies on the mechanism of Indexed Views Service BrokerAndrew Novick

  13. 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

  14. Key supporting technologies • Encryption/Certificates • XML Service BrokerAndrew Novick

  15. Service Broker Objects • Messages • Contracts • Queues • Conversations • Conversation Groups • Services • Routes Service BrokerAndrew Novick

  16. 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

  17. CONTRACT • Specifies which MESSAGE TYPE(S) can be sent in a Conversation (Dialog) • 2 parties • INITIATOR • TARGET Service BrokerAndrew Novick

  18. QUEUE • Holds messages • Implemented as a hidden table Service BrokerAndrew Novick

  19. 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

  20. DIALOG • The exchange of messages between services • Identified by a conversation handle(GUID) Service BrokerAndrew Novick

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

  22. Services • Direct messages to a destination • The code that sends doesn’t know the destination of a service Service BrokerAndrew Novick

  23. 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

  24. 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

  25. 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

  26. Master Keys • Databases require master keys for encrypted communication between instances. Service BrokerAndrew Novick

  27. 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

  28. Gotcha’s • Security • Security • Security Service BrokerAndrew Novick

  29. 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

  30. 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

  31. 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

  32. Thanks for Coming SQL Server 2005 Service Broker Andrew Novick www.NovickSoftware.Com anovick@novicksoftware.com 978-440-8126 Service BrokerAndrew Novick

More Related