QL Server 2005's HTTP/SOAP endpoints provide SQL Server developers with new capabilities for using Web services within SQL Server. It's worth noting though that Web services aren't new to SQL Server 2005. In SQL Server 2000, SQLXML provided a set of supplemental tools that extended SQL Server's existing support for retrieving and storing XML data. SQLXML 3.0 supported Web services that enabled executing stored procedures, user-defined functions, and also supported templates. However, one of the challenges with SQLXML was a complex deployment process that required IIS to handle HTTP requests and enable the invocation of Web services.
In SQL Server 2005 the HTTP/SOAP endpoints feature lets you consume Web services mapped to objects in SQL Server. Unlike earlier versions, IIS is no longer required. The Windows Server 2003 kernel listener, HTTP.sys
, lets SQL Server listen for HTTP requests and process them directly. This simplifies application development and deployment, and requires fewer infrastructure componentsbecause it's not best practice to deploy IIS on the main SQL Server instance. In this article, you'll see what is required to create and expose an HTTP/SOAP endpoint for use in your application. You'll also see some considerations that can help you determine when this technology is appropriate, and when to avoid it.
HTTP/SOAP Endpoints Explained
SQL Server 2005 supports the concept of an endpointa service that listens for requests natively within the server. Each endpoint supports a protocol, which can be TCP or HTTP, and a payload type, which can include support for database mirroring, service broker, T-SQL, or SOAP. This article focuses on HTTP/SOAP endpoints supporting SOAP payloadsthe core elements of a Web service implementation.
To get started, you first need to determine what object you want to expose from SQL Server. You can expose stored procedures or user-defined functions as the endpoints for the mapping but you can't expose tables or views directly. As an example, here's a simple stored procedure from the AdventureWorks database that you can use:
CREATE PROCEDURE [dbo].[uspGetPersonInfo]
SET NOCOUNT ON;
WHERE ContactID = @ID
This stored procedure simply returns the name of a person from the Person.Contact
table in AdventureWorks based on a ContactID
input parameter value, giving you a result set to test with. After defining which object you want to expose, you'll need to run the T-SQL commands that expose and launch the endpoint.