Accessing SQL 2000 via HTTP
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
In the first three chapters, we covered the XML specification, Extensible Stylesheet Language Transformations (XSLT), and the necessary setup steps for virtual roots on SQL Server. In this chapter, we'll first discuss client/server architecture to give you a feel for how the different system components, application servers, database servers, and so on interact with each other. Then we'll look at how to utilize the HTTP protocol in various ways to execute SQL statements against SQL Server. This includes the use of template files to generate XML data. Utilizing the HTTP protocol via URLs will simplify our tasks because most people in the computer industry are very familiar with this process.
This chapter will cover the following topics:
General client/server architecture in two-, three-, and n-tiered configurations
SQL Server 2000's HTTP capabilities
Entities in XML and URLs
Generating XML documents by querying SQL Server via HTTP
Generating XML documents utilizing XML template files
Generating XML documents utilizing stored procedures
I think it's about time to define this template file that we've been talking about. It's not some new language you'll have to learn, so you can relax. It also has nothing to do with the template elements of XSLT that we learned about in Chapter 2, "XSLT Stylesheets." Simply put, these templates are just XML files that contain one or more SQL statements. When these templates are applied to a database through mechanisms you'll learn about in this chapter, they help produce results in XML format.
Let's take one last look at the XML process diagram that we used in Chapter 1, "Database XML," and Chapter 2 (see Figure 4.1).
){kind=link}
Figure 4.1 The XML process.
Yes, that's right; this is the last look. We've covered all the components in the diagram and will remain at the XML engine component for the rest of this book. The XML engine is SQL Server 2000.
Two-, Three-, and N-Tiered Architectures
To get a better understanding of how client/server components function together and therefore a better understanding of system extensibility and capability, we need to have a brief discussion of client/server architecture.
Each component of a client/server system performs one or more specific logical functions. We'll begin this discussion by defining these functions, and then we'll show you how they are distributed throughout a client/server system. These four functions are the basic building blocks of any application:
Data storage logic. Most applications need to store data, whether it's a small memo file or a large database. This covers such topics as input/output (I/O) and data validation.
Data access logic. This is the processing required to access stored data, which is usually in the form of SQL queries (whether they are issued from the command line of Query Analyzer or from a stored procedure).
Application logic. This is the application itself, which can be simple or complex. Application logic is also referred to as a company's business rules.
Presentation logic. This is the projection of information to the user and the acceptance of the user's input.
With these different functional processes now defined, we can look at how different client/server configurations split these functions up and what effect they have on extensibility. We'll begin with two-tier architecture, followed by three-tier and then n-tier architecture. Finally, we'll talk briefly about a sample SQL Server and IIS configuration.
Two-Tier Client/Server Architecture
In its simplest form, client/server architecture is a two-tier structure consisting of, believe it or not, a client component and a server component. A static Web site is a good two-tier example (see Figure 4.2).
){kind=link}
Figure 4.2 Two-tier client/server architecture.
In this configuration, the client accepts user requests and performs the application logic that produces database requests and transmits them to the server. The server accepts the requests, performs the data access logic, and transmits the results to the client. The client accepts the results and presents them to the end user.
Three-Tier Client/Server Architecture
The next step up is three-tier architecture. Take a look at Figure 4.3.
){kind=link}
Figure 4.3 Three-tier client/server architecture.
This design utilizes three different focus points. In this case, the client is responsible for presentation logic, an application server is accountable for application logic, and a separate database server is responsible for data access logic and data storage. Many large-scale Web sites with database servers separate from Web servers are examples of this.
N-Tier Client/Server Architecture
Last but not least, there is the n-tier client/server architecture. This configuration is basically open ended (see Figure 4.4).
){kind=link}
Figure 4.4 N-tier client/server architecture.
In this figure, there are more than three focus points. The client is responsible for presentation logic, a database server(s) is responsible for data access logic and data storage, and application logic is spread across two or more different sets of servers. In our example, one of the application servers is a Web server, and the other is a non-Web server. This isn't required for the architecture. Any combination of two or more types of application servers is all right.
The primary advantage of n-tiered client/server architecture, as compared to a two-tiered architecture (or a three-tiered to a two-tiered), is that it prepares an application for load balancing, distributing the processing among multiple servers. Also, a proper n-tier setup enables better integration with other components and ease of development, testing, and management.
Typical Microsoft Three-Tier Architecture for IIS and SQL 2000
Looking at Figure 4.5, you'll see that I have diagrammed it in a slightly different way to illustrate some important points. At first glance, you'll see that it represents three-tier client/server architecture. Nothing is new here; the client is responsible for presentation logic, a SQL Server 2000 server(s) is responsible for data access logic and data storage, and IIS contains the application logic. It's the internal workings of IIS in this instance that I want to explain a little more deeply.
){kind=link}
Figure 4.5 Typical Microsoft client/server architecture.
When a URL-type query is passed to IIS, it examines the virtual root contained in the URL and ensures that the SQLISAPI.DLL has been registered for this virtual root. This should have been done when the virtual root was configured via one of the two configuration methods covered in Chapter 3, "Internet Information Server and Virtual Directories."
SQLISAPI.DLL, in conjunction with other DLLs, establishes a connection with the SQL Server identified in the virtual root. After the connection is established and it is determined that the command in the URL is an XML command, the command is passed to SQLXMLX.DLL. SQLXMLX.DLL executes the command, and the results are returned. All XML functionality is contained in SQLXMLX.DLL.
If you take into account what we have just discussed and look back to Chapter 3's section "The Advanced Tab," you'll see why the configuration of the Advanced tab is so important. If the SQLISAPI.DLL file can't be found, nothing works.
As you can see in Figure 4.5, template files, schema files, and XSLT stylesheets all reside on the IIS server.