23. Access Microsoft SQL server from the Internet
Internet programming concepts
Use ISAPI to access SQL Server
Use ASP to access SQL Server
Use XML to access SQL Server
Summary
Using Microsoft SQL Server 2000 on the Microsoft Windows 2000 operating system platform allows us to easily publish SQL Server database to the Internet. The combination of Windows 2000 and Microsoft Internet Information Server (IIS) 5 provides all-encompassing components and interfaces for connecting and interacting with SQL Server 2000 databases. This chapter describes how to access Microsoft SQL server over the Internet. Since this book is not a special book on program development, it only provides an overview of several different access methods. There are many good books on the market for reference in the details of related programming.
This chapter begins with the concept of Internet programming and introduces the preset Internet programming interface in Windows 2000. Next, we will introduce the basic concepts of ODBC program design and ADO, as well as several methods for accessing SQL server, including ISAPI and ASP. Finally, we will discuss XML to understand what XML is and how it is used. This chapter covers many topics and is believed to help you understand the basic tools for Internet programming.
Internet programming concepts
This section describes how to connect SQL Server to Internet applications. These applications must contain two different interfaces to execute different tasks: one interface for the user and the other for the SQL server. This section describes how to connect an application to SQL Server, including IIS and ODBC.
Use Windows 2000 and IIS 5 as the Internet platform
Using Windows 2000 and IIS 5 as Internet application platforms, program developers can fully access various functions of SQL Server. Functions that developers can use include the server-side script language that integrates database access, and open database connectivity (ODBC) data Source, ole db (a powerful Component Object Model (Component Object Model, com) interface used to access universal data), and an Internet Server API (ISAPI) web application architecture, the last architecture is a strong competitor of traditional CGI (Common Gateway Interface, Common Gateway Interface) applications.
Use ODBC Data Source
For Microsoft Windows, ODBC is undoubtedly the best choice for database interfaces. Through ODBC, program developers can access a wide range of data sources, from the simplest plain text files to Microsoft Excel data tables, and even Microsoft Access and SQL Server databases. ODBC provides a general and powerful abstract level for database programmers.
Developing Internet applications using SQL Server does not require any special means. To access SQL Server through Web servers, ODBC data sources have provided the main methods. They can do this through an ole db com object set called ActiveX Data Objects (ADO. ADO provides an object-oriented-interface to access ODBC data sources, which is easier than using odbc c APIs. With ado, program developers can initialize some simple objects to represent the database online, commands (such as SQL statements or pre-stored programs), and recordsets ), it is similar to the Client Data Pointer and has the function of near-Server Database Data Pointer. All these database objects and interfaces make it easy for us to develop SQL server programs on the Internet, especially ODBC provides some powerful functions, for example, Connection pooling ).
One of the most important issues is how to properly utilize online sharing for an ODBC-based Web application. Online sharing allows a medium-level application to maintain and share online data with the SQL Server database. Shared connections will remain open for a specified period of time and can be shared with several users. Establishing online is usually a resource-intensive job that causes the burden on the database server. Because web servers and associated Internet applications manipulate the vast majority of traffic, online database sharing can minimize the number of online connections and (especially) reconstruction. In this way, the user's online time can be faster and the resource consumption of the database server is reduced. By default, IIS 5.0 dynamically allocates online database sharing.
________________________________________
Description
When using ODBC data sources and ADO, the ADO online object should be used based on each page. Compared with the entire working stage object, if the online object is created only when the page is required, and it is released immediately when it is not needed, the database can be released back to the shared status faster for other online users. This greatly reduces the time for Internet application users to wait online.
________________________________________
Select network link library
Although SQL Server supports several different network connection libraries, such as Named Pipes and Banyan vines, you should still use the TCP/IP network connection library when deploying an SQL Server database on the Internet. Among the many choices of the SQL server network connection library, TCP/IP can provide excellent network adaptability, online speed and performance. (The Network Connection Library is explained in chapter 11th .)
If TCP/IP is used as the network link library, the SQL server security mode is restricted to standard security only. This mode uses SQL Server login for verification. TCP/IP does not support integrated security. It uses a Windows 2000 account for verification, which can provide better security and more efficient methods to maintain the centralized management of accounts in the console of SQL Server. However, using integrated security degrades the system performance at some levels.
First, if integrated security is used, the effectiveness of online ODBC sharing is severely limited. For integrated security, each user who enters the database server through Web Server authentication must use a unique login. In this way, the benefits of online sharing are completely invalid because the shared connection opportunities are rejected. To connect online, you must use the same logon and password as the existing online connections.
Second, most of the online connections based on the Named Pipe Network Connection Library are used together with integrated security. Such connections are generally slow. Using Named Pipes also causes problems with online sharing. You can log on to an anonymous account with a valid SQL Server. In this way, you can use online sharing in the named pipe connection, however, most Web servers adopt either the Challenge and Response Verification Mode or the basic Verification Mode to forcibly connect to the SQL Server separately. The result is that it denies any performance improvements that online sharing can provide.
If SQL Server and web server are deployed on different machines, the choice of Authentication mode is limited when integrated security is used. The Challenge and Response Verification Mode will not be available. Only the Basic Authentication Mode and Anonymous Authentication mode can be selected. It is no wise to allow anonymous authentication by a security system over the Internet. Basic verification, as we have just said, will make online sharing unavailable.
Use ISAPI to access SQL Server
ISAPI is a collection of letters and calls that are designed to provide Internet application developers with a powerful tool that extends IIS functions. ISAPI applications provide superior performance and low-level control. In fact, ISAPI applications are more efficient on IIS than other web applications. In addition, through ISAPI, program developers can apply all the functions of Win32 API.
The dynamic link library (DLL) of an ISAPI application has two types: Extension and filter ). Since DDL is a typical compiled program code, the execution speed is much faster than the literal translation program code or script language. If your internet applications require high execution speed and scalability, the ISAPI expansion program and filter are the best choice even if it takes some time to design and modify the program. In the following sections, we will introduce the ISAPI expansion program and filter in detail, and then explain the design and use restrictions of ISAPI applications.
ISAPI expansion program
The execution method of the ISAPI extension program is the same as that of the DLL program. They will be loaded to the IIS processing space or another processing space separated from them. You can select the loading method when installing the ISAPI extension program on the Web server. If the application is not stable, the extended application should be loaded into separate processing space to avoid a wrong ISAPI Extended Program destroying the entire web server. This can happen if the ISAPI expansion program is still in the test phase or has not been fully tested.
To call an ISAPI extension, you can take advantage of the virtual path of. dll in the URL, such as the http://www.mydomain.com/SampleISAPI.dll.
When we call the ISAPI expansion program in this way, if it is not loaded, the ISAPI expansion program will be loaded into the memory and take control of the entire requirement, IIS becomes a pure data transmission medium between the server and the client browser. An example of this application is the custom search engine on the Internet platform.
ISAPI filter
The ISAPI filters are executed in the same way as DLL, but they are loaded to the IIS processing space when the web server is started and kept in the memory until the server stops. The ISAPI filter can be set to receive any number of filter Event Notifications, which appear When IIS processes each HTTP request and generates an HTTP response. When an ISAPI filter is loaded, the filter will communicate with IIS, and events that need to be notified of the filter will be transmitted through a special structure. When a similar event occurs, a notification message is sent to the event registration (registered) as the filter to be notified.
ISAPI is a powerful tool that can be used to perform compression or encryption, custom verification, web traffic logs and analysis, and even server-side scripting engine. You can create an ISAPI filter to check the web page settings sent to the client, search for special tags, and modify them to make them have a consistent appearance, just as an ASP Web page does.
The ISAPI filter can check every incoming and outgoing data stream, which makes it a useful and flexible tool. However, if too many ISAPI filters are used, the efficiency and scalability of the entire website will be completely reduced. For example, if a single input and output page needs to be filtered, the system may be severely burdened by excessive filter activity. When deciding what filters are to be executed, you should pay special attention to how to minimize their work. For example, in a custom encryption structure, the filter must be encrypted only when IIS needs to read data from the client or write data to the client. This reduces the amount of work that ISAPI filters should do.
ISAPI restrictions
Although the ISAPI technology can provide the best efficiency, it also has some disadvantages. Because most of the ISAPI expansion programs and filters use C or C ++, developing ISAPI expansion programs and filters is time-consuming and difficult to maintain than developing script web pages. When the ISAPI expansion program and filter need to be modified, the whole application needs to be re-compiled, not just replacing the file. Furthermore, even though ISAPI extended programs and filters generally run faster than server-side script programs similar to their functions, they cannot guarantee optimal scalability. Finally, some program design abstraction processes (which most server-side script developers think), especially session objects, are surprisingly absent in ISAPI programming. Compared with the program design of the server script, the ISAPI program design is quite low.
Use ASP to access SQL Server
ASP is a server-side script environment that developers can use to build interactive and dynamic web pages and develop powerful web applications. An ASP file is a plain text file with the extension. asp, which can contain any combination of texts, HTML labels, and server scripts. Although ASP is purely executed on the server side, program developers can still include client scripts in their HTML code and execute them through the user's browser rather than through the Web server.
When the Web server receives a requirement for the ASP file, the web server executes the server sript contained in the file with a special label and creates a webpage. The output result is a pure HTML file, which can contain the client sript component just mentioned. The file is then transmitted to the browser of the client, and then the Browser executes or displays these components on the screen. No ASP source code is sent to the client. Their analysis and execution are all completed on the server. Users do not see script commands for creating web pages. What they see is what these commands create.
In addition to server scripts, ASP files can also contain calls to COM components to execute many different tasks, including database access. In addition, business logic objects that span several different development environments can be executed.
ASP is quite neutral in language adoption. Program developers can use familiar script languages such as Microsoft Visual Basic Scripting Edition (VBScript), Microsoft JScript, or Perl to Develop ASP files. However, it is not difficult to develop files. ASP Web pages can use the script language supported by the scripting engine installed on the Web server and compatible with COM. The scripting engine is a program that can be used to process commands written in a specific language. IIS's Scripting Engine supports common VBScript (based on Visual Basic) and JScript (Microsoft version of European Computer Manufacturers Association, ECMA) 262 language specifications. Third-party vendors can obtain scripting engines in languages such as Perl.
ASP has some advantages over traditional CGI applications. As mentioned earlier, programmers who are familiar with VBSCRIPT or JScript do not need to learn new programming languages, such as C or Perl. ASP provides some objects for user's work stage, request and response processing, which makes ASP more powerful and makes personalized content development easier. In addition, compared to CGI applications developed and compiled using the C language, ASP can process and collect HTML form information with little time and program code, and store it in the database. Because all ASP program code is embedded in HTML files, ASP maintenance is quite easy.
Use XML to access SQL Server
XML is the abbreviation of extensible markup language, but XML is not actually a language. It is actually a system that can be used to define other languages and general syntax for expressing data structures. Different from the HTML markup language used only to specify the way web files are rendered, XML specifies the content and structure of the files. The so-called structured data refers to the data marked by its content or usage.
XML is inherently extensible. Program developers use XML to define data in web pages and define detailed levels as needed. For example, a program developer can use the <author> or <title> label to describe information about books and publishing. If you need additional definitions, program developers can add <retailprice>, <publisher>, or even <ISBN> labels. Using XML is similar to creating a data table in a database. It must determine the data elements required by the application, as if deciding which data rows are required to fully describe the data columns in the data table.
Because XML does not describe the rendering method, an XML file can be displayed in a variety of ways using different devices as long as it is written, including Web browsers, mobile phones, automotive displays, and so on. These devices may have their own display requirements: The computer screen may be 800x600 pixels, while a wireless Internet device may only support 200x200 pixels. Because XML only defines the structure and content of files, these devices can present files according to their specific display requirements as long as they use their integrated XML browsers. Unlike HTML files, XML files can be used for a longer time after being written, and will not be quickly eliminated due to new display technologies.
What makes XML really useful is that it can use the Document Object Model (DOM) to generate interactive functions. Dom is an interface used to define the mechanism for accessing data in a file. With Dom, developers can write dynamic content in a standardized manner. For example, a program developer can use Dom to make a specific part of the content generate behavior in some situations. Using this method can increase the effect of a file, for example, a certain part of the text-for example, the title of a volume marked as <title> in XML-changes the color when the user's mouse and cursor slide, indicates that this is a hyperlink pointing to an online bookstore. It is not easy to use the dedicated Dom and style sheet specifications to achieve this effect. However, the new Dom standard developed by W3C will help XML program developers maintain true platform independence.
XML soon became the standard for data exchange and file exchange. XML can be used to exchange data between different database systems on the Internet. For example, an automotive parts supplier can use XML to exchange inventory data with automakers, even if the two vendors may use two completely different database systems on two different platforms. Because XML describes the data structure, it can be a bridge between two systems that are both different and incompatible.
Summary
In this chapter, you have learned the basics of using SQL Server and IIS 5 to develop Internet applications on the Windows 2000 operating system platform. There are quite a few options for developing applications, including script environments such as ASP, or compiled program codes such as ISAPI extended programs and filters. Each option has its own advantages and disadvantages. When you select a tool to develop your large Internet applications, pay special attention to the transaction restrictions of each option to avoid future problems.
