Internet Programming Concepts | Microsoft SQL Server 2000 Administrators Companion

3 4

In this section, you will learn the basics of connecting Internet-enabled applications to SQL Server. These applications contain two different interfaces, both of which are necessary because they perform different tasks: they are the interface for the user and the interface for SQL Server. This section introduces you to the connection between the application and SQL Server and covers IIS and ODBC connection methods.

Using Windows 2000 and IIS 5 as an Internet Platform

By using Windows 2000 and IIS 5 as the platform for Internet applications, developers have unrivaled access to the features of SQL Server. Developers can take advantage of such features as server-side scripting with integrated database access, Open Database Connectivity (ODBC) data sources, OLE DB (a powerful set of Component Object Model [COM] interfaces for universal data access), and a Web application architecture known as Internet Server API (ISAPI) (an extremely strong competitor of traditional Common Gateway Interface [CGI] applications).

Using ODBC Data Sources

ODBC is undoubtedly the database interface of choice for the Microsoft Windows platform. Through ODBC, developers can access a wide variety of heterogeneous data sources, from simple text files to Microsoft Excel spreadsheets to Microsoft Access and SQL Server databases. ODBC provides a common yet powerful layer of abstraction for the database programmer.

Developing Internet applications using SQL Server is no exception. ODBC data sources provide the primary means of accessing SQL Server databases through Web servers. They do so through a set of OLE DB COM objects referred to as ActiveX Data Objects (ADO). ADO provides an object-oriented-interface access into ODBC data sources, which is an easier method than using the ODBC C API. With ADO, developers can instantiate simple objects representing database connections, commands (such as SQL statements or stored procedures), and recordsets, which are analogous to client-side cursors and which have much of the same functionality as server-side database cursors. All of these database objects and interfaces make Internet development using SQL Server almost trivial while providing some of the more powerful features available within ODBC, such as connection pooling.

The most important aspect of an ODBC-based Web application is the proper use of connection pooling. Connection pooling enables a middle-tiered application to maintain and share connections into SQL Server databases. Shared connections remain open for a specified duration and can be shared by users. Establishing connections is often a resource-intensive operation and can incur lots of overhead on the database server. Because Web servers and associated Internet applications handle a great deal of traffic, establishing and, more important, re-establishing connections should be minimized through database connection pooling. The results are quicker connection times for users and a reduction in the resource overhead on the database server. By default, IIS 5 activates database connection pooling.

NOTE
When using ODBC data sources and ADO, you should use ADO connection objects on a per-page basis. By creating connection objects only when the page needs them and releasing them when they are no longer needed, as opposed to using an entire user session object, the database connection can be released back to the pool more quickly for another connection to use. This will drastically reduce the amount of time users of your Internet application must wait for a connection.

Choosing a Network Library

Although SQL Server supports network libraries such as named pipes and Banyan Vines, you should use TCP/IP as your network library when deploying a SQL Server database on the Internet. TCP/IP provides network flexibility and the fastest connectivity and best performance of any network library option in SQL Server. (Network libraries are explained in Chapter 11.)

By using TCP/IP as your network library, you are limited to using standard security as your SQL Server security method. This method uses SQL Server logins for authentication. Using integrated security, which is not supported by TCP/IP and which uses Windows 2000 accounts for authentication, offers better security and is arguably the most efficient means of maintaining centralized account management in the enterprise. However, using integrated security can worsen performance in several ways.

First the effectiveness of ODBC connection pooling can be severely hampered if you use integrated security. When integrated security is implemented, each user authenticating through the Web server into the database server uses a unique login. The performance benefit provided by connection pooling is lost because for a pooled connection to be reused, a request for the connection must include a login and password identical to those used by the existing connection.

Second, connections based on the network library most commonly used with integrated security, named pipes, are generally slow. Using named pipes also poses problems for connection pooling. You can use connection pooling in conjunction with named pipes by mapping the Anonymous account to a valid SQL Server login, but most implementations use challenge/response or basic authentication from the Web server to enforce individual connections into SQL Server. This effectively negates any performance improvement connection pooling might have offered.

If SQL Server is installed on a machine other than the Web server, using integrated security also limits the available authentication options. Challenge/response authentication cannot be used, and the only available options are basic authentication and anonymous authentication. Allowing anonymous authentication in an otherwise secure system on the Internet is generally not advised, and, as mentioned, using basic authentication limits the benefits of connection pooling.