• Illustration: Supporting XML messaging with SQL Server 2000
• Illustration: The SQL Server 2000 Web Architecture
• Sidebar: New Document Storage and Indexing Features
• Sidebar: XML Concepts and Technologies
• Sidebar: Other SQL Server 2000 Development Features

New Web access and Extensible Markup Language (XML) features put SQL Server 2000 at the center of Microsoft’s Next Generation Web Services (NGWS) application architecture. These features will enable new Web and e-commerce applications in which the database server does much of the grunt work for which developers currently write code. The features could change the basic architecture of Web sites, shifting the focus from the Web server to the database server and the applications built on top of it. Organizations waiting for NGWS can use Beta 2 of SQL Server 2000 to understand Microsoft’s future development and services platform.

(This is the first in a series of articles on SQL Server 2000. Future articles will cover the most notable new enterprise features, including better multiprocessor and cluster performance, management of very large databases, and "data mining" for online analytic processing.)

Overview and Goals

SQL Server 2000 provides XML and Web support through both the database server itself and middle-tier components that can run on the Internet Information Server (IIS) Web server. The database server can both build XML documents and extract information from them. The middle-tier components let Web browsers and applications send queries to the database server, and get XML or HTML documents in response, using the standard Hypertext Transfer Protocol (HTTP) that powers the Web.

These new features are aimed squarely at supporting the next generation of Microsoft's Distributed interNet Architecture (DNA) application architecture. Microsoft is integrating the disparate products and tools that make up DNA into a new end-to-end platform for writing Web applications. The full details won't appear until June 2000, when Microsoft announces the hitherto mysterious "Next Generation Windows Services." However, one part is already clear: DNA applications will communicate using messages encoded in XML and sent over standard Internet protocols. Microsoft is already moving ahead with support for XML messaging in a couple of areas:

Business-to-business (B2B) commerce. Microsoft's BizTalk project is developing XML message standards for sending business documents (such as purchase orders, invoices, and product catalogs) between companies over the Internet. Its BizTalk Server product, currently in beta, will route these messages and bridge the gap between XML formats and traditional Electronic Data Interchange (EDI).

Megaservices. Microsoft has announced plans to create "megaservices," Internet-based applications that offer services to Web sites. Examples include Passport, an electronic wallet and user profile service, and DealerPoint, a customer relationship management service for car retailers. In Microsoft's vision, Web sites will communicate with Microsoft's megaservices mainly through XML messages sent over HTTP.

The plans for XML messaging don't stop with B2B and megaservices, however. Microsoft expects XML messages to tie together all Windows applications, including its own. Its ubiquitous application-to-application communication mechanism, COM, will eventually become an API for sending and receiving XML messages.

SQL Server 2000's new features position it as a Web-accessible storage service for XML documents in general and for XML-formatted messages in particular (see "Supporting XML messaging with SQL Server 2000" illustration). Applications can use SQL Server to store XML messages, generate XML messages from database data, accept and retrieve XML messages over HTTP, and perform database updates based on the content of XML messages.

Beyond its role in Microsoft's Web universe, SQL Server 2000's XML features position it as the foundation for new types of applications.

XML Web sites. Developers and the World Wide Web Consortium (W3C) have begun promoting XML as a way to format Web site content. XML and related style sheet standards enable sites to separate Web site content from the way it's presented on the screen. This in turn makes it easier to build sites that support many different ways of viewing content—for example, sites such as Yahoo.com may be viewed on either a PC monitor or the small screen of a wireless phone. SQL Server 2000’s XML and Web-enabled features could replace Microsoft's Active Server Pages (ASP) technology and markedly reduce the amount of code required to get Web content into and out of the database.

Knowledge management. Microsoft is devoting increasing attention to applications that capture a company’s proprietary knowledge and store it in an information system so that it can be retrieved whenever and wherever needed. Examples include Microsoft's own Knowledge Base, which captures and indexes documents that describe bug fixes and other procedures developed by Microsoft support personnel. SQL Server 2000’s Web-addressable storage service can combine unstructured documents (e.g., Office files), XML documents, and traditional database records. Microsoft has been promoting the upcoming Exchange 2000 and its "Web Store" features for a leading role in knowledge management applications, but SQL Server 2000 is a highly capable understudy (see "New Document Storage and Indexing Features" sidebar).

Web Access and XML Features

SQL Server 2000 relies on three major components to provide Web access and XML processing features (see "The SQL Server 2000 Web Architecture" illustration). The first component provides Web and HTTP services, the second component manages database queries and responses, and the third component is the database server itself, which applications access by using the first two components. (All of the components depend on emerging XML technologies, some standard, some Microsoft-specific; see "XML Concepts and Technologies" sidebar for a brief overview.)

The SQL Web Component

SQL Server 2000 gets its connection to the Web from a SQL Internet Server API (ISAPI) component that hooks it to IIS. This component enables an administrator to set up a "virtual root" on an IIS Web site, which corresponds to a particular SQL Server database. When a browser or other Web client requests a URL within this virtual server environment, the SQL Web component transfers the query to the database. (The queries are actually handled by the data access libraries, described below.) The query can include formatting parameters that control how the query results get converted into an HTML or XML document that the client sees.

The Web component can accept two types of requests from clients:

