SQL Server 2005 has introduced a wonderful feature for SOA scenarios: HTTP Endpoints. HTTP Endpoints permits you to directly expose SQL Server data (or stored procedures) to external applications as Web Services.
Exposing a SQL Server stored procedure as a web service is quite simple with the HTTP Endpoint usage. Imagine that you have the sp_GetOrders stored procedure, that retrieve from SQL Server the orders for a given period of time.
In order to expose it to external applications, you've to firstly create the HTTP Endpoint via T-SQL. The script you've to use is this:
create ENDPOINT SQL_Endpoint
STATE = STARTED
AS HTTP(
--Specify the Virtual Path used to get your method
PATH = '/sqlendpoint',
/*Authentication mode used to login through the Http Endpoint*/
AUTHENTICATION = (INTEGRATED) ,
/*HTTP Protocol in use. Specify SSL instead of CLEAR if you want
to enable HTTPS*/
PORTS = ( CLEAR ),
--Endpoint port number
CLEAR_PORT = 1024,
/*Server name */
SITE = 'localhost'
)
FOR SOAP(
/*Name of the exposed web method to create*/
WEBMETHOD 'GetOrders'
(
/*name of the Stored Procedure to expose
(MyDatabase is the database name)*/
name='MyDatabase.dbo.sp_GetOrders'
),
WSDL = DEFAULT,
--Include Schema in SOAP response
SCHEMA = STANDARD,
DATABASE = 'MyDatabase',
--Endpoint namespace
NAMESPACE = 'http://braintecture.org/'
);
GO
After the Endpoint creation, the external application can reach your newly created web method simply by adding a web reference to http://localhost/sqlendpoint?WSDL.
This is quite simple but sometimes I can see that the HTTP Endpoints usage is abused. When to use HTTP Endpoints and when to use ASP.NET Web Services in order to expose SQL Server data to extenal applications?
The first aspect to take in consideration for the choice is security: HTTP Endpoints must be used only on an intranet scenario because they expose your database directly to the external world. ASP.NET Web Services are the ideal choice if your application needs an internet access to the DB.
Another aspect to remember is that SQL Server 2005 uses the http.sys kernel driver in order to provide HTTP communication between external application and SQL Server and not IIS (as explained in this Microsoft chart):
The http.sys driver provides better performance compared to ASP.NET Web Services (that use IIS), but this is not the ideal solution if you want an application that can scale out a lot, because HTTP Endpoints use the database directly.
The last point to consider is the response time you need for your application: HTTP Endpoints are not so ready for mission-critical applications as ASP.NET Web Services (that can use ADO.NET with all its features for the database access).
My final personal recommendation is to always use ASP.NET Web Services if your application has a lots of concurrent user and you need access from the Internet. With ASP.NET Web Services you can check security aspects and query parameters (validation) prior to access the database. Use HTTP Endpoints if you have certain applications only for an intranet usage and that have the needs to temporarily retrieve some data from your database. Rememeber to disable the Endpoints when they're not in use (for security reasons).
Don't abuse of HTTP Endpoints please... ;)
Technorati Tag:
ASP.NET,
SQL Server