Developing Asynchronous Database Applications with SQL Server 2005

Developing Asynchronous Database Applications with SQL Server 2005 Service Broker Noah Subrin Technical Lead SRA International

Biography n n n Technical Lead – SRA International Speaker at several Southeast developer community events. Custom Development – web services, SOA, training materials. Technical Reviewer – Apress Email – support@federalsystems. net Blog – http: //blogs. federalsystems. net

Agenda n n n n n Why Service Broker? What Service Broker Is Not Service Broker Architecture Code Demos Activation Security Considerations Service Broker Usage Scenarios Technology Comparisons

Why Service Broker? n n How do we build a loosely coupled asynchronous application that scales? How do we build a message-based infrastructure? How do we ensure that the sender and the receiver of a message maintain state? How can we ensure reliable, ordered messaging?

What Service Broker Is n n n A platform for building asynchronous, scaleable database applications. Three features that distinguish SSB are reliable, ordered, and asynchronous delivery of messaging. Basic unit of communication is the conversation. Supports queuing out of the box which includes plumbing features like locking, ordering, and multithreading. We can use SSB with a single instance or scale out across multiple instances.

What Service Broker Is Not n n n Not a general purpose messaging system – it only works for SQL Server. Supports only transactional messaging – if transactional messaging is not required this is unnecessary overhead. Not just a messaging system, we can use the queuing features on a single SQL Server instance or multiple instances.

Service Architecture (Design Time Components) n n Services Contracts Queues Message Types

Services n n A Service Broker service is a logical name for a specific business task or set of related tasks – it is not the name of an executable. A service lives (is scoped) inside a database. A service is a named endpoint that may initiate or receive messages. Service Broker uses the name of the service to deliver messages to the correct queue within a database, to route messages, to enforce the contract for a conversation, and to determine the remote security for a new conversation.

Queues n n Queues are internal (hidden) tables that store messages. They are exposed as views of the internal tables. Each service is associated with one queue where messages are stored until they are processed. Can peek messages or dequeue them.

Contracts n n A contract contains a name, a list of what message types can be sent on the contract, and which endpoints can send the approved messages. Initiator is the endpoint that starts the conversation, opposite endpoint is Target. Contract is specified in BEGIN DIALOG. Default contract sender is Any.

Message Types n n A message type object defines a name for a message type and defines the type of data that the message contains. Each message type specifies the validation that SQL Server performs for messages of that type. Validation options: None, Empty, Well. Formed, Valid with Schema Collection. Catalog view sys. services_message_types.

Conversation Architecture (Run Time Structure) n n n Messages Dialog Conversations Conversation Groups

Messages n n n Messages are information passed in a conversation. Messages consist of a message header and a message body. Message header contains info about the message, such as its source, destination, sequence number, and security credentials. SSB uses conversation identity and sequence number to enforce message ordering. Message body is used by the app to transport data and can contain any data that can be converted to varbinary(max) datatype.

Dialog Conversations (Dialogs) n n n A reliable, persistent, bidirectional, stream of messages between two services. Exactly-once-in-order message delivery. The lifetime of a dialog lasts from the time that the local SQL Server instance creates the dialog until an application either explicitly ends the dialog (END DIALOG) or receives an error message associated with the dialog.

Conversations n n May be long lived (years) or short lived (seconds). Identified by a GUID. Initiator is the endpoint that begins the dialog, target is the receiver. Each conversation is uniquely identified by a conversation_handle.

Types of Conversations n n Currently only dialog is supported – conversations and dialogs are synonymous. A dialog is a conversation between exactly two endpoints. Monolog conversations were cut early in SQL Server 2005 but may be included in a future release. Monolog is a single endpoint sending to a group of receiving endpoints.

Conversation Group (Advanced Topic) n n A conversation group identifies a group of related conversations. Each conversation belongs to a conversation group. Every conversation group is associated with a specific service, and all conversations in the group are conversations to or from that service. SQL Server uses conversation groups to provide exactly-once-in-order (EOIO) access to messages that are related to a specific business task

Service Broker Data Flow

Example Data Flow

Code Demos n n n How To Tasks Simple messaging within one instance System Catalog Views

Activation n n Activation uses Service Broker to start an application when there is work for the program to do. An application uses activation if traffic to the service varies unpredictably or if the service must dynamically scale to match the traffic the service receives. External and internal activation. Not all SSB applications use activation.

Internal Activation n n Allows a stored procedure to be started on a separate task when a message is placed in a queue. Configured as a property of the queue via the ‘Create Queue’ t-sql command with optional parms: procedure_name, max_queue_readers, user to execute as (principal)

