.NET Enterprise Services Technologies : SQL Server

10/13/2010 9:10:28 AM
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:

  • Native XML Web Services

  • Service Broker

  • Query Notification Services

  • SQL CLR integration

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

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.
PATH = '/Customers',
SITE = 'localhost'
WEBMETHOD 'GetCustomers'

The CREATE ENDPOINT statement has several possible arguments, as explained in Table 1.

Table 1. Arguments supporting the CREATE ENDPOINT statement.
STATEspecifies if the initial state of the endpoint is started or stopped
AS HTTPthe transport protocol to use (TCP or HTTP)
PATHthe URL the service consumer will use to reach the service
AUTHENTICATIONhow clients will be authenticated (supported options include BASIC, DIGEST, NTLM, KERBEROS, and INTEGRATED)
PORTSspecifies whether the client will listen on SSL or CLEAR text
SITEthe host name of the computer
FOR SOAPspecifies whether the endpoint uses SOAP messaging
WEBMETHODused to map a Web method to an underlying stored procedure or function
WSDLspecifies whether WSDL generation is supported for the endpoint
DATABASEthe database that contains the stored procedure and data
NAMESPACEthe 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.

Figure 1. The HTTP listener receives the request over port 80 and directs the request to the endpoint defined within SQL 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.

Figure 2. The message travels from the service endpoint to the queue and then to the service program.

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:

  • data changes

  • message arrival

  • timer firing (triggers the service logic at the end of an interval to execute and do something)

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.

Figure 3. Interaction between messages, services, and queues in Service Broker.

In order to work with SSB, it must be enabled using the ALTER DATABASE command:


A stored procedure must be created to encapsulate the service logic required to process a message:

Example 3.
-- Process Logic

A message is subsequently created with validation type WELL_FORMED_XML (a statement that allows well-formed XML messages to be processed):

Example 4.

Here we create a contract for the message:

Example 5.
CREATE CONTRACT [MessageContract]
[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.
PROCEDURE_NAME = ProcessMessage,

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.

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.
BEGIN DIALOG @RequestDialog
ON CONTRACT MessageContract
MESSAGE TYPE SendMessage (N'My Request Message');
MESSAGE TYPE SendMessage (N'My Request Message 2');

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).

SOA Principles & Patterns

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
SqlCommand cmd = new SqlCommand
("SELECT FirstName,LastName FROM dbo.Customers", conn);
SqlDependency depend = new SqlDependency(cmd);
depend.OnChange += new OnChangeEventHandler
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.

Summary of Key Points

  • 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.

PS4 game trailer XBox One game trailer
WiiU game trailer 3ds game trailer
Top 10 Video Game
-   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Total War: Warhammer [PC] Demigryph Trailer
-   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
-   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
-   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
-   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
-   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
-   Satellite Reign [PC] Release Date Trailer