These days, you should hardly be surprised when you run into a
business requirement to make data available over the Web. Fortunately,
database products are making it easier and easier to hook up Internet
protocols directly to your data. Microsoft SQL Server is a case in
point. While SQL Server 2000 did allow Web data access, the process
had a certain Rube Goldberg feel to it, requiring the use of the
SQLXML library and a full installation of Internet Information
Services. With SQL Server 2005, Microsoft has revisited its database
Web access story, and the story is much improved. Now, you can have
direct access to your SQL Server data from any HTTP SOAP client
without any extra middleware at all – not even IIS. In this article,
I’ll show you how it works.
SQL Server Data as a Web Service
Web Services may not be getting quite the flurry of publicity that they
were seeing for a few years, but they’ve developed into an essential part
of connectivity for many applications. Microsoft continues to bet heavily
on Web Services as a backbone for service-oriented architectures, so it
makes sense that they’ve chosen to expose SQL Server 2005 data via a Web
Service. Or, to be more precise, you can create as many Native XML Web
Services as you like in SQL Server 2005.
To do so, you use the new CREATE ENDPOINT statement to create HTTP
endpoints. Each HTTP endpoint ties the results of a SQL Server stored
procedure directly to a Web Service, providing support through the Web
Services Description Language (WSDL) and Simple Object Access Protocol
(SOAP) protocols for retrieving the data supplied by the stored procedure.
SQL Server interfaces directly with the Windows HTTP listener process
(http.sys) so that SOAP requests are routed directly to SQL Server, with
no intervening middleware necessary. Similarly, SOAP responses are
sent directly back to the requesting client.
That’s the theory. Let’s look at the practice.
Creating an HTTP Endpoint
To get started, I’ll need a stored procedure that returns some data. As
an example, I’ll use a simple stored procedure that returns the contents
of two fields from the Sales.Store table in the AdventureWorks sample
database that ships with SQL Server 2005:
CREATE PROC dbo.SalesStoreProc AS SELECT CustomerID, Name FROM Sales.Store
The next step is to create the HTTP endpoint. Running this SQL
statement is what makes the data from the stored procedure available to
CREATE ENDPOINT GetStores STATE = STARTED AS HTTP ( PATH = '/Store', AUTHENTICATION = (INTEGRATED), PORTS = (CLEAR), SITE = 'localhost' ) FOR SOAP ( WEBMETHOD 'StoreList' (NAME='AdventureWorks.dbo.SalesStoreProc'), BATCHES = DISABLED, WSDL = DEFAULT, DATABASE = 'AdventureWorks', NAMESPACE = 'http://AdventureWorks/Store' ) GO
As you can see, there are quite a few clauses to the
CREATE ENDPOINT statement. Here’s a quick rundown to get you
STATEclause specifies the initial state of the
can be started, stopped (listening but returning errors to clients) or
disabled (not even listening for requests)
AS HTTPclause specifies the transport protocol to
use. You can also specify
PATHclause specifies the URL on the server that
clients will use to reach this Web service.
AUTHENTICATIONclause specifies how clients will
themselves to the SQL Server:
PORTSclause specifies whether the service will
listen on the
SSLports, or both (other
clauses, not shown here, let you specify non-standard port numbers)
SITEclause lets you specify a hostname for the
computer that will respond to requests.
FOR SOAPclause states that this endpoint will
respond to SOAP messages. Other endpoints handle messages for Service
Broker or database mirroring.
WEBMETHODclause defines a Web method, mapping a
method name to the name of a stored procedure
BATCHESclause specifies that this endpoint won’t
process arbitrary SQL statements.
WSDLclause specifies that it will provide WSDL
DATABASEclause specifies the database that
contains the data.
NAMESPACEclause specifies the XML namespace for
Testing the Web Service
There are many tools that will let you test a Web Service that uses
SOAP for communication – that’s one of the advantages to using a standard
protocol. I’ve chosen to use Altova’s XMLSPY for testing, because it lets me
see the exact SOAP request and response messages.
To generate the SOAP request, I selected Create New Soap Request from
the SOAP menu. This opens the WSDL File Location dialog box, shown in
The URL for the WSDL file (http://localhost/Store?wsdl) is determined
CREATE ENDPOINT statement. I’m working on the same
computer where SQL Server 2005 is running, so the Web server itself is
PATH clause dictates the Store portion of the
URL. The remainder of the URL is where SQL Server listens, by convention,
for WSDL requests associated with this particular Web Service.
XMLSPY retries the generated WSDL file and parses it for a list of
operations. In this case, there’s only the single StoreList operation that
was defined by the
WEBMETHOD clause in the
CREATE ENDPOINT statement. When I select that operation,
XMLSPY uses the information in the WSDL file to generate a SOAP request
for the information supplied by that operation:
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns_xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns_xsd="http://www.w3.org/2001/XMLSchema"> <SOAP-ENV:Body> <m:StoreList xmlns_m="http://AdventureWorks/Store"/> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
Then it’s simply a matter of selecting Send Request to Server from the
SOAP menu in XMLSPY to send this SOAP Request to the HTTP endpoint. SQL
Server receives the request, processes it, and sends back a SOAP response
containing the requested data. The whole response runs to about 2800 lines
(the stored procedure returns over 700 rows of data); here’s an
<?xml version="1.0" encoding="utf-8"?> <SOAP-ENV:Envelope xml_space="preserve" xmlns_xsd="http://www.w3.org/2001/XMLSchema" [additional namespace declarations omitted] xmlns_method="http://AdventureWorks/Store"> <SOAP-ENV:Body> <method:StoreListResponse> <method:StoreListResult xmlns=""> <sqlresultstream:SqlRowSet xsi_type="sqlsoaptypes:SqlRowSet"> <xsd:schema xmlns_xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"> [schema details omitted] </xsd:schema> <diffgr:diffgram xmlns_diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"> <SqlRowSet1 xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1"> <row> <CustomerID>1</CustomerID> <Name>A Bike Store</Name> </row> <row> <CustomerID>2</CustomerID> <Name>Progressive Sports</Name> </row> <row> <CustomerID>3</CustomerID> <Name>Advanced Bike Components</Name> </row> [remaining data omitted] </SqlRowSet1> </diffgr:diffgram> </sqlresultstream:SqlRowSet> <sqlresultstream:SqlRowCount xsi_type="sqlrowcount:SqlRowCount"> <sqlrowcount:Count>701</sqlrowcount:Count> </sqlresultstream:SqlRowCount> <sqlresultstream:SqlResultCode xsi_type="sqlsoaptypes:SqlResultCode">0</sqlresultstream:SqlResultCode> </method:StoreListResult> </method:StoreListResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
And really, that’s all there is to it: run one T-SQL statement, and SQL Server will
accept, and respond to, standard HTTP SOAP 1.2 requests. This gives you a supremely easy
way to hook SQL Server 2005 data into any sort of service-oriented architecture that
you can dream up.
Some Final Thoughts
When you find a new tool, the temptation is always to start using it in all of your
development. But before you rush to hook up native XML Web Service for all your SQL
Server data needs, you should consider whether it’s really the appropriate solution.
In the case of this particular technique, there’s a pretty clear tradeoff to keep in
mind. On the one hand, delivering SQL Server data wrapped up as native SOAP messages means
that it’s incredibly accessible: you can extend the reach of your data in many
cross-platform, cross-language scenarios where it was formerly unavailable. XML and SOAP
parsing libraries are ubiquitous, so data delivered in this format can be consumed
from just about any software you can imagine. Remember, too, that the delivery mechanism
is pure HTTP, which means that you can chip this data anywhere the Internet reaches.
But on the downside, SOAP is necessarily a “fat” format, being text-based (as opposed
to the native binary SQL Server Tabular Data Stream, TDS, format). You can expect it to take
20-30% longer to ship data from point A to point B using this technique than if you were
building an application that used traditional SQL Server client libraries. If your application
uses Windows clients and LAN connectivity, there’s little or no point to incurring that
level of overhead just to be buzzword-compliant. Keep the Web Service access for times when
you need to integrate with service-oriented architectures, and you’ll find this to be a
useful and powerful tool.
About the Author
Mike Gunderloy is the author of over 20 books and numerous articles on
development topics, and the lead developer for Larkware. Check out his latest books, Coder to Developer and Developer to Designer,
both from Sybex. When
he’s not writing code, Mike putters in the garden on his farm in eastern