SODA – SOA in Databases SQL Service Broker HTTP Endpoints

2. SODA – SOA in DatabasesSQL Service BrokerHTTP Endpoints Srinivas Sampath MVP SQL Server Head – Technology Labs SCT Software Solutions, India

3. Agenda • SOA Basics • HTTP Endpoints • Service Broker • Questions

4. SOA Basics • What is SOA? • The policies, practices, frameworks that enable application functionality to be provided and consumed as sets of services published at a granularity relevant to the service consumer. Services can be invoked, published and discovered, and are abstracted away from the implementation using a single, standards-based form of interface. (CBDI) • Eventually, most software capabilities will be delivered and consumed as services • Adoption is essential to deliver the business agility and IT flexibility promised by web services

5. Why SQL Server? • SOA needs • Service Identification using standards based mechanisms • Reliable messaging for synchronous or asynchronous operations • Loose coupling between operations • SQL Server now has both • Service Broker • HTTP Endpoints • SQLXML 3.0 is still supported, but HTTP Endpoints recommended

6. HTTP Endpoints

7. Why HTTP Endpoints? • Provide an open “standards” based method to access SQL Server • Ubiquitous data access • Based on standards such as HTTP, SOAP 1.1 and 1.2, XSD and WSDL 1.1 • Comply with latest web services specifications wherever possible

8. Access Mechanisms SQLXML IIS ODBC OLEDB ADO.NET TDS TCP N Pipes Client Database Server SOAP HTTP Database Server Client • Microsoft Windows 2003, Microsoft Windows XP SP2 • Kernel Mode HTTP.SYS Support • No requirement for IIS

9. Architecture ENDPOINT RPC Access Stored Procedure WSDL Request WSDL Generator Batch Access • Endpoints are the point of communication • Created using CREATE ENDPOINT • Has a name • Transport Options • Payload Options

10. Security Model • No Anonymous Access • Authentication – two levels • Transport level (HTTP) • Basic, Integrated (NTLM, Kerberos), Digest • SQL Server level • Uses WS-Security UserName token • Requires SSL to secure the channel

11. HTTP EndpointsEndpoint CreationWSDL Request

12. WSDL Support • Dynamic support • Out of the box SP for WSDL generation • Complex • Used when complex types are used • ?WSDL yields complex WSDL • Simple • Uses primitive types • Better interoperability • ?WSDLSIMPLE yields simple WSDL • Custom SP can be called for WSDL generation

13. SOAP Responses • UDFs are simple • Stored procedure result sets are returned as object arrays or datasets • Batches are returned similar to stored procedures • Clients can consume the results and operate upon the same

14. HTTP EndpointsConsuming WSDL

15. What about SQLXML? • SQLXML Offers • Templates • Updategrams, Bulkload, Query strings • Post processing • SOAP Access offers • No dependency on IIS • ~40% improvement over SQLXML • Full support for Batches • Dynamic WSDL

16. Service Broker

17. Asynchronous Applications • Useful for modeling long running transactions • Does not require user intervention

18. Messaging - Hard Problems • Message Ordering • Skipped or duplicate messages • Rolled back messages • Correlation • Relating sent messages to received ones • Multi-threading • Related messages received on different threads • Queue reader management • What happens when a Queue reader goes away? • How many Queue readers are needed?

19. Service Broker • Database Application Framework • Loosely coupled services • Asynchronous actions • Queues are first-class database objects • Data integrity • Fault tolerance • Reliability • New DDL and DML

20. SQL Server itself uses SSB • Query Notifications • Event Notifications

21. Terminology • Message Types • Atoms of Service Broker • Can be typed or un-typed • Can be grouped into contracts • Contract • Defines the messages that are allowed • Specifies INITIATOR, TARGET or ANY • Queue • Endpoint of services • Can point to a service program • Can also specify number of readers • Service • External interface for the queue • Contracts are enforced by the service • Conversation • Service Program

22. Service BrokerSimple Service

23. Architecture Application Layer Meta Data Layer Messages Messages Contracts Contracts Dialog Conversation Services Services Physical Layer TCP / IP Queue Queue Transport Transport

24. Dialogs • Ensure in-order processing • Across transactions • Across sending threads • Across receiving threads

25. Conversation Groups • Grouping of related conversations • Group Identifier • Associated with messages • Guarantee exclusive access to messages • Special service instance lock allows multiple queue readers • Can store application state indexed by conversation ID • Move conversations to conversation groups

26. Message Transport • Dialog Protocol • End-to-end protocol • Exactly once delivery • In-order delivery • Authentication and encryption • Transport Protocol • Efficient binary message format • TCP/IP based • Bi-directional, multiplexed, best-effort

27. Transactional Semantics • A single transaction can encompass: • Beginning a conversation • Sending a message • Receiving messages • Modifications to the database tables • Storing state for the conversation

28. Service BrokerComplex Service

29. Routing • Service Broker instances can be inter-instance • Define the required endpoints • Define routing table • Specify broker name and instance • Specify address of next hop • Decision tree is used during dialog creation • Look in local database instance • Look in route tables • Look for default route • Look in other databases (same instance)

30. Messaging in the Database • Integrated management, deployment and operations • Backup, restore, configuration, monitoring, security • Start and shutdown, start, recover and stop Service Broker • Queued messages and application state may be queried • Trace events for monitoring and debugging • Messages have database integrity and recoverability

31. Messaging in the Database • Performance advantages • No two-phase commits for transactional messaging • Single log write on commit • No process boundary crossing to external messaging software • Optimized “in-instance” delivery

32. Positioning • Against BizTalk • No workflow support • Custom protocol used for communication • May not be firewall friendly • Against MSMQ • SQL Server is a requirement • Both ends must be SQL Server 2005 • Built-in capability of triggering • No message size limit • Chunking of messages is automatic • Can throttle the number of queue readers • Against COM+ Queued Components • More robust • Against Web Services • Custom protocol used for communication • Against Indigo • Requires SQL Server 2005 at both ends • Message persistence built-in

33. Summary • HTTP Endpoints • Use when IIS and SQL Server are on the same machine • Do not expose directly to the internet. Use firewalls and proper privileges • Not good for high traffic access • For heavy business logic, shift to the middle tier • Service Broker • Use when multi-threading is required • Use when asynchronous processing is required in databases • Use when the endpoints are known to be SQL Server • For complex scenarios not involving SQL Server, MSMQ, Indigo or BizTalk is a better bet

34. Resources • Service Oriented Architecture • http://msdn.microsoft.com/architecture/soa/default.aspx • SQL Server Web casts • http://www.microsoft.com/events/series/msdnsqlserver2005.mspx • SQL Server Development Center • http://msdn.microsoft.com/sql/2005/2005articles/default.aspx

35. Your Feedbackis Important! Please Fill Out the feedback form

36. © 2005 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.