1. No category

SODA – SOA in Databases
SQL Service Broker
HTTP Endpoints
Srinivas Sampath
MVP SQL Server
Head – Technology Labs
SCT Software Solutions, India
Agenda
SOA Basics
HTTP Endpoints
Service Broker
Questions
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
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
HTTP Endpoints
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
Access Mechanisms
`
SQLXML
IIS
ODBC
OLEDB
ADO.NET
TDS
TCP
N Pipes
Client
`
Client
Database Server
SOAP
HTTP
Database Server
Microsoft Windows 2003, Microsoft
Windows XP SP2
Kernel Mode HTTP.SYS Support
No requirement for IIS
Architecture
RPC Access
ENDPOINT
WSDL Request
Stored Procedure
WSDL Generator
Batch Access
Endpoints are the point of
communication
Created using CREATE ENDPOINT
Has a name
Transport Options
Payload Options
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
HTTP Endpoints
Endpoint Creation
WSDL Request
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
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
HTTP Endpoints
Consuming WSDL
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
Service Broker
Asynchronous Applications
Useful for modeling long running
transactions
Does not require user intervention
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?
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
SQL Server itself uses SSB
Query Notifications
Event Notifications
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
Service Broker
Simple Service
Architecture
Application
Layer
`
`
Meta Data
Layer
Messages
Messages
Contracts
Contracts
Services
Dialog Conversation
Transport
Queue
Queue
Physical
Layer
Transport
TCP / IP
Services
Dialogs
Ensure in-order processing
Across transactions
Across sending threads
Across receiving threads
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
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
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
Service Broker
Complex Service
Routing
Service Broker instances can be interinstance
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)
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
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
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
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
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
Other Resources
Email: [email protected]
URL: http://www32.brinkster.com/srisamp
Blog: http://blogs.sqlxml.org/srinivassampath
Your Feedback
is Important!
Please Fill Out the
feedback form
© 2005 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.