What is SQLDataPipe?

SQLDataPipe is a program module that runs on the Internet Information Server used to speed up data delivery, response time, and simplify Internet programming.

Web Application Performance Issues

Web enabled applications have been gaining popularity as a platform for deploying systems across multiple organizations or via a wide area network. A web-enabled application does not require any distribution of software to the client. The browser is used to view data and perform data entry. These types of applications require no special data communication platform because they use the standard Internet http protocol.

Building a web-enabled solution is quite challenging because the relationship between functionality, performance and manageability is different from that of traditional client/server application design. Furthermore, the response time is a function of the server side program architecture.

Application developers need to appreciate the subtle difference between server side performance and page delivery speed. Most architectural deployment is designed to optimize the number of pages delivered by the application and pay very little attention to the speed of building and delivering pages. Response time depends on how quickly a page can be dispatched from the server.

Two Tiers with Microsoft Internet Information Server™ (IIS)

Early database-backed websites were designed where the user interface is written in Microsoft Active Server Pages™ (ASP) format or Microsoft ISAPI™ and the IIS would provide the pooling and the connection to the Microsoft SQL Server™. The IIS took care of ensuring efficiency to the SQL Server and the programmer did not need to be concerned with that. This architecture demanded that all business rules and connectivity to the SQL database be implemented in the ASP and ISAPI programs, which is more suitable for programming the presentation of a page. Program manageability became very awkward.

Figure 1. Two Tiered Architecture. IIS provides the pooling, ADO connection and multithreading to the SQL Server.

Three Tiered Microsoft Architecture

Microsoft then introduced the 3-tiered web application model, where the presentation of pages uses the help of business modules and a data module, implemented as Dynamic Link Library (DLL) programs. Most sites nowadays use Visual Basic™ (VB) to implement these DLLs. Now with this architecture, IIS’ automatic configuration cannot be used. Having a DLL as the layer between the web pages and the database, the user lost IIS’ help in managing the connectivity to the database, and has to concern him/herself with such issues. The result is commonly a considerable increase in waiting time for data to arrive from the SQL Server. Because of this wait, the IIS running this configuration attempts to generate more pages, as the current page is still awaiting data from the database (the CPU of the IIS machine is idle). This increases the number of pages returned, although the wait for each of these pages is increased, having the illusion of increased performance. With this configuration, the SQL Server is working at maximum capacity because of the lack of tuning and efficient connection, which takes a toll on data delivery. Because the SQL Server uses more CPU cycles per unit of data with this configuration, this reduces the number of IIS machines that could share this database server, making the setup less scalable.

Figure 2. Three Tiered Architecture. Pooling, ADO connection and multithreading must be programmed by the web developer in the Data Connectivity module.

Some programmers realized this deterioration in speed and attempted to reprogram the data modules in a faster programming language such as C++, but there are many modules, and to convert all these DLLs and retest them is not worth the effort, and is virtually impossible for a production system. So the attempts were very limited and people have settled to accept the slow response time.

Three Tiers with SQLDataPipe

At Doxess, we realized that the minute you left the two-tiered architecture, you couldn’t use the IIS capabilities, and had to create your own. At the same time, we realized that a lot of companies would not have the skill required to program and fine-tune an efficient connection to the SQL Server. So we created SQLDataPipe. This is a DLL programmed to optimize the connectivity to the SQL Server. This DLL can be called directly from an IIS or ISAPI program, or alternatively by a business module if necessary. It wraps the SQL connection. Using this will free the programmer from dealing with creating an efficient connection to SQL Server without IIS. In addition, we have reduced the need for the business module because we implemented much of that in the database itself. This helps in reducing the overhead at the IIS machine, which has to connect to two modules at the same time. We have established that if performance and speed are top priority, business rules should be implemented at the database level. Because the IIS machine now does not wait very long for data coming from the SQL Server, pages are delivered much faster to the end user, resulting in a dramatic improvement in response time. Because the connectivity to the SQL Server is very efficient, this machine uses fewer CPU cycles per unit of data compared to the previously discussed architecture (three tiered with DLLs). And as a result, one SQL Server could support a higher number of IIS machines on the system.

Figure 3. Three Tiered Architecture with Doxess SQLDataPipe. Pooling, multiple ADO connection and multithreading are provided by SQLDataPipe.

SQLDataPipe Client Gateway

Currently much of the web-enabled applications are built around the browser. The common problem is that the browser cannot read data available on the client’s machine or the Local Area Network (LAN). Adopting a web system raises the need to get into your own existing application and retype data into the web application. We envision that future web-based applications will require one Windows application running on a LAN that allows a client to connect to a remote SQL database through a web service. A web service running on the IIS machine will talk to a Windows application via software that extends the SQLDataPipe across the Internet to the client. The protocol of the connection will be exactly the same as that used by the browser (http or https).

Summary of Features and Benefits of SQLDataPipe

SQLDataPipe contributes the following when used in a web application:

§ Dramatic improvement in response time

§ Reduced number of CPU cycles per unit of data on SQL Server

§ Simplified programming of ASP or ISAPI user interface and increase in programming standards

§ Supports scalable architecture

Because SQLDataPipe is available all the time, there is no down time in development. No need for shutdown of services on development machines that may have been necessary when compiling DLLs. Clients have the capability of testing programs on the same machine while developers are working on it.

SQLDataPipe is configured to be fail-safe. It is designed to load on start and rebuild the connection dynamically. For instance, if an IIS machine went down and came back up again, SQLDataPipe will reload itself dynamically and reestablish connectivity to multiple SQL databases as configured earlier. Moreover, it will also reconnect itself to SQL Server if that shuts down and restarts. So in an environment with multiple IIS machines and multiple databases, SQLDataPipe provides maximum availability automatically with no manual interference to support it. SQLDataPipe can work with Microsoft SQL Server, Oracle and DB2.

Three Tiered Windows 32 Applications

SQLDataPipe is not limited to the web applications running on the IIS. It can run on any server to provide efficient 3-tiered architecture for Win32 client/server applications. Win32 programs running on client machines may connect to SQLDataPipe as a Distributed Component Object Model (DCOM) on a LAN, or as an http service over the Internet or Intranet to achieve an efficient three-tiered client/server system.

Licensing

SQLDataPipe requires one license per server running it. One license on the server can support an unlimited number of connections to SQL Servers.