Browse DevX
Sign up for e-mail newsletters from DevX


Easy Web Services with SQL Server 2005 HTTP Endpoints : Page 3

By leveraging HTTP endpoints and SOAP in SQL Server 2005, developers can bypass IIS and use SQL Server directly to expose Web services and simplify their applications.

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.

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:

     ADD WEBMETHOD 'BillOfMaterials' 
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.

Randy Holloway specializes in application server technologies, including SQL Server and BizTalk, in Microsoft's Central Region. He is also the author of the book "Developing Solutions with SQL Server 2005" from Addison-Wesley. Check out his weblog here.
Thanks for your registration, follow us on our social networks to keep up-to-date