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 components?because 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 endpoint?a 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 payloads?the 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] @ID [int] AS BEGIN SET NOCOUNT ON; SELECT [FirstName],[MiddleName],[LastName] FROM [AdventureWorks].[Person].[Contact] WHERE ContactID = @ID END;
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.
Creating and Launching Endpoints
You use the CREATE ENDPOINT statement to create an endpoint as shown below:
CREATE ENDPOINT SQLEP_AWPersons STATE = STARTED AS HTTP ( PATH = '/AWpersons', AUTHENTICATION = (INTEGRATED), PORTS = (CLEAR), SITE = 'sqlidw14' ) FOR SOAP ( WEBMETHOD 'PersonInfo' (NAME='AdventureWorks.dbo.uspGetPersonInfo'), BATCHES = DISABLED, WSDL = DEFAULT, DATABASE = 'AdventureWorks', NAMESPACE = 'http://Adventure-Works/Persons' )
The CREATE ENDPOINT statement has several key components. The STATE argument is set to STARTED, which indicates that the endpoint listener is running. Other options included STOPPED and DISABLED for handling service endpoints. The rest of the code sits in the AS HTTP clause or the FOR SOAP clause. AS HTTP sets HTTP as the transport for the endpoint and includes the following settings:
- PATH?Specifies the URL for the service endpoint, and in this example defaults to the server name and the path specified, e.g. http://yourdemoserver/AWpersons.
- AUTHENTICATION?Specifies the type of authentication to be used for the service endpoint. A variety of authentication mechanisms are supported, including BASIC, INTEGRATED (uses Windows security), NTLM, and KERBEROS.
- PORTS?Specifies what port type to use. CLEAR or SSL are the supported options, with CLEAR accepting only HTTP requests and SSL requiring HTTPS.
- SITE?Specifies the name of the host computer for the endpoint.
The FOR SOAP clause supports the following arguments:
- WEBMETHOD?Specifies the Web method used to send requests via the HTTP SOAP endpoint. You can declare multiple Web methods per endpoint.
- BATCHES?Specifies whether not the endpoint supports ad-hoc SQL requests. This is disabled by default.
- WSDL?Specifies whether the endpoint supports WSDL. For custom WSDL implementations, you can provide a stored procedure name that returns a custom WSDL implementation.
- DATABASE?Specifies the name of the database where the requested operation is executed in context.
- NAMESPACE?Specifies a namespace for the endpoint.
After creating the endpoint, you can submit an HTTP request to the server to ensure that the endpoint is responding. A simple test is to navigate to http://sqlidw14/awpersons?wsdl (substitute your own server and endpoint name), which, if the endpoint is responding, will return the WSDL for your exposed service.
To consume the Web service, simply follow the steps for adding a Web reference and invoking a Web service in a standard .NET application. The downloadable sample application includes a simple application for testing purposes, including the following code to invoke the Web service:
sqlidw14.SQLEP_AWPersons proxy = new sqlidw14.SQLEP_AWPersons(); i = Convert.ToInt32(txtID.Text); proxy.UseDefaultCredentials = true; results = proxy.PersonInfo(i);
After the Web service returns the results, the sample client application displays the data.
Modifying Existing Endpoints
Endpoints can be very useful, but any feature that relies on a mapping to underlying database objects must support the modification of those endpoints as needed. One common modification of an endpoint is to change its state. As you saw in the earlier example, the CREATE ENDPOINT statement supports a state argument that lets you start, stop, or disable the endpoint. So, to change the state of an endpoint, use a simple ALTER ENDPOINT statement as shown below.
ALTER ENDPOINT SQLEP_AWPersons STATE = STOPPED
After stopping an endpoint, using a STATE = STARTED clause starts the endpoint listener again. However if the endpoint is DISABLED, you cannot start it again until the SQL Server service (MSSQLSERVER) is restarted.
You can also alter the endpoint in other ways. For example, the earlier CREATE ENDPOINT example enabled only one method for the SOAP endpoint. To add another method, use the following code:
ALTER ENDPOINT SQLEP_AWPersons FOR SOAP ( ADD WEBMETHOD 'BillOfMaterials' (name='AdventureWorks.dbo.uspGetBillOfMaterials') );
When you run it, the preceding statement adds a second method mapped to a stored procedure in the AdventureWorks database that retrieves a bill of materials. In general, the ALTER ENDPOINT statement makes it easy to change endpoint state, add methods, and alter the endpoint as needed for your application.
When to Use HTTP/SOAP Endpoints
As you can see, creating an HTTP/SOAP endpoint is relatively simple, and you can implement one with only a few lines of code. But that begs the question: How and when should you use this functionality in your applications?
Like any new technology, you shouldn’t use HTTP/SOAP endpoints simply for technology’s sake, but rather as a tool that increases productivity or provides new capabilities. From an architectural perspective, HTTP/SOAP endpoints do introduce some problems. When you create an endpoint, you’re adding a layer of abstraction to the database, and in the end you still need to write a program to consume a Web service to get to your data. If you’re writing a .NET application and need to access data in SQL Server, using an HTTP/SOAP endpoint is probably not a good way to do that. On the other hand, if you’re using a development tool or application that can consume Web services more easily than it can leverage APIs to access data in SQL Server, then HTTP/SOAP endpoints may be an appropriate solution.
One challenge with HTTP/SOAP endpoints is configuration management and security. While it is easy to create and expose an endpoint, best practices dictate that endpoint access should be restricted to individuals or small groups, that you use SSL to exchange data, that SQL Server reside behind a firewall, and that you use Kerberos for authentication. While these requirements do make sense given the importance of keeping your data secure, they may also take away from some of the perceived benefits of agility and ease of deployment for endpoint services. But you don’t want to trade off security for agility.
In addition, you’ll need to carefully consider the performance implications when evaluating the Web services features supported by HTTP/SOAP endpoints. If your application consumes and produces raw XML, or if your application relies on business logic already well represented by SQL Server stored procedures, then HTTP/SOAP endpoints can easily expose this business logic to other applications and may make a lot of sense for your solution. However, if your application inserts or retrieves large binary objects from a database, the overhead associated with converting those payloads into SOAP messages may not make sense. In addition, if your application has high mission-critical performance requirements, then HTTP/SOAP endpoints are not the appropriate tool to use for your implementation.
The ideal scenario for using HTTP/SOAP endpoints will be to integrate inside-the-firewall applications that support Web services. If you have an integration tool or other application that natively supports Web services but doesn’t run on Windows or doesn’t integrate easily with SQL Server, then the endpoints solution is a low-friction way to accomplish the integration with a minimal amount of code.
Leveraging HTTP/SOAP Endpoints Effectively
If you have data on the SQL Server platform and an extensive investment in stored procedures for those systems, you’re already aware of the challenges associated with taking that code base and moving towards a more service-oriented approach to application deployment. As part of that effort, you’ll want to keep a few key do’s and don’ts in mind.
- Use HTTP endpoints to simplify the process of exposing your database logic to your applications.
- Focus on the use of HTTP endpoints for inside the firewall scenarios.
- Leverage HTTP endpoints for solutions that don’t require linear scale.
- Use HTTP endpoints on SQL Servers directly connected to the Internet. If you can’t avoid doing so, ensure that you’re following all of the appropriate security guidelines and that you’re leveraging the advanced authentication mechanisms available.
- Put SQL Server on the same machines that support IIS or other Web server products (HTTP endpoints eliminates the need to do this).
- Turn on the BATCHES capability that enables ad-hoc SQL commands to be processed through the endpoint.
Because HTTP endpoints can’t effectively support a scale-out deployment model, you’ll want to focus your use to inside-the-firewall scenarios. For security reasons, you’ll also want to concentrate on scenarios that limit the exposure of your database infrastructure to public networks.
One of the benefits of HTTP/SOAP endpoints is that you can begin to enable a service-oriented architecture in smaller steps without having to completely rewrite your existing applications. Because the exposed services support WSDL (both a default WSDL implementation and custom implementations), your service should be interoperable with existing Web services tools, and should also support integration with upcoming Indigo-based services.
When deciding whether to use endpoints, you’ll need to determine whether your existing investment in database code is primarily static, or whether the code base is fairly dynamic and evolves steadily. For an evolving code base, a new architecture based on .NET Web services and updated data access code may make more sense. For organizations that have made extensive investments in SQLXML and are looking to migrate that technology to a new platform, or for those that want to expose business services without having to redesign their SQL Server applications, HTTP/SOAP endpoints should be a very good solution.