SQL
Server 2005 and 2008 extend core database functionality to include
analysis, reporting, integration, replication, and notification
services. SQL Server databases can play a significant role in the
design of service-oriented solutions due to the many ways they can
support cross-service loose coupling and state management deferral.
Some of the primary SQL Server technologies and features most relevant to service-oriented solution design include:
Additionally,
SQL Server’s native support for XML directly facilitates the XML-based
SOAP messaging framework provided natively by WCF.
Native XML Web Services Support
Web service
technologies and industry standards compliance are intrinsic to SQL
Server and can be leveraged in many different ways. Provided here is a
list of characteristics and features that describe different aspects of
Web service support and are directly relevant to service-oriented
solution architecture:
SQL Server can expose Web service contracts based on WSDL, SOAP, and WS-Security.
SQL Server can receive data via XML and SOAP messages in addition to traditional persistent binary connections.
SQL Server Web service access is built on top of the Microsoft Windows Server HTTP kernel-mode driver (Http.sys).
SQL
Server has the ability to process and transform SOAP messages into
native formats used by internal database components, such as stored
procedures.
SQL Server provides a runtime environment to host service endpoints.
SQL Server provides application pooling and other activation logic.
The
ability to host a service endpoint within SQL Server is supported,
thereby leveraging other features, such as transactions, data
management, and security.
SQL
Server provides a separate Service Broker, which includes a tightly
integrated messaging feature that receives, processes, and sends
messages reliably.
SQL Server databases are event-driven, allowing you to raise events based on changes taking place in the data.
SQL
Query is provided by SQL Server as a means of notification that enables
data-dependent caches to be notified when an underlying database has
changed.
SQL CLR
integrates the .NET CLR into databases, allowing automation logic to be
written in C# or VB.NET and processed directly inside SQL Server
(thereby reducing latencies due to remote data access).
SQL
Server allows you to mark a stored procedure or a scalar value function
as a Web service and program against it using SOAP-compliant clients.
This approach is well-suited for developing entity and utility services.
A
Web service hosted in SQL Server maps directly to a stored procedure or
a function. In this example we create such a stored procedure:
Example 1.
CREATE PROC dbo.GetCustomersProc
AS
SELECT
FirstName,LastName
FROM
dbo.Customers
|
SQL Server 2005 introduced the new CREATE ENDPOINT
statement to create HTTP and TCP endpoints, which continues to be
supported in SQL Server 2008. The next code sample creates an endpoint
called Customers that maps to the dbo.GetCustomersProc stored procedure:
Example 2.
http://localhost/Customers?wsdl.
CREATE ENDPOINT Customers
STATE = STARTED
AS HTTP
(
PATH = '/Customers',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = 'localhost'
)
FOR SOAP
(
WEBMETHOD 'GetCustomers'
(NAME='SQLWS.dbo.GetCustomersProc'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'SQLWS',
NAMESPACE = 'http://example.org/samples'
)
GO
|
The CREATE ENDPOINT statement has several possible arguments, as explained in Table 1.
Table 1. Arguments supporting the CREATE ENDPOINT statement.
| Argument | Description |
|---|
| STATE | specifies if the initial state of the endpoint is started or stopped |
| AS HTTP | the transport protocol to use (TCP or HTTP) |
| PATH | the URL the service consumer will use to reach the service |
| AUTHENTICATION | how clients will be authenticated (supported options include BASIC, DIGEST, NTLM, KERBEROS, and INTEGRATED) |
| PORTS | specifies whether the client will listen on SSL or CLEAR text |
| SITE | the host name of the computer |
| FOR SOAP | specifies whether the endpoint uses SOAP messaging |
| WEBMETHOD | used to map a Web method to an underlying stored procedure or function |
| WSDL | specifies whether WSDL generation is supported for the endpoint |
| DATABASE | the database that contains the stored procedure and data |
| NAMESPACE | the XML namespace for the message |
SQL Server endpoints
support basic, digest, integrated (NTLM or Kerberos), and regular SQL
Server authentication in addition to WS-Security, which allows security
tokens to be passed using the username token headers for
authentication. SQL Server endpoints are off by default and need to be
explicitly enabled using the GRANT CONNECT statement. Channels can be secured with SSL using the PORTS argument listed in Table 1.
SQL Server Native Web Services support is tightly integrated with the Windows Server Kernel-mode HTTP listener as shown in Figure 1. This precludes the need for IIS to be installed and managed on the server.
Endpoints
created can be listed by querying the catalog view. All endpoints
created can be queried using the following SQL statement:
SELECT * FROM sys.endpoints;
Endpoints that specifically use HTTP for transport can be accessed using the SQL statement:
SELECT * FROM sys.http_endpoints;
SOAP methods and the associated database objects can be accessed using the following SQL statement:
SELECT * FROM sys.endpoint_webmethods;
Service Broker (SSB)
SSB is a messaging
extension that integrates reliable message processing functionality
with SQL Server databases and is primarily useful when service
compositions rely on the guaranteed delivery of ordered messages.
Messages sent using SSB are durable, which ensures that if message
processing logic fails (or the system itself fails), the message
survives and is placed back into the receive queue.
Essentially,
when messages are sent to service endpoints, services place them in a
queue from where they are picked up and processed. Each message is
processed by an internal service program (typically a stored
procedure), as shown in Figure 2.
SSB can be configured so that
multiple instances of the service program can be used to process
incoming messages. The queue is a database table and therefore
leverages all the reliability features in SQL Server, such as recovery
in the event of a database failure and guaranteed delivery to the queue
using database transactions.
In addition to
performing standard request-response message exchanges, SSB also
handles correlation and concurrency of messages between the services
and consumers. It introduces dialogs,
which are bidirectional streams of messages between two endpoints. All
messages in a dialog are sent in order, and the order is maintained
across input threads and machine restarts. Each message includes a
conversation handle to identify the dialog.
To avoid any concurrency issues, SSB provides a way of grouping dialogs used for a specific task via conversation groups. A conversation group is implemented with a conversation group identifier
included with all messages in all dialogs contained in the conversation
group. When a message is received from any of the dialogs in a
conversation group, the group is locked and the lock is held by the
receiving transaction. This allows services to be resilient to problems
that are caused by simultaneous processing of a single message.
Message types are
grouped into contracts and are used to describe all the messages
received by a specific dialog. Contracts are grouped together to form a
service. A service represents the dialogs required to process messages,
and it can be associated with one or more queues.
The result of
implementing a service endpoint with SQL Server is that the service
logic is much more closely bound to the data. This makes it possible to
natively trigger events within the logic as a result of data changes
because the service logic is bound to query notifications and events.
When changes happen inside a
database, notifications about the change can also be pushed to the
outside world using query notifications. SQL Server has an eventing
infrastructure built into it with event-driven service logic that can
be triggered by:
SSB complements the Native XML
Web Services implementation in SQL Server. A service endpoint
implemented in SQL Server using Native XML Web Services can be
augmented using SSB to make the endpoint implementation reliable and
scalable.
The upcoming code examples show the interaction between messages, services, and queues based on the simple scenario depicted in Figure 3.
In order to work with SSB, it must be enabled using the ALTER DATABASE command:
ALTER DATABASE databasename SET ENABLE_BROKER
A stored procedure must be created to encapsulate the service logic required to process a message:
Example 3.
CREATE PROCEDURE ProcessMessage
AS
BEGIN
SET NOCOUNT ON;
-- Process Logic
END
|
A message is subsequently created with validation type WELL_FORMED_XML (a statement that allows well-formed XML messages to be processed):
Example 4.
CREATE MESSAGE TYPE ReceiveMessage
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE SendMessage
VALIDATION = WELL_FORMED_XML;
|
Here we create a contract for the message:
Example 5.
CREATE CONTRACT [MessageContract]
(
[SendMessage] SENT BY INITIATOR,
[ReceiveMessage] SENT BY TARGET
)
|
Next, a queue is associated with a stored procedure. The queue activates the stored procedure ProcessMessage when a message arrives:
Example 6.
CREATE QUEUE REQUEST_QUEUE
WITH STATUS=ON,
ACTIVATION
(
PROCEDURE_NAME = ProcessMessage,
MAX_QUEUE_READERS = 3,
EXECUTE AS SELF
);
CREATE QUEUE RESPONSE_QUEUE
|
Each service exists as an endpoint in SSB, so to create a service we need to use the queue MESSAGE_QUEUE and associate it to the message contract MessageContract.
This service will only accept messages that conform to MessageContract:
Example 7.
CREATE SERVICE REQUEST_SERVICE
ON QUEUE [REQUEST_QUEUE]
(
[MessageContract]
);
CREATE SERVICE RESPONSE_SERVICE
ON QUEUE [RESPONSE_QUEUE]
(
[MessageContract]
);
|
At this point, all the
necessary SSB objects have been created and can be used in queuing
solutions. This next example shows how messages can be sent to the REQUEST_QUEUE queue:
Example 8.
DECLARE @RequestDialog UNIQUEIDENTIFIER
BEGIN TRANSACTION
BEGIN DIALOG @RequestDialog
FROM SERVICE RESPONSE_SERVICE
TO SERVICE 'REQUEST_SERVICE'
ON CONTRACT MessageContract
WITH LIFETIME = 1000;
SEND ON CONVERSATION @RequestDialog
MESSAGE TYPE SendMessage (N'My Request Message');
SEND ON CONVERSATION @RequestDialog
MESSAGE TYPE SendMessage (N'My Request Message 2');
COMMIT TRANSACTION
|
The listing starts with creating a unique identifier that will be assigned to the dialog. The BEGIN DIALOG statement is used to open a new dialog with SSB. The FROM SERVICE statement identifies the initiator of the messages, while the TO SERVICE statement identifies the target. The SEND statement sends messages, which are received by the target service and added to the queue.
Query Notification
Query
Notification solves the problem of having to poll a database to get
updated data. With Query Notification, SQL Server can notify .NET
routines when any data manipulation language (DML) operations, such as
insert, update, or delete are invoked (which are performed on specified
database tables).
|
The functionality
provided by Query Notification is comparable to messaging exchange
mechanisms that result from the application of Event-Driven Messaging .
|
As shown in the following example, the notification cycle starts from .NET 2.0 applications using the SqlDependency class in System.Data.SqlClient namespace. SqlDependency object takes SQLCommand object as the parameter. When the data changes, SQL Server raises an event that invokes the GetNotified method.
Example 9.
SqlConnection conn = new SqlConnection
(DBConnectionString);
SqlCommand cmd = new SqlCommand
("SELECT FirstName,LastName FROM dbo.Customers", conn);
SqlDependency depend = new SqlDependency(cmd);
SqlDependency.Start(DBConnectionString);
depend.OnChange += new OnChangeEventHandler
(GetNotified);
static void GetNotified(object caller,
SqlNotificationEventArgs e)
{
string msg = "Notified Received";
}
|
XML Support in SQL Server
SQL Server 2005 includes a
native data type called XML. The XML data type supports both typed and
untyped XML and a table can contain one or more columns of type XML in
addition to relational columns. The XML data types can be queried using
XQuery, which includes XPath 2.0 for navigating the XML document and a
language for modifying XML data. Besides XQuery, T-SQL includes support
for transforming data between XML and relational format seamlessly
using FOR XML and OPENXML.
XML documents are stored as
BLOBs in order to support XML characteristics such as recursive
processing. SQL Server includes an XML schema collection to persist
schemas in the
database. The XML data type can be linked to an XML schema in the
collection to enforce schema constraints on XML instances.
Indexes play an important
role in optimizing relational tables for searches. SQL Server extends
the concept of indexes to XML documents. Very large XML documents can
be indexed using a primary XML index on an XML column. The primary XML
index provides efficient evaluation of queries on XML data.
SQL Server extensions include Native XML Web Services, Service Broker (SSB), and deep support for XML at the database level. Native
XML Web Services can be used to expose stored procedures and
user-defined functions in a database as a Web service, allowing SQL
Server to act as a service by creating and hosting endpoints. SQL
Server Query Notification enables applications to request a
notification from SQL Server when the result of a query changes. Query
notifications also allow an application to rely on cached data until
the data changes. SQL
Server includes extensive support for processing XML documents
including the ability to query, parse, transform, index, and generate
XML documents.
|
