Create Interoperable Native Web Services
Yukon lets you take advantage of the new kernel-mode Http.sys listener and eliminates the need for IIS to process ASMX files or SQLXML 3.0 templates that implement Web services.
by Roger Jennings
August 26, 2005
Technology Toolbox: VB.NET, XML, SQL Server 2005 (June CTP or later), Windows Server 2003 or Windows XP SP2
SQL Server 2005 Developer Edition and higher add native XML Web services as a new feature that enables DBAs and .NET database developers to expose stored procedures, user-defined functions, and ad-hoc batch queries as SOAP 1.x Web methods. Native Web services substitute SOAP over HTTP endpoints to Windows' Http.sys kernel-mode HTTP listener for Internet Information Services' (IIS) virtual directories. SQL Server 2005 Express Edition doesn't support HTTP endpoints, so you must license the Workgroup Edition or better to enable these features for production. Only Windows XP Service Pack 2 (SP2) and Windows 2003 Server install Http.sys; thus, you can't deploy native Web services to Windows 2000 servers. On a more positive note, native Web services improve data security by eliminating the need for access to TCP port 1433, preventing anonymous connections to Web services and requiring Secure Sockets Layer (SSL) to encrypt clear-text login names and passwords.
The watchwords of SQL Server 2005's native Web services are interoperability and simplicity. The SQL Server team's design goal was twofold: Provide cross-platform data access that delivers parity with the Windows-only Tabular Data Stream (TDS) protocol, and move the SOAP stack from the IIS middle tier to the database engine's process (see Additional Resources). Replacing TDS with SOAP messages eliminates the requirement for JDBC, ODBC, or MDAC stacks on the client. Integrating the SOAP stack in the database engine's process creates a single data-access component for heterogeneous clients. You also can take advantage of C# or VB CLR stored procedures to customize the service's WSDL document for use by non-Microsoft SOAP toolkits, such as Borland's JBuilder 9.0 or webMethods' Glue 5.0.1. In this article, I'll show you how to create SOAP over HTTP endpoints with T-SQL batches, consume native Web services with a Windows client, alter the contents of auto-generated WSDL documents, and compare Web service performance with alternative SQL Server data-access methods. The VS 2005 NativeWebServices.sln VB.NET demonstration project automates most of these operations for the Northwind sample database's stored procedures (download the project here).
Native Web services have a feature set that's similar to the soap-typed virtual directory you create with SQLXML 3.0's graphical IIS Virtual Directory Management utility or its object model (see Additional Resources). SQLXML 3.0 lets you execute SQL Server 2000, MSDE, or any SQL Server 2005 edition's parameterized stored procedures to retrieve and update data, or submit parameter values to T-SQL template queries. SQLXML and IIS 5+ manage default database, security, and virtual directory assignment, and they support anonymous, integrated Windows, basic, or digest authentication. You can designate a single Windows user account or a login ID and password for all SQLXML 3.0 clients' database access. Typing http://ServerName/DatabaseVDir/SoapVName?wsdl in VS 200x's Add Web Reference dialog's URL box generates the Web service proxy class from the dynamic WSDL document. Most other vendors' Web service client toolkits can't create proxy classes directly from SQLXML 3.0 WSDL documents.
SQL Server 2005's native Web services enable declarative Web service deployment and offer greater implementation flexibility than SQLXML 3.0. You add an HTTP endpoint for SOAP data access with a two-part T-SQL declaration. The first CREATE ENDPOINT statement part supplies HTTP transport parameters:
Back to top