PDC05 - Day 1 SQL Service Broker Musings

  • Article

Attending the Building Distributed Asynchronous Applications with SQL Service Broker session at PDC05 and thought I'd share these notes ....

  • Service Broker is for SQL Server applications
  • The queues are actually database objects
  • You can write these applications as stored procedures with T-SQL (or .NET). New T-SQL keywords include:
    • transaction
    • receive
    • conversation
    • waitfor(?)
    • queue
    • xml (as part of SQL Server 2005)
  • Reliable messaging included for queuing across databases
  • Dialogs are for two-way messaging between services
    • Guaranteed delivery, in-order delivery, secure communications
    • Dialogs can be over a long period of time, or short
  • A service is a named endpoint that a message can travel to
  • Some basic code might start like:
    create queue [Inventory Queue];
    create service [Inventory]
    on queue [Inventory Queue] ([DEFAULT)];
  • Begin a dialog transaction "from" a service, "to" a service and pass on a defined transaction
  • On target server, you select from the destination queue
  • Can debug the T-SQL service from within Visual Studio.NET
  • What if you don't want all the logic within stored procs? Answer: External Activation
    • Messages placed in an Event Queue where an external activitor handles the logic
  • Visit www.sqlservicebroker.com
  • How do you keep this working without deadlocking? Answer: Conversation Group Locking
    • Dialogs are single-threaded
    • Locking of conversations to prevent collisions [note: look at further]
  • Announcing ... Indigo (WCF) channel for the Service Broker!
  • Using (datbase stored) certificates and public/private keys for Dialog Security between service endpoints
  • Summary: Asynchronous DB "Stuff", Reliable Messaging, Reliable SOA