Microsoft Corporation
Summary: Microsoft® Open Data Services, a key feature of Microsoft SQL Server version 4.2, opens up extensive possibilities for new client-server applications. Simple applications written on top of Open Data Services can extend a SQL Server application to the outside computing environment—to fetch data values from another system, for example, or to notify a user on the local-area network (LAN) of changes in the SQL Server database. A security layer built with Open Data Services can verify login information with the network operating system or maintain an audit log of client transactions. Other applications for Open Data Services might not involve a SQL Server database at all: Open Data Services can be used to build applications that "look like" a SQL Server to a client on the LAN. These applications might do something very simple, such as responding to a predetermined procedure call, or they might perform more complex tasks, such as providing a general-purpose gateway to another vendor's relational database. (12 printed pages)
Just as the client application programming interface (API) in SQL Server allows corporate developers and independent software vendors (ISVs) to produce unique and powerful client applications that share a common database server, Open Data Services provides the foundation for a variety of server applications that share a common client interface. By building applications on top of Open Data Services, developers can take advantage of the work Microsoft has done to design, build, test, support, and promote the client-server protocol in SQL Server. The resulting applications can truly enable client-server computing in the real world of established applications, disparate networks, multiple vendors, and limited resources.
An earlier version of Open Data Services was sold separately as the Microsoft SQL Server Gateway Development Kit. Including this server toolkit in Microsoft SQL Server version 4.2 adds significant value and encourages you to investigate its potential for powerful new applications.
This technical article gives an overview of how to build applications using Open Data Services. It also suggests several useful applications, some of which are very simple to implement. This discussion is not intended to limit your creativity; instead, it is intended to stimulate creative thinking about ways you can use Open Data Services.
Open Data Services provides the foundation for multithread server applications to communicate with SQL Server DB-Library™ or open database connectivity (ODBC) clients. (ODBC is the Microsoft programming interface for Microsoft Windows™ operating system applications.) 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.
A comparison of Open Data Services with SQL Server helps to explain how Open Data Services operates. SQL Server can be described as having two logical components: the client manager and the data manager. The client manager represents the portion of SQL Server that coordinates requests from many DB-Library or ODBC clients on the network. It handles such tasks as establishing the named pipe instance for each client connection, formatting data for transmission over the network, and managing multiple client threads. The data manager performs the database request each client makes and handles such tasks as parsing the SQL request, optimizing data access, establishing locks to control concurrency, and accessing data on the disk drive.
Given this picture of SQL Server, think of Open Data Services as the complete client manager, with the addition of "hooks" that allow developers to define their own data management routines for client requests. The Data Services library handles all network communication with network clients, and the Open Data Services developer determines what data to return in response to individual client requests.
A server application written for Open Data Services consists primarily of a set of event handlers. These handlers respond to events triggered by incoming client requests. In a sense, Open Data Services "runs" the custom application by calling these event handlers at the appropriate times. This style of programming is similar to writing an application for a graphical user interface such as the Microsoft Windows graphical environment. In the case of a Windows-based program, the environment notifies the program of events such as mouse clicks or key presses by the user. The program must be prepared to handle these events and take appropriate action. In the case of an Open Data Services application, the Data Services library notifies the application of client actions such as user login requests. The application is responsible for taking the appropriate action when each type of event occurs.
Figure 1 illustrates the structure of an Open Data Services server application.
Figure 1. A server application written with Open Data Services consists primarily of user-written event handlers. These event handlers are called by the Data Services library when it receives client requests.
An Open Data Services application needs to handle only a few client events. The following table lists these major events:
| Event | Description |
| Connect | A client user wants to connect to the Open Data Services application. |
| Language | A client has sent a command string (such as a SQL statement) to the server for processing. |
| Remote stored procedure | A client or a SQL Server has called a remote stored procedure on the Open Data Services server. |
| Disconnect | A client has disconnected from the Open Data Services application. |
To the application, these events come in one at a time, complete with information about who the client is and what the client is requesting. The Open Data Services application deals with these events in isolation, without having to worry about blocking other clients by taking too long with a particular request. In reality, all client events can come in simultaneously. To deal with simultaneously occurring events, the Data Services library creates a thread and allocates memory for each new client connection. In a running application with many client users, the same event handler code will execute at the same time in many different threads, with each thread dedicated to a different client connection. This multithread operation enables server applications written with Open Data Services to maintain a high level of performance and availability. At the same time, developing a multithread server application becomes much simpler, because Open Data Services itself handles all of the complexity.
Open Data Services comes with several sample applications: one of these, the GATEWAY application, implements a "pass-through" gateway to another SQL Server. The GATEWAY application illustrates all of the event handler types. The Language event handler, for example, issues a dbsqlexec call against the back-end SQL Server, passing on the same command that the client sent to the gateway. Many potential Open Data Services applications involve simple modifications of this pass-through gateway.
Remote stored procedure events deserve special attention for their flexibility and ease of use. Open Data Services remote stored procedure calls correspond to the remote stored procedure feature in Microsoft SQL Server version 4.2. This feature allows a client connected to a local SQL Server to execute a stored procedure on another SQL Server. The local server issues a request to the remote server on behalf of the client and then directs any results of that remote request back to the client. Version 4.2 of DB-Library includes new APIs that allow a client application to call remote stored procedures directly from a client.
Remote stored procedures are particularly powerful when combined with Open Data Services. A SQL Server or a client can issue a remote stored procedure call to an Open Data Services application. Figure 2 illustrates a connection between a client, SQL Server, and Open Data Services using remote stored procedures. To Open Data Services, these requests from clients or SQL Servers look the same: they simply trigger a remote stored procedure event. Thus, with one event handler, an Open Data Services application can respond to requests that come from a SQL Server (as the result of a trigger, for example) or directly from a client. Moreover, event handlers for remote stored procedure events are generally easier to write than language event handlers, because the incoming command and parameters do not have to be parsed from a command string.
Figure 2. Remote stored procedure events can originate with clients or with SQL Server, either running on the same computer (as shown here) or on another server on the network.
The ability to activate Open Data Services remote stored procedure events from SQL Server itself makes these events particularly useful for extending SQL Server solutions to the external environment. For example, an Open Data Services application could initiate updating data in SQL Server from a centralized database. An application using remote stored procedures could also notify a network operator when data in a particular table on SQL Server changes.
Although Microsoft SQL Server is the fastest and most flexible database management system for PC networks available today, your particular enterprise might require extended capabilities. Because of the tight integration of Open Data Services with SQL Server and SQL Server clients, you can usually address these needs through a server application. Open Data Services literally "opens up" the server side of a SQL Server application and lets it communicate with other resources on the network. Open Data Services applications can take advantage of extensions to SQL Server for a variety of applications. The following scenarios show how Open Data Services applications can help increase security, enhance tracking of database activity, automatically notify users of changes in data, facilitate the running of external programs, and obtain data from an outside source.
Microsoft SQL Server has its own system of login IDs, passwords, user groups, and permissions that allows it to maintain tight security on any type of PC network, be it Microsoft LAN Manager, Novell®, or Banyan®. In some cases, however, you might want to integrate SQL Server security with the security provided by the local-area network. An integrated security system can simplify administration by maintaining only one set of user IDs and passwords for all users on the LAN. Other security applications might require that access to SQL Server by users be logged.
Microsoft LAN Manager has a powerful user-based security scheme for controlling use of network resources. The LAN Manager system includes the domain feature, which allows user accounts and passwords to be synchronized across an entire group of LAN Manager servers. LAN Manager can also restrict access to server applications such as SQL Server by restricting access to the named pipe resource that SQL server uses to communicate with clients. Using the Net Administration tool, the administrator can grant or deny access to the pipe\sql\query resource by user ID, by group, or by user. And with a click of the mouse, the administrator can turn on auditing of successful or unsuccessful attempts to access named pipes.
By itself, however, restricting named pipe access does not secure the SQL Server running on a LAN Manager network. Users who log in to SQL Server can give login IDs different from their LAN Manager usernames. As long as SQL Server recognizes the login ID and password combination given, it will allow access. Users who have permission to access the server for queries only (and are thus granted permission to use named pipes in LAN Manager) can log in as system administrators of the SQL Server if they know the password.
This problem is solved by the SECURE sample application included with Open Data Services. This application is a simple modification of the pass-through gateway, designed to run as a security layer in front of SQL Server, as shown in Figure 3.
Figure 3. A simple security layer implemented with Open Data Services can help verify logins with the network operating system and keep an audit trail of all requests sent to SQL Server.
To integrate SQL Server logins with LAN Manager security, the SECURE application traps connect events before passing them on to SQL Server. When a client requests a connection, SECURE calls LAN Manager to see what client is on the other end of the named pipe. The application then compares this network username with the login ID the user gives to SQL Server. If the names match, the application simply sends the login on to SQL Server. If the names differ, the Open Data Services application rejects the login attempt.
A network administrator can employ the SECURE application by prohibiting all access to the SQL Server named pipe but allowing all clients to access the SECURE application's named pipe. The administrator can also enable auditing on the named pipe for SECURE. Because LAN Manager is responsible for verifying network usernames and passwords, the administrator can simplify network maintenance by not requiring a second password within SQL Server itself. Because the design of Open Data Services is so efficient, the overhead of adding this security "layer" in front of SQL Server is minimal.
The SECURE application is provided in source code form, so you can adapt it to similar applications. The code can be modified, for example, to query other security systems when it receives a connect event. Alternatively, the code can be enhanced to query LAN Manager group names for the user of the named pipes. A group name could be substituted for the login ID in the connection to SQL Server. SQL Server administration would then involve granting permission by LAN Manager group name rather than by individual network username.
Transaction processing lets SQL Server ensure that committed changes to a database are actually made and incomplete changes are backed out. However, some applications require a history log of all requests that have taken place against the database—recorded with a time stamp and a user ID—whether or not users complete changes. This log lets the database administrator know who issued a given request to the database, and when. Using triggers, certain types of auditing can be performed in SQL Server, but this method is appropriate only for tracking requests that update specific tables. It does not provide a global log of all requests made to the server. With Open Data Services, you can implement auditing of all client requests with a minor modification of the pass-through gateway.
The SECURE application illustrates this method of auditing. If the appropriate flag is given on startup, all language events coming from clients are recorded in a file before being passed on to SQL Server. Because the modifications required to implement request auditing use the built-in logging functions of Open Data Services, only a few lines of additional code are involved. You can use this logging feature as is, or you can alter it to log only UPDATE, INSERT, and DELETE language events. You can also modify this feature to record remote stored procedure events and error messages sent to clients from SQL Server.
Client-server architecture assumes that clients want the latest copy of data from the server only when they request it. SQL Server always responds to a client request, but it never initiates the conversation. In some applications, however, a client must be notified within a short period of time that a key piece of data has changed at the server. A stockbroker, for example, might want to know immediately when the price of a particular security changes by more than a certain percentage. Using standard client-server architecture, that stockbroker's client workstation would have to query the server frequently to see whether the data had changed. This type of activity costs processing resources at both the client and the server.
The remote stored procedures capability of Open Data Services offers a simple solution to this problem. In SQL Server, you can place a trigger on the table of interest. When data in that table changes, the trigger activates a remote stored procedure call to an Open Data Services application, passing as a parameter in the procedure call the name of the client to be notified. In the Open Data Services application, the remote stored procedure event handler responds in turn by calling a program that notifies the designated client.
The LAN Manager mailslot API provides a flexible mechanism for notifying a client on the network when something new arrives in the client's mailslot. The PROCSRV sample application included with Open Data Services uses LAN Manager mailslots to implement the broadcast remote stored procedure call. Access to the client side of the mailslot can be incorporated into a front-end application using a tool such as Microsoft Visual Basic™ programming system. Another method for notifying clients uses the net send command.
Often a business application needs to take a certain action when the value of a field in a particular table reaches a certain threshold. An inventory application, for example, might reorder stock when the quantity on hand falls below a designated reorder point. Using conventional client-server SQL applications, the client application would have to query the database periodically to determine if the reorder point had been reached. Open Data Services provides a better approach to this problem: a trigger on the table can compare the quantity-on-hand value with the reorder point. When the quantity on hand falls too low, the trigger issues a remote stored procedure call to an Open Data Services application, passing it the item's part number as a parameter. The remote stored procedure handler then automatically runs an external ordering application. The ordering application might even send an electronic mail message to a purchasing agent about the part that fell below the reorder point and must be reordered.
The PROCSRV sample application in Open Data Services provides a mechanism for handling this type of SQL Server extension without any C programming. SQL Server can call sp_exec, passing it the name of a command or executable file on that server, along with any required input parameters. In response to the event, PROCSRV automatically starts the requested command or executable file. Using this application, a SQL Server installation can draw on a set of simple command or executable files in any language to perform the external functions required by the client-server database applications.
Some applications require SQL Server to reflect a current data value from the external environment. A currency-trading application, for example, might need to get a current exchange value from a wire service. With conventional client-server techniques, a client program would be responsible for obtaining this value and updating the server as frequently as required. Using Open Data Services, however, this value can be obtained by SQL Server, when required, from an Open Data Services application. The server calls the Open Data Services application as a remote stored procedure, and then the application sends the current value(s) back to SQL Server in the form of return parameters. You can then store the value in a table or incorporate it into a Transact-SQL™ procedure as required.
This type of application is suited to situations that involve a computation that can be performed more easily in a program external to SQL Server. The PROCSRV sample application is an example: When SQL Server calls the diskfree remote stored procedure, passing a drive letter as a parameter to the procedure, PROCSRV returns the amount of free disk space on the drive.
Gateway applications using Open Data Services focus on connecting SQL Server clients to outside data sources, rather than interacting with SQL Server. Gateway applications fall into two general categories: general-purpose gateways to other relational databases and application-specific gateways that respond to a limited set of predefined data requests.
The classic application for Open Data Services is a server-based gateway to another relational database—one that can handle any ad hoc SQL request from a DB-Library or ODBC client. The Database Gateway from Micro Decisionware®, for example, implements a general-purpose gateway into DB2®. It receives SQL requests from SQL Server clients on the LAN and forwards them to the mainframe for processing against a DB2 database. Because the results it then returns to the client look exactly like a results set from SQL Server, the client applications can handle the data. A component that understands the SQL language and can act on SQL requests is essential to the operation of a general-purpose gateway. This SQL interpreter usually resides in the back-end database itself (as is the case with DB2), but it can also be implemented in the gateway. The RMS Gateway from SQL Solutions, for example, implements a full Transact-SQL interpreter that permits SQL queries to flat files on a DEC® VAX® minicomputer.
Relational database gateways based on Open Data Services give customers and software developers tremendous leverage from their investment in SQL Server. Many development tools based on DB-Library work directly against an Open Data Services gateway without modification. But not all SQL databases are equal—gateways don't magically make all back-ends support the same capabilities as SQL Server. Front-end tools that expect the unique features of SQL Server usually must be adapted to work through a gateway to another data source. Fortunately, the work required to adapt an existing DB-Library application to operate with a gateway is very small compared to the effort of rewriting to a new API.
Looking at the problem of heterogeneous databases more broadly, one approach that application developers can take is to write to the lowest common denominator of database features. Another approach is to attempt to create a single system image on the server side. In this approach, all databases or gateways appear to support the exact same SQL syntax, datatypes, system catalogs, and capabilities. Microsoft, however, believes that the most practical approach to heterogeneous data access is to put enough intelligence into front-end applications so that they can handle the differences among databases and exploit the unique features of each. This principle is the basis of the Microsoft Open Database Connectivity (ODBC) programming interface for Windows-based applications. ODBC provides a database-neutral, call-level interface based on the SQL Access Group standard for accessing relational databases. ODBC provides facilities that allow the client application to determine the capabilities of the back-end database and then behave appropriately. ODBC is designed to function both in a direct connection to a database (with an ODBC driver tailored to the data source) and in a gateway configuration (with an ODBC driver that connects through a gateway).
Microsoft SQL Server supports ODBC clients with a fast, full-featured ODBC driver for the Windows environment. This driver will also work with Open Data Services relational gateways. When an ODBC client application needs database-specific capabilities or system catalog information, the ODBC driver gets the information from the gateway using a set of catalog stored procedures defined by Microsoft and Sybase. This architecture makes it possible for Open Data Services gateways to connect ODBC client applications with no changes at all on the client workstation. Furthermore, because these catalog stored procedures are a standard feature of SQL Server, developers with existing DB-Library applications can easily adapt them to interoperate across many different gateways.
If you have multiple relational data sources on a network, including multiple SQL Servers and database gateways, general-purpose gateways can provide additional integration services to client applications. The Micro Decisionware database gateway, for example, implements a two-way data transfer service between SQL Server and DB2 in addition to connecting LAN clients to mainframe data. A client simply issues a TRANSFER command that includes a SELECT statement for one database and an INSERT statement for the other. The data is piped between databases through the gateway without using any intermediate files.
More sophisticated gateways can take advantage of the SQL Server advanced features, such as a distributed transaction capability that few other relational databases can match. Using special DB-Library calls for two-phase commit, a SQL Server client can update two or more SQL Servers as part of a single transaction. If the transaction rolls back, all of the changes at all of the servers are rolled back. Most front-end applications, however, are not designed to support this DB-Library feature. A data integration application based on Open Data Services can intercept transaction statements from the clients and automatically issue the appropriate two-phase commit calls on their behalf. This type of application can keep a redundant backup server in sync with all updates made on a primary server.
Not all data server applications need to understand and respond to SQL requests (for example, a data server application that returns the contents of a specific flat file as a results set). This type of application might be designed to respond to only one particular procedure call (such as GetFileA). The event handler in the Open Data Services application would define the column names and the datatypes of the fields in the flat file and then return the records in the file to the requesting client as rows of data. Because this results set would look just like a SQL Server results set, the client could process it.
Nearly any set of data values that needs to be shared on a network can be described in terms of rows and columns, so this capability is relevant to many LAN applications. In particular, specific data needed by LAN users is often maintained by an established application that does not use a relational database. Rather than attempting to move this entire application to a relational database, you can use an application-specific gateway to extract from the existing system the precise information required. This approach works when the information required from the existing system is well-defined, not ad hoc in nature. For ad hoc queries, the better approach is to extract the data from the existing system and load it into an actual SQL Server database.
The PROCSRV sample provided with Open Data Services is a simple example of a procedure server that responds to a call from a client to sp_diskfree by returning the number of bytes free on all disk or network drives attached to the server. PROCSRV also provides flexible facilities for calling its stored procedures as language events from clients on the network. Client applications written for SQL Server version 1.1 cannot use the new APIs available in version 4.2 for calling stored procedures directly. These clients can only issue EXEC procedurename calls in the same way as they issue SQL requests. PROCSRV converts these language events into remote procedure call events (including all parameters), which the remote procedure call handler then processes. This allows the Open Data Services application developer to write one simple procedure handler that can interoperate with existing SQL Server version 1.1 clients and new version 4.2 clients and servers on the network.
The data retrieved by an application-specific gateway need not be from disks or from existing computer applications. Any data that can be read by the server can be incorporated into server procedures. Potential data sources for Open Data Services applications include the following:
Whenever a defined set of data is required from one of these sources by clients on the LAN, an Open Data Services application can solve the problem. Accessing these data sources from one server rather than from every client generally results in lower costs and improved maintenance for a given application.
There are several distinct advantages to building new client-server applications around the client interfaces that Microsoft has established with SQL Server. In general, building an application around an established API shortens the development and test cycle and produces an application that can interoperate with SQL Server solutions. Even for application-specific gateways that have nothing to do with SQL requests, building around Open Data Services offers significant advantages over the alternative of defining an entirely new set of client calls specific to the application. These advantages include the following:
Microsoft Open Data Services is a powerful, flexible platform for building new server applications in a PC local-area network environment. Applications built with Open Data Services can offer value-added enhancements to SQL Server installations, and they can provide entirely new data services to SQL Server clients.
Open Data Services creates a powerful, open architecture on the server side to complement the open interface of SQL Server on the client side. Open Data Services enables client-server computing to be successfully implemented in the real world of existing applications, mixed data sources, and unique business requirements.
To receive more information about Microsoft SQL Server, contact Microsoft Inside Sales, Systems Software, at 1-800-227-4679.
"Using Microsoft SQL Server on a Banyan VINES Network" (part number 098-30193)
"Using Microsoft SQL Server on a Novell NetWare Network" (part number 098-32655)