Christopher Moffatt
Technical Note
Volume 4, Number 5
February 1994
This technical note describes the architecture that enables Microsoft® SQL Server™ and related products to run efficiently in nearly all network environments. It first describes the basic network integration architecture and then discusses how each of the following products uses this architecture to integrate into complex heterogeneous environments:
For additional technical information about the products discussed in this technical note, refer to the following:
Microsoft SQL Server Net-Library® architecture for client-server applications is based on the Net-Library concept that abstracts the client and server applications from the underlying network protocols being used. Figure 1 shows how SQL Server and related products can be accessed from practically any network environment.
Figure 1. Net-Library architecture
Tabular Data Stream (TDS) is the data stream protocol used by Microsoft SQL Server, Open Data Services, and SYBASE® software to transfer requests and responses between the client and the server. TDS is a logical data stream protocol and must be supported by a physical network interprocess communication mechanism (IPC). The Net-Library architecture provides a method of sending TDS across a physical network connection, as well as a transparent interface to the DB-Library application programming interface (API) and the SQL Server driver for ODBC.
Net-Libraries are linked dynamically at run time. With the Microsoft Windows NT®, Windows®, and OS/2® operating systems, Net-Libraries are implemented as dynamic-link libraries (DLLs), and multiple Net-Libraries can be loaded simultaneously. With the MS-DOS® operating system, Net-Libraries are implemented as terminate-and-stay-resident (TSR), and only one can be loaded at any given time. The Net-Library architecture can be divided into two components, client-side Net-Libraries and server-side Net-Libraries.
Figure 2. Client-side Net-Library architecture
When a call is made to open a connection to SQL Server, the API involved (DB-Library or the SQL Server driver for ODBC) determines which client-side Net-Library should be loaded to communicate with SQL Server or Open Data Services. As mentioned earlier, with the Windows NT, Windows, and OS/2 operating systems, multiple client-side Net-Libraries can be loaded simultaneously.
Server-side Net-Libraries were first introduced with Microsoft SQL Bridge for OS/2, and continue to be used in Microsoft SQL Bridge for Windows NT. They are also used by Network Manager in the Microsoft SQL Server Network Integration Kit for Novell® NetWare® Networks and in the Microsoft SQL Server Network Integration Kit for Banyan® VINES® Networks. Open Data Services on the Windows NT platform uses this architecture.
When a server-side Net-Library is loaded by an application (Microsoft SQL Bridge, Network Manager, or Open Data Services for the Windows NT operating system), it implements a network-specific way of establishing communication with clients and, in some cases, registers its presence on the network.
Figure 3 illustrates the integration of server-side Net-Libraries with the various SQL Server–based products on the Windows NT platform.
Figure 3. Server-side Net-Library architecture on the Windows NT platform
Note SQL Server on the Windows NT platform is implemented as an Open Data Services application.
Figure 4 shows the integration of server-side Net-Libraries with the various SQL Server–based products on the OS/2 operating system.
Figure 4. Server-side Net-Library architecture on the OS/2 platform
The following components of SQL Server use named pipes with the OS/2 platform. With the Windows NT platform, they use the DB-Library/Net-Library architecture.
This section details the various client-server applications that use the Net-Library architecture.
On the Windows NT platform, SQL Server is implemented as an Open Data Services application. It is capable of supporting clients communicating on multiple network protocols simultaneously without the need for add-on products like SQL Bridge. Server-based Net-Libraries, implemented as DLLs, handle connections concurrently over multiple transports. Several Net-Libraries can be active at once, allowing SQL Server to “listen” on multiple connection types (such as sockets or named pipes) over multiple transports (such as NetBEUI, TCP/IP, or IPX/SPX).
SQL Server for Windows NT supports named pipes connections over either NetBEUI or TCP/IP transports for clients running Windows, Windows NT, MS-DOS, and OS/2 operating systems. Named pipes connections are also supported over IPX/SPX for Windows NT clients using NWLink. In NetWare environments, MS-DOS, Windows and OS/2 clients are supported over native IPX/SPX protocols and require no additional network software other than the Novell redirector. SQL Server also simultaneously supports the TCP/IP sockets API for communication with Macintosh®, UNIX®, or VMS® clients running SYBASE® Open Client software. SQL Server for Windows NT also includes the client- and server-side components for Banyan VINES clients to communicate using VINES IP.
Microsoft SQL Server for the OS/2 platform is designed to communicate via local named pipes or via named pipes extended across the network by a network operating system. Prior to the development of SQL Bridge and the Microsoft SQL Server Network Integration Kit for Novell NetWare Networks and the Microsoft SQL Server Network Integration Kit for Banyan VINES Networks, it was necessary to use network software that supported named pipes in order to communicate with SQL Server or Open Data Services applications. SQL Server on the OS/2 platform can be enabled to communicate with clients using TCP/IP and DECnet™ through SQL Bridge, and IPX/SPX or VINES IP through the SQL Server Network Integration Kits.
Microsoft Open Data Services is a server-side development platform that provides application services to complement the client-side APIs discussed earlier. Open Data Services provides the foundation for multithreaded server applications to communicate with DB-Library or ODBC clients over the network. When the client application requests data, Open Data Services passes the request to user-defined routines, and then routes the reply back to the client application over the network. The reply looks to the client as if the data were coming from SQL Server.
Open Data Services is a server-based library that can receive, break apart, reform, and send TDS packets from many simultaneous clients. It uses the native multithreading facilities of the underlying operating system to handle simultaneous requests in a fast, memory-efficient way. The Open Data Services developer needs to focus only on the actions required to respond to individual requests; Open Data Services and the operating system handle and schedule multiple simultaneous requests. Figure 5 illustrates the architecture of an Open Data Services application; the application developer supplies required functionality to the event handlers registered with Open Data Services, while Open Data Services manages the communication with clients on the network.
Figure 5. An Open Data Services application
On the Windows NT platform, Open Data Services has the ability to load multiple server-side Net-Libraries simultaneously. This allows applications built on Open Data Services to communicate natively with clients on different network protocols simultaneously. It uses the same multithreaded architecture as SQL Server for Windows NT.
On the OS/2 platform, Open Data Services communicates directly with named pipes, so it requires SQL Bridge or a SQL Server Network Integration Kit to communicate with clients running other network protocols.
Microsoft SQL Bridge is a server application based on Open Data Services technology; it can be thought of as a “protocol router.” SQL Bridge uses the Net-Library architecture to support the IPC mechanisms used by SQL Server running on the OS/2, Windows NT, UNIX, or VMS platforms. Each instance of SQL Bridge "listens" for TDS messages from clients using a particular IPC mechanism (named pipes, TCP/IP sockets, DECnet sockets), and then routes the TDS message to SQL Server using a potentially different IPC mechanism. Results are received from SQL Server, translated to the client IPC protocol, and then sent to the client.
SQL Server for the OS/2 operating system can “listen” for client connections using only named pipes. In order for clients using other protocols (such as TCP/IP sockets) to connect to SQL Server, the client requests must go through SQL Bridge. SQL Bridge accepts the socket requests from the clients, and passes those requests on to SQL Server using named pipes. It receives named pipes requests from SQL Server, and passes those on to the clients using sockets. In addition, it allows Microsoft SQL Server clients using native network protocols and IPC methods to access SYBASE SQL Servers using sockets and TCP/IP.
SQL Server for Windows NT has multiprotocol support built in, enabling it to listen for client connections on many different protocols, such as named pipes, TCP/IP sockets, IPX/SPX, and Banyan VINES SPP. Therefore, SQL Bridge is not necessary for clients using those protocols to communicate with SQL Server for Windows NT. In this environment, SQL Bridge is primarily useful for Microsoft SQL Server clients to use native network protocols and IPC methods to access SYBASE SQL Servers using sockets and TCP/IP. This eliminates the need to purchase, load, and configure multiple network protocols and Net-Libraries for each client.
Figure 6. SQL Bridge architecture
Using SQL Bridge in environments where communication is required between Microsoft and SYBASE clients and servers can greatly reduce cost and maintenance overhead, as well as free up resources on each client because it is not necessary to load multiple network protocols and Net-Libraries. Because SQL Bridge uses the Net-Library architecture to support the various IPC mechanisms, it can be configured and extended.
SQL Bridge is a highly efficient application. Its only function is to “listen” for TDS messages coming in and to reroute them using a different IPC mechanism. Because it is optimized for SQL Server client-server communication, you should notice very little overhead when using SQL Bridge.
The Microsoft SQL Server Network Integration Kit for Novell NetWare Networks and the Microsoft SQL Server Network Integration Kit for Banyan VINES Networks enable client workstations to communicate with Microsoft SQL Server and Microsoft Open Data Services running on the OS/2 platform using the native protocol of the network involved (IPX/SPX for NetWare, and VINES IP for VINES). In addition, the SQL Server Network Integration Kits integrate with the naming services of the respective networks, allowing SQL Servers to be registered and easily identified at run time by SQL Server clients.
The SQL Server Network Integration Kits have been developed for SQL Server running on the OS/2 platform. As mentioned earlier, SQL Server for Windows NT communicates with clients on multiple network interprocess communication mechanisms simultaneously, so network integration kits are not required.
The core component of the SQL Server Network Integration Kits is Network Manager, the program responsible for passing network packets back and forth between Novell NetWare or Banyan VINES clients and either Microsoft SQL Server or a Microsoft Open Data Services application (for example, a gateway to another data source). Network Manager receives incoming network packets sent across the native network protocol (IPX/SPX or VINES IP), and then passes them on to SQL Server using local pipes. Similarly, it receives packets from SQL Server via local pipes and passes them on to Novell NetWare or Banyan VINES clients using the native network protocol. Network Manager uses appropriate server-side Net-Libraries to listen and communicate on the required network protocol (IPX/SPX or VINES IP). SQL Server clients running the matching client-side Net-Library and network software can then communicate with SQL Server or Open Data Services via Network Manager.
The Microsoft SQL Server Network Integration Kit for Novell NetWare Networks enables clients to communicate with Microsoft SQL Server and Open Data Services applications using the Novell IPX/SPX protocol, as shown in Figure 7.
Figure 7. Communication using Microsoft SQL Server Network Integration Kit for Novell NetWare Networks
SQL Server clients in NetWare-based environments thus have a choice of how they communicate with SQL Server—by using named pipes (supplied with the NetWare Requester) or by using SPX supported through the SQL Server Network Integration Kit for Novell NetWare Networks.
The SQL Server Network Integration Kit for Novell NetWare Networks eliminates the need for a named pipes TSR on MS-DOS– and Windows-based clients, thereby reducing memory requirements. It integrates directly with the Novell Bindery for finding servers, so clients can connect to a server, by name, any time after startup (note, however, that this is not the case when using named pipes support provided by the NetWare Requester). The DB-Library function, dbserverenum, returns a list of SQL Servers registered with the Novell Bindery.
Note The dbserverenum function also returns a list of server names when using Novell named pipe support. In a named pipe environment, dbserverenum is resolved into a NetServerEnum call, which is supported by NetWare Requester named pipe support.
The Microsoft SQL Server Network Integration Kit for Banyan VINES Networks enables clients to communicate with Microsoft SQL Server and Open Data Services applications using the Banyan VINES IP protocol, as shown in Figure 8.
Figure 8. Communication using Microsoft SQL Server Network Integration Kit for Banyan VINES Networks
SQL Server clients in Banyan VINES environments thus have a choice of how they communicate with SQL Server—by using named pipes, supplied with the VINES Requester, or by using VINES IP supported through the Microsoft SQL Server Network Integration Kit for Banyan VINES Networks.
The SQL Server Network Integration Kit for Banyan VINES Networks eliminates the needs for a named pipes TSR on MS-DOS– and Windows-based clients, thereby reducing memory requirements. This approach resolves the problems encountered with SQL Server and the Banyan implementation of named pipes on wide-area networks, which registers SQL Servers using the NetBIOS naming service. Calls to dbserverenum return a list of SQL Servers registered with the StreetTalk™ naming service.
The following points provide further technical insights into the use of the Microsoft SQL Server Network Integration Kits:
Note On the Windows NT platform, SQL Administrator, remote stored procedures, and the console utility use the DB-Library/Net-Library architecture.
This technical note has described the architecture and technology that allows SQL Server and related products to integrate well into all popular local-area network operating systems, and to gain connectivity to enterprise data sources.
To receive more information about Microsoft SQL Server, or to have other technical notes faxed to you, call Microsoft Developer Services FAX Request at (206) 635-2222.
"Microsoft Open Data Services: Application Sourcebook." MSDN Library, Technical Articles.
Moffatt, Christopher. "Designing Client-Server Applications for Enterprise Database Connectivity." MSDN Library, Technical Articles.
Narayanan, Suriya. "Maximizing Performance Using Binary Columns and Bitwise Operations in Microsoft SQL Server for Windows NT." MSDN Library, Technical Articles.
"Query Optimization Techniques." MSDN Library, Technical Articles.
Schroeder, Gary. "Backup and Recovery Guidelines for Microsoft SQL Server." MSDN Library, Technical Articles.