.NET

Over the past year, I've been discussing some of the various technologies found inside SQL Server 2005. Three of these technologies are CLR integration, HTTP endpoints, and Service Broker. (Articles on these topics were published, respectively, in the November 2005, March 2006, and November 2006 editions of the DNDJ.) Each of these is a powerful tool in its own right, and can be used to great effect in almost any SQL installation. When used together, however, they become much more powerful. Used together, these tools let SQL Server become an active participant in a service-oriented system. Not only can SQL Server provide the data used by these services but it can host the services themselves. This is an example of what Microsoft calls a "Service-Oriented Database Architecture" or SODA, and it's another powerful capability of the SQL Server 2005 system.

Simply put, a SODA uses the database server as a provider of services in a Service Oriented Architecture or SOA. If you think about it, this makes perfect sense. I can't count the number of services that I've created that are really nothing more than wrappers around stored-procedure calls. For example, a customer Web Service might provide, among other things, a "CustomerList" method and a "GetCustomer" method. Both of these methods would typically be implemented via calls to stored procedures defined in a SQL Server database. In these cases, it might make sense to eliminate the Web server entirely. If SQL Server can host the service as readily, and as securely, as the Web server can then the Web server might not be necessary. This thought started me down the path of understanding and accepting the promise of SODA.

After considerable thought and research I've decided that I agree with the idea of SODA, and I think you will too. In this article, I'll show how SQL Server can create and host services in an SOA environment. I'll discuss why CLR integration, HTTP endpoints, and Service Broker are key technologies in this environment, and how they each contribute to the whole solution. I'll assume that you're familiar with the concepts involved with these technologies. If you're not, I'd suggest that you read the articles mentioned above. If you're ready, we'll start with a discussion of the requirements of an SOA provider.

Requirements of an SOA Provider
An application can't be considered a service provider unless it can actually provide one or more services. To do this effectively, an application must possess three core attributes:

• Communications - The application must be able to communicate with clients and/or other service providers. Typically, this involves using TCP/IP, HTTP, and SOAP. So the application must be able to process whatever TCP/IP packets, HTTP headers, and SOAP envelopes are required.
• Messaging - The application must be able to send and receive data. Normally, this data is encoded inside an XML document. As a result, the application must be able to extract message data from this XML document. In addition, the application must be able to encode new messages, using an appropriate XML format or schema. The ability to process these messages asynchronously, while not actually required, is an extremely valuable addition.
• Process Logic - The application must be able to perform whatever business logic is required. A service may perform tasks of almost any complexity. The application must be able to execute the steps needed, no matter how intricate.

Together these core attributes represent the minimum requirements of a service provider. SQL Server 2005 not only meets these requirements, but it exceeds them. Let's examine each requirement in turn, and concentrate on the technologies inside SQL Server 2005 that satisfy them.

Process Logic via CLR Integration
One of the most pervasive new features of SQL Server 2005 is CLR integration. This feature is, conceptually, nothing more than the ability to create SQL objects (stored procedures, functions, triggers, user-defined data types, and user-defined aggregates) using a .NET language instead of T-SQL. As simple as it may sound, however, it's extremely powerful. Stored procedures written in, say, Visual Basic.NET can perform tasks that aren't possible in T-SQL. In addition, procedures written in a .NET language may execute much faster than those written in T-SQL. (I say "may" because T-SQL is still useful, and it's a better choice for many batch-oriented operations.) For more information about CLR integration, see my article in the November 2005 issue of the .NET Developer's Journal.

CLR integration greatly expands our ability to create process logic. T-SQL is a good language, and all SQL developers should learn to use it well. However, complex logic is best handled via a high-level language like C# or Visual Basic. Many services implement business processes that are difficult if not impossible to code via T-SQL. Other services implement processes that must run very quickly and are simply too slow when written in T-SQL. In these cases, CLR integration is the key that lets SQL Server 2005 host such services efficiently.

Communications via HTTP Endpoints
HTTP endpoints are new to SQL Server 2005. They expose stored procedures and scalar-valued functions to clients via the HTTP and SOAP protocols. Using these endpoints, SQL developers can create standard XML Web Services with very little work. SQL Server hosts these Web Services directly, using the HTTP.SYS component of Windows XP Service Pack 2 and Windows Server 2003. So no Web server is needed. Communications data, such as stored procedure and function parameters, return values, result sets, and error messages, are converted to and from XML automatically. SQL Server also handles the creation of standard service-description documents (WSDL), although custom documents can be supplied if necessary. Finally, endpoint security is managed via the standard principles, securables, and permissions used by all other SQL subsystems. T-SQL statements are all that are needed to create and maintain these services, although CLR integration may also be used when the services become more complex. For more information about HTTP endpoints, see my article in the March 2006 issue of the .NET Developer's Journal.

Messaging via CLR Integration and Service Broker
I mentioned that HTTP endpoints automatically convert communications data to and from XML. This is true, and it works quite well in many scenarios. However, as your services become more complex and/or more highly utilized, you'll find that this simple mapping is no longer sufficient. This may be because the data itself becomes too complex, or it may be because you need more than just the simple call-and-response interaction typical of simple services. In these cases, you'll need to take advantage of some additional features of SQL Server.

The first feature is CLR integration. CLR integration lets us use a high-level language to manipulate large and/or complex messages with relative ease. These messages are typically XML documents. The .NET Framework contains many pre-built classes that are specially designed to make XML manipulation easy. With CLR integration, we can take advantage of these classes. On the other hand, if these classes aren't well-suited for a particular task then we can write our own classes or purchase those created by a third party. We're not limited to the XML support provided with SQL Server.

The second feature is Service Broker, which is an entire messaging system in its own right. It efficiently implements message queuing, intra-server message routing, message encryption, and message signing. Among other things, it allows services to become asynchronous. Such services accept requests from clients and immediately return a response. However, executing the request can take days. (Think of a typical expense reporting system, for example.) Service Broker provides the infrastructure for such systems. For more information about Service Broker, see my article in the November 2006 issue of the .NET Developer's Journal.

Other Useful Technologies
The technologies discussed above, CLR integration, HTTP endpoints, and Service Broker, allow SQL Server 2005 to meet the minimum requirements of an SOA provider. Using them, developers can make perfectly acceptable Web Services. There are other technologies, however, that provide additional functionality that can be used to make these services more robust. Two of my favorites are Notification Services and Query Notifications. Both of these are briefly described below:

• Notification Services - allows users to subscribe to events and to be notified when those events occur. Notifications can be formatted in various ways and delivered via different protocols, all according to the users' desires. Developers have quite a bit of control over the workings of the system, and can extend its capabilities when needed. An example use of this capability would be the expense reporting system mentioned earlier. This system might send us an e-mail when our checks are ready. Notification Services could be used to implement this e-mail functionality.
• Query Notifications - allows remote caches of table data to be invalidated when applicable data changes. An example of this is the SQLCacheDependency feature of ASP.NET 2.0 applications. Developers can cache the results of a query and indicate that the cache should be cleared when the query's source data is updated. Such caching can greatly improve the responsiveness of applications and services.

Summary
We've now seen how SQL Server can provide services in an SOA environment. We've seen how it can provide these services using its own capabilities, without having to rely on the support of external servers. Along the way, we've been introduced to five technologies that are new to SQL Server 2005 and have seen how they might help us create our own services. Once again, SQL Server shows that it is much more than just another database engine.