External Activation n n External activation works with programs that run independently of SQL Server. For external activation, Service Broker produces a SQL Server event indicating that the external program should start another queue reader.

Code Demos n Internal Activation of a stored procedure with sp_send_dbmail

Networking and Remote Security Architecture n n n Remote Service Bindings Routes Service Broker Endpoints

Remote Service Bindings n n n A remote service binding establishes a relationship between a local database user, the certificate for the user, and the name of a remote service. A remote service binding specifies the identity to be used to authenticate to the remote service. CREATE REMOTE SERVICE BINDING (TSQL)

Routes n n Service Broker uses routes to determine where to deliver messages. A route specifies the location of a remote service. By default, each database contains a route that specifies that messages for any service which does not have an explicit route are delivered within the SQL Server instance. Create Route TSQL command

Service Broker Endpoints n n A Service Broker endpoint configures SQL Server to send and receive Service Broker messages over the network. Endpoints are specified using service names. Listens on a specific port. Options for transport security and message forwarding.

Security Considerations n n n Not all SSB apps require all security features for example apps running on a single instance. By default, transport connections are authenticated, all messages are encrypted and signed. Security across multiple instances may also vary, i. e. secure LAN vs. insecure WAN.

Certificates n n n SSB remote security is dependent on certificates. Certificates are containers for public key/private key pairs. Certificates establish the credentials of a remote database, and then map operations from the remote database to a local user.

Transport Security n n n Secures the TCP/IP connection between two SSB’s connected on the same network. Transport security allows database administrators to restrict network connections to a database and can encrypt messages on the network. Transport security applies to the network connection between two instances.

Transport Security Continued n n Transport security controls which instances can communicate and provides encryption between two instances. Transport security applies to the instance as a whole. Transport security does not secure the contents of individual messages, nor does transport security control access to individual services within an instance.

Dialog Security n n n Secures each individual dialog between two dialog endpoints, regardless of how many networks the messages traverse as the travel between the conversation endpoints. Dialog security establishes authenticated and encrypted communication between two services. Dialog security provides encryption, remote authentication, and remote authorization for a specific conversation.

Service Broker Usage Scenarios n n n Asynchronous triggers Reliable Data Processing Distributed Order Processing Data Consolidation for Client Apps Message Forwarding

Asynchronous Triggers n n A trigger queues a message that requests work from a Service Broker service. Trigger creates a message that contains information about the work to be done and sends this message to a service that performs the work. When the original transaction commits, Service Broker delivers the message to the destination service. The program that implements the service performs the work in a separate transaction.

Reliable Data Processing n n Some applications must reliably process queries, without regard to computer failures, power outages, or similar problems. An application that needs reliable query processing can submit queries by sending messages to a Service Broker service. The application that implements the service reads the message, runs the query, and returns the results. All three of these operations take place in the same transaction.

Distributed Order Processing n n n Scenario where an order does not need to be fulfilled immediately but does need to be delivered. Example local order system receives order and routes to remote warehouse for fulfillment. Fulfillment systems do not have to fill order immediately.

Data Consolidation for Client Apps n n n Example, customer service application that consolidates data from multiple locations. Requests are executed in parallel using several services. Customer service app collects the responses and displays the results.

Technology Comparisons n n MSMQ Biz. Talk Server WCF SQL Service Broker

SSB vs. MSMQ n n SSB can commit updates to queues in a simple transaction; MSMQ uses two phase commit. SSB can handle much larger messages. SSB messages can be processed by any app that can connect to the database. MSMQ must run on the same physical machine. MSMQ provides for TCP/IP and SOAP messages. At this time SSB is TCP/IP only.

SSB vs. Biztalk Server n n n Very little in common except for reliable messaging and use of database queues. Biz. Talk can also manipulate the contents of messages over multiple transports, map message formats, manage workflows etc. If your app just requires reliable messaging from one SQL Server instance to another then SSB is probably a better choice.

SSB vs. WCF n n n SSB supports reliable messaging by using a proprietary format, WCF is standards based. WCF supports multiple transports. SSB is more suitable for SQL Server apps. WCF offers a richer feature set and connectivity options beyond what SSB has. We can combine SSB’s asynchronous reliable database platform capabilities with WCF’s interoperability to build heterogeneous, reliable apps.

Service Broker Resources n n SQL Server BOL. SQL Server 2005 Service Broker by Roger Wolter Inside Microsoft SQL Server 2005: TSQL Programming Ben-Gan, Roger Wolter http: //www. sqlservicebroker. com

Questions? n Feel free to email me with questions after the presentation support@federalsystems. net Thank you for your participation!!