URL-embedded queries. Clients can embed database queries directly in URLs. URL-embedded queries might be useful in applications that don't need much database security (e.g., some intranet applications). They might also prove helpful for debugging—a developer can type a query URL into a browser and get back a response. However, most production sites will configure the SQL Web component to refuse URL-embedded queries; few will want to let Web site visitors run arbitrary queries (including updates and deletes) against their databases.

Template files. A client can also issue queries by navigating to template files on a Web site. A template is an XML document that contains embedded database queries. When the user requests a template file, the SQL Web component hands the entire template in the file off to the database, which returns an XML or HTML document based on the template. Template files are thus similar to ASPs, except that templates contain XML interspersed with queries, whereas ASPs can contain HTML interspersed with code in a scripting language such as Visual Basic.

The SQL Web component enables developers to create simple Web applications with relatively little code compared with ASPs. For example, a developer could create a template that lets customers check the status of their orders at an e-commerce Web site. The template contains a query for order status by customer, an XML header and footer, and a link to a style sheet for formatting the results. The component connects to the database, builds a query string, and writes the resulting HTML into the response—all tasks for which an ASP programmer might have to write code.

The SQL Web component does not offer the full power of a scripting language as ASPs do, although it can execute any database operation (including updates and stored procedures). Fortunately, developers don't have to abandon ASPs to get at SQL Server's features for generating XML and HTML documents. They can get these features the same way the Web component does—via Microsoft's new Web-enabled data access libraries.

Data Access Components

SQL Server 2000 delivers new versions of Microsoft's data access components that can accept queries and formatting instructions, and return HTML, XML, and other Web-friendly formats to applications. The data access components are libraries that enable applications to retrieve and change data in databases and a variety of other data stores (e.g., the Exchange message store). ASPs and Visual Basic applications tap the new capabilities of these components through the ActiveX Data Objects (ADO) API. Other applications can use ADO or bypass it and go directly to the SQL Server OLE DB provider component that actually implements all these capabilities.

The data access components provide the following Web and XML features:

Generating XML and HTML documents. The data access components can accept queries in templates (described above) and return an XML or HTML document. Queries can include a style sheet to format the result document.

Giving XML tools database access. The data access components can accept queries to SQL Server databases in the XPath query language. This feature enables XML document-processing tools to directly manipulate data stored in SQL Server, essentially treating databases as virtual XML documents. For example, Microsoft's BizTalk Server includes a tool for converting XML business documents to EDI formats; an application could use this tool and the XPath feature to generate EDI purchase orders directly from data stored in a procurement database.

Formatting large text and image objects for the Web. The data access components can return large text or image objects (also known as BLOBs—binary large objects) in a byte-stream format that's ready for transmission over the Web. This will make it easier for Web applications (including the SQL Web component) to keep such content in the database.

Database Server

The SQL Server database server itself implements a variety of useful features for generating and processing XML, and for storing and retrieving documents in general. These include the following:

XML query results. SQL Server 2000 queries can include a FOR XML clause to return results as XML documents, rather than in the usual (proprietary) tabular data stream. For example, a query for all items bought on purchase orders could return an XML document with a top-level element for each purchase order, and a nested sequence of second-level elements for each item.

XML document processing. SQL Server 2000 supports an OPENXML clause in queries that extracts information contained in XML documents. This feature can make it easier to write applications that pull information from XML documents and store it in database tables, or that compare data in XML documents with other data in database tables. On receiving an XML-formatted purchase order, for example, an application could extract data such as customer identity and quantity and write them into a row in the database. More generally, the feature could make it easier to build knowledge management applications that log and retrieve structured documents, such as engineering change management and bug-tracking systems.

Improved document storage and indexing. Finally, SQL Server adds new features for storing and indexing long text documents and images (see "New Document Storage and Indexing Features" sidebar). These will prove particularly useful for knowledge management applications and for Web applications that use SQL Server for quick access to content.

Status and Resources

Beta 2, the first public beta of SQL Server 2000, is also reportedly its last. Microsoft said SQL Server 2000 would ship no later than Aug. 1 when it submitted a batch of benchmark results to the Transaction Processing Council site in Feb. 2000. The product appears to be on track for that date. However, not all of the XML features in this beta will make the final release. For example, Microsoft has said that it will ship later an "updategrams" feature (for updating databases) and a utility for bulk import/export of XML data.

An online presentation covering SQL Server 2000's XML and Web access features appears at http://support.microsoft.com/servicedesks/webcasts/wc042800/wcblurb042800.asp.

A Web developer's overview of SQL Server 2000's XML and Web access features appeared in the Mar. 2000 MSDN magazine, online at http://msdn.microsoft.com/msdnmag/issues/0300/sql/sql.asp. The article is based on a Mar. 2000 "XML Technology Preview" add-on for SQL Server 7.0, but still provides useful code examples. The Technology Preview is still available as of this writing, at http://msdn.microsoft.com/workshop/xml/articles/xmlsql/sqlxml_prev.asp.

To order SQL Server 2000 Beta 2, see www.microsoft.com/sql/productinfo/sql2kord.htm. The CD-ROM costs US$9.95 in the United States and Canada, US$14.95 elsewhere. MSDN Universal and Professional subscribers can also download the beta from the subscriber downloads site.

A reviewers’ guide summarizing all new features of SQL Server 2000 appears at http://www.microsoft.com/sql/productinfo/sql2krev.htm.

Microsoft's developer resources for XML are at http://msdn.microsoft.com/xml/default.asp.