Running SQL Server in the NetWare Environment Ü Running SQL Server in the NetWare Environment Û Û ßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßß Willie Tejada Corporate Integration Manager Systems Engineering Division ÉÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍ» º PgDn to Scroll or follow link for º º Table of Contents º ÈÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍÍͼ Abstract: This Application Note explains how to install Microsoft's SQL Server v1.0 and v1.1 in a NetWare environment, using Novell's Named Pipes support for both OS/2 and DOS clients. It summarizes some of the issues involved in using Named Pipes for client-server applications and details the architecture of SQL Server under NetWare from both the OS/2 and DOS client perspective. It also gives a step-by-step procedure for manually installing SQL Server v1.0 (designed solely for LAN Manager networks) on a NetWare network. Finally, it includes some pointers for running SQL Server, querying a database, and diagnosing problems associated with Named Pipes connections. Running SQL Server in the NetWare Environment Contents Introduction The Client-Server Model Named Pipes Life Cycle of a Named Pipe Architecture of SQL Server Under NetWare NetWare Requester for OS/2 NetWare Requester for OS/2 Client Architecture NetWare DOS Named Pipes Extender Client Architecture NetWare Requester for OS/2 Installation Issues Tuning SPX NetWare DOS Named Pipes Extender Installation Issues Tuning the DOS Named Pipes Extender Maximum Machine Names Maximum Open Named Pipes Maximum Communication Buffers SQL Server v1.0 Installation SQL Server v1.1 Installation Operation Issues Starting and Stopping the SQL Server Diagnosing Problems with MAKEPIPE and READPIPE Querying the SQL Server Summary Appendix A: Bibliography Disclaimer Novell, Inc. makes no representations or warranties with respect to the contents or use of these Application Notes (AppNotes) or of any of the third-party products discussed in the AppNotes. Novell reserves the right to revise these AppNotes and to make changes in their content at any time, without obligation to notify any person or entity of such revisions or changes. These AppNotes do not constitute an endorsement of the third-party product or products that were tested. Configuration(s) tested or described may or may not be the only available solution. Any test is not a determination of product quality or correctness, nor does it ensure compliance with any federal, state or local requirements. Novell does not warranty products except as stated in applicable Novell product warranties or license agreements. Copyright (c) 1991 by Novell, Inc., Provo, Utah. All rights reserved. As a means of promoting NetWare AppNotes, Novell grants you without charge the right to reproduce, distribute and use copies of the AppNotes, provided you do not receive any payment, commercial benefit or other consideration for the reproduction or distribution, or change any copyright notices appearing on or in the document. Running SQL Server in the NetWare Environment Introduction In 1989, Novell included support for the Named Pipes application programming interface (API) in the NetWare Requester for OS/2. The following year, Novell added support for Named Pipes in the NetWare DOS Client Shell. This Named Pipes support makes it possible to run applications based on Named Pipes in the NetWare environment. Microsoft's (R) SQL Server is one such application. In most network configurations, SQL Server is installed as a LAN Manager service and runs as an OS/2 application along with other OS/2 applications such as Microsoft LAN Manager, IBM LAN Server, or 3+ Open. The SQL Server documentation fully describes the installation and use of the SQL Server in a LAN Manager environment. Microsoft has certified SQL Server for use in the NetWare environment. However, prior to SQL Server v1.1, the installation programs and manuals which accompanied SQL Server presumed installation into a LAN Manager environment. This Application Note examines the different components of NetWare and SQL Server, and explains how to install and configure these components in a NetWare environment. Running SQL Server in the NetWare Environment The Client-Server Model The client-server model expands the number of distinct pieces of applications software needed to support database activity from one to at least four. Traditional PC database management systems used the server both for data storage and as a distribution facility. Figure 1 shows four parts of a client-server database application. Part 1, the client front end, typically is responsible for such tasks as presentation, collection, and validation of data. This front end might be a custom application written to provide a simple-to-use graphical or text user interface. The front end accomplishes its tasks by passing SQL statements to part 2, the interface between the front end and the underlying network protocol. This network interface can facilitate network communications for the front end application. This part always looks the same to the front end, but must be customized to work with specific network protocols. Microsoft SQL Server uses the Named Pipes API as its network interface. Novell has provided the customization of the interface to the network protocol by providing Named Pipes support over Novell IPX/SPX through a Named Pipes driver and daemon supplied in the NetWare Requester for OS/2 and through a Named Pipes extender for DOS clients. (These components are discussed later in this AppNote.) Part 3 is analogous to part 2, but acts as the interface between the database engine and a particular network protocol. Part 4 is the database engine. It ultimately receives SQL statements generated by the client front end and responds to them. To establish a successful connection between the front end and the server, parts 2 and 3 must support the same network protocol. Running SQL Server in the NetWare Environment Named Pipes A basic understanding of Named Pipes may provide a useful background on how the various components of the SQL Server and NetWare fit together. Named Pipes is a high-level API for interprocess communications (IPC). Named Pipes provides a method for transferring data between different processes across the network or in the same machine. These functions have been part of OS/2 since v1.1 Standard and Extended Edition and do not require LAN Manager/LAN Server to be installed. As mentioned before, the use of Named Pipes shelters both front-end and server applications from the underlying transport protocol, allowing applications to run on many different networks. Life Cycle of a Named Pipe To illustrate how a server-based application works with Named Pipes, let's look at how a database server works on an application server using Named Pipes. Microsoft's SQL Server is an example of such an application. (Named Pipes is not required for server-based applications. Other server-based applications use IPCs such as Novell's IPX/SPX or NetBIOS to accomplish the same functions). Figure 2 steps through the life cycle of a Named Pipe. First, a named pipe is created on the server side with the DosMakeNmPipe API, and the pipe is given a name. Second, the server-based application must connect to the pipe using the DosConnectNmPipe API. The Named Pipe server is now in a listening state, waiting for a client to establish a connection and request its resources. Third, the client end connects to its end of the pipe by using the DosOpen API and submitting the name of the server and the pipe. The Named Pipe server receives the client's request and the connection is established. If the NetWare Requester for OS/2 is installed on each machine, the user must clarify which machine will be the Named Piped server and which will be the Named Pipe client. There is a distinction between the two: Named Pipe servers broadcast their machine name on the network; Named Pipe clients do not. Fourth, after both sides are connected, they can begin the DosRead, DosWrite, or DosTransactNmPipe dialogue. Lastly, when the dialogue is complete, the server calls the DosDisconnectNmPipe API, and the client performs the DosClose operation. The server can then execute another DosConnectNmPipe call and wait for the next client. Running SQL Server in the NetWare Environment Architecture of SQL Server Under NetWare Currently, the SQL Server is an OS/2 application and cannot run on a NetWare file server. To support SQL Server and other Named Pipes-based client-server applications, Novell added support for the Named Pipes API to both their OS/2 and DOS client services. This allows OS/2 computers to be set up as application servers and communicate in a peer-to-peer fashion with client programs running on other workstations. Figure 3 illustrates a typical SQL Server installation on a NetWare Network. Because of the peer-to-peer nature of the Named Pipes support, the NetWare server need not be present for the SQL Server to function. NetWare Requester for OS/2 The NetWare Requester for OS/2 is Novell's client software for OS/2 workstations. It allows OS/2 workstations to access and use resources on a NetWare file server. It also supports Named Pipes and NetBIOS communications. The NetWare Requester includes the following components. Component Description NetWare Spooler OS/2 print spooler LSL driver The link support layer (LSL) driver, part of Novell's Open Data-Link Interface (ODI) NIC driver An ODI-compliant network interface card Novell IPX Protocol Novell's Internetwork Packet Exchange (IPX) protocol stack SPX Protocol Novell's Sequenced Packet Exchange (SPX) protocol stack. This stack is optional, but required for Named Pipes support. Named Pipes Layer Named Pipes driver and daemon. Requires useof the SPX protocol stack. A "computername" parameter is required when loading the daemon to configure the OS/2 machine as a Named Pipes server. Requester NetWare requester drivers and daemon.Provides I/O redirection for file, printer, and serial devices to NetWare servers. NetBios Layer NetBIOS driver and daemon. Not required forNamed Pipes support. NetWare Requester for OS/2 Client Architecture Figure 4 illustrates the NetWare OS/2 Requester client architecture. The NetWare Requester services user requests, such as directory listings and logins, as well as application requests, such as file access or print service. OS/2 was designed to be an intelligent workstation operating system. This means that when the OS/2 kernel receives a call from an application or a user, it is capable of determining if the call is for a local or a remote service. If the call is local, OS/2 will execute the request at the workstation. Examples of local requests include accessing files on a local hard disk or printing to a local printer. If the call is remote, such as creating a file or listing a directory's contents, the OS/2 kernel passes the request to the NetWare Requester. When the NetWare Requester receives the call, it can do one of two things: send the request on its way to the file server or hand the request to the Named Pipes driver. If the call is a request for NetWare services (such as file or print access), the NetWare Requester translates the request into a NetWare Core Protocol (NCP) request. (NCP is the language spoken at the NetWare file server.) The NCP request is then passed on to Novell IPX, which takes care of the routing of information over the network. If the call is for Named Pipes, the NetWare Requester passes the call to NetWare's Named Pipes driver. Once Named Pipes has completed its function, it passes the call to SPX. (Notice the application's Named Pipes call is never translated into NCP.) SPX sits on top of Novell's IPX and guarantees packet delivery on the network. During run time, an OS/2 SQL Server client application makes calls to the DB-Library, a call-level host language interface. This collection of function and procedure calls provides an easy-to-use, uniform, consistent interface to the SQL Server for the C programming language. The DB-Library in turn makes calls to the SQL Server's NetLib interface for Named Pipes, which is implemented as a dynamic link library (DLL). The DBNMPP.DLL makes Named Pipe calls to the NetWare Requester, which passes the calls to the NetWare Named Pipes driver. The NetWare Named Pipes driver then makes use of Novell's SPX protocol to communicate with a SQL Server on an OS/2 application server. NetWare DOS Named Pipes Extender Client Architecture Figure 5 illustrates the DOS client architecture. The NetWare DOS shell program works with IPX and the network driver to convert a standalone DOS workstation to a network workstation. The NetWare DOS Shell is loaded into RAM when access to the NetWare file server is desired. The shell sits between the operating system (DOS) and the DOS client application. The shell intercepts DOS function requests (through interrupt 21h) and determines whether the request is for a local or NetWare service. If the call is a request for a NetWare services (such as file or print access), the DOS shell translates the request into NCP and uses the underlying Novell IPX protocol to route the request to the file server. Novell provides several other IPCs to the DOS client, including NetBIOS, Named Pipes, and the SPX guaranteed delivery transport. Client-server applications may use any of these IPCs. A DOS SQL Server client application uses Novell's DOS Named Pipes Extender (DOSNP.EXE), implemented as a terminate-stay-resident (TSR) program. As on an OS/2 workstation, an interface to the SQL Server's Named Pipes interface is required. Microsoft provides this static link library as a TSR program, DBNMPIPE.EXE. At run time, the DOS SQL Server client application makes calls to the DB-Library. The DB-Library in turn makes calls to the SQL Server NetLib interface for Named Pipes (DBNMPIPE.EXE). The DBNMPIPE.EXE TSR makes Named Pipes requests that are trapped by Novell's DOS Named Pipes Extender (DOSNP.EXE) TSR. As in the OS/2 workstation sequence of events, the Named Pipes calls are passed directly to the DOS Named Pipes Extender, which then uses the underlying Novell IPX/SPX protocols to route the request to the Named Pipes server. (Notice that the DOS SQL Server client application Named Pipes calls are never passed through the NetWare DOS shell for translation into NCP requests). Running SQL Server in the NetWare Environment NetWare Requester for OS/2 Installation Issues Follow the procedures for installation provided in the NetWare Requester for OS/2 installation manual, making the following modifications to the OS/2 CONFIG.SYS file for SQL Server operation. Since the default lines added to CONFIG.SYS for the OS/2 Requester do not load the SPX protocol stack or Named Pipes support, you must do this manually by uncommenting certain lines. For example, you would remove REM from these lines: REM DEVICE=C:\directory\SPX.SYS REM RUN=C:\directory\SPDAEMON.EXE REM DEVICE=C:\directory\NMPIPE.SYS REM RUN=C:\directory\NPDAEMON.EXE "computername" Note that the Named Pipe support layer in the OS/2 Requester is installed after the Novell IPX and SPX stacks are loaded. When uncommenting the lines to load Named Pipes support, it is important to provide a unique "computername" to the Named Pipes daemon. This allows the workstation to act as a Named Pipes server and makes the server visible on the network for programs like the SQL Server System Administration Facility (SAF). It also allows NetWare to imitate the server naming scheme used by LAN Manager and indicates that applications will be creating and sharing Named Pipes at this application server. The OS/2 requester diskette for version 1.21 contains two DLLs in a \SQL directory that emulate a small subset of Microsoft LAN Manager API calls. In particular, these DLLs are required to support the extended functionality of the SAF. Note that these DLLs are provided specifically for SQL Server applications and so are not automatically installed with the OS/2 Requester. You will need to check that these DLLs are installed in the OS/2 LIBPATH before using the SAF on an OS/2 server or workstation. A README file in the \SQL directory clearly explains what needs to be done. Tuning SPX The NET.CFG file can include the following SPX parameters under the "Protocol Stack SPX" heading: Protocol Stack SPX Socket "count" Sessions "count" (Note: replace count with a value 256) SPX Abort timeout "number" SPX Verify Timeout "number" SPX Listen Timeout "number" SPX Retry Count "number" In order to get the maximum number of Named Pipes connections on the OS/2 application server, increase the SPX sessions parameter from its default of 16 to the maximum of 256. If there are not enough SPX sessions available, the OS/2 Requester issues the following error message: NPDAEMON:Error SPX listen for connection For a complete listing of NET.CFG parameters, refer to the installation guide under "Reconfiguring the OS/2 Requester." Running SQL Server in the NetWare Environment NetWare DOS Named Pipes Extender Installation Issues Installing the DOS Named Pipes support layer is straightforward. The Novell OS/2 Requester diskette for version 1.21 contains a \DOSNP subdirectory that contains the Named Pipes TSR (DOSNP.EXE). Copy this TSR to the network directory on the local workstation (or to the boot directory on the NetWare file server for remote-boot systems). In the AUTOEXEC.BAT file, insert a line to load DOSNP.EXE immediately after the Novell IPX driver and the NetWare Shell. The network software must be loaded in the following order: o IPX.COM o NETx.COM o DOSNP.EXE o DBNMPIPE.EXE (provided with SQL Server) Tuning the DOS Named Pipes Extender To fine tune the NetWare DOS Named Pipes Extender, set the following options in the SHELL.CFG file. (You can rename this file NET.CFG.) Maximum Machine Names The NetWare DOS Named Pipes Extender maintains a table of Named Pipes servers on the network. The maximum machine names option allows the size of this table to be adjusted. NP MAX MACHINE NAMES = number Replace number with the desired number of Named Pipes servers. Minimum = 4 Maximum = 50 Default = 10 Maximum Open Named Pipes This option specifies the number of pipes that can be opened. This number is the total number of open pipes. NP MAX OPEN NAMED PIPES = number Replace number with the desired number of open named pipes. Minimum =4 Maximum = 128 Default = 4 Maximum Communication Buffers The NetWare DOS Named Pipes Extender requires communication buffers for transmitting data to and from the Named Pipes server. This option specifies the number of buffers. NP MAX COMM BUFFERS = number Replace number with the desired number of communication buffers. Minimum = 4 Maximum = 40 Default = 6 NOTE: You should specify a minimum of two buffers per open pipe; however, as the number of pipes increases, this number can be reduced. Running SQL Server in the NetWare Environment SQL Server v1.0 Installation The installation program for Microsoft SQL Server v1.0 was designed strictly for LAN Manager networks. You cannot use the setup program that accompanies the SQL Server v1.0 if you are installing on a non-LAN Manager network. You must perform manual installation of the SQL Server v1.0. You'll need at least 8MB of RAM and at least 20MB of free disk space on your hard disk. Here's how to do it. 1. Create the SQL Server directory structure by creating the following directories (use the appropriate drive letter): C:\SQL C:\SQL\BINP C:\SQL\DATA C:\SQL\DLL C:\SQL\INSTALL C:\SQL\LOG 2. Copy the SQL Server files from the OS/2 release diskettes. o Copy all .EXE and .HLP files to the C:\SQL\BINP directory. o Copy DBLIBP.DLL and MSHELP.DLL to the C:\SQL\DLL directory. o Copy all .SQL files to the C:\SQL\INSTALL directory. Don't worry about the .C, .H, and .LIB files for now. They are part of the DB-Library support package, and can be installed directly on a workstation where C language programming will be done. 3. Create the SQL Server executable. The SQL Server is too big to fit on one diskette. You must concatenate two SQLSERVx.EXE binary files into one. To do this, change to the C:\SQL\BINP directory, then type: COPY SQLSERV1.EXE /b + SQLSERV2.EXE SQLSERVR.EXE /b You may delete the component files once they are joined. 4. Modify your PATH and LIBPATH variables. Add C:\SQL\BINP to your OS/2 PATH statement and C:\SQL\DLL to our LIBPATH statement in CONFIG.SYS. Reboot if necessary to allow the changes to take effect. 5. Create the SQL Server master database device file. Change to the C:\SQL DATA directory. This is where you will create the SQL Server master database device (an OS/2 file which contains SQL Server databases). Issue the following command: BLDMASTR /dMASTER.DAT /s5120 /C The first parameter is the name of the file and the second parameter is the size in 2KB pages (5,120 is the minimum size but you may use a larger number if you like). Note that switches for the SQL Server utilities are case sensitive (for example, /d means something different from /D). The /C switch instructs BLDMASTR to create a server that is not case sensitive (the default is case sensitive). This cannot be changed later. Omit this switch if you want to have a case sensitive database. 6. Start the SQL Server. You are now ready to start the SQL Server for the first time. It must be running to complete the remainder of the installation. From the OS/2 prompt, type (on one line): SQLSERVR /d C:\SQL\DATA\MASTER /e C:\SQL\LOG\ERROR.LOG Remember to provide the complete path names to the master database device and error logs. For more information on starting and stopping the SQL Server, see "Operation Issues" after the following section. 7. Build the Master, Model, and Pubs databases. Run another copy of the OS/2 command processor (the current screen group should be tied up by the SQL Server). Change to the C:\SQL\INSTALL directory. You are now ready to execute the TRANSACT-SQL build scripts that create the complete SQL Server database environment. From the OS/2 prompt, issue the following commands in this order: ISQL /Usa /P < INSTMSTR.SQL ISQL /Usa /P < INSTMODL.SQL ISQL /Usa /P < INSTPUBS.SQL These commands will each take a few minutes to execute. NOTE: If you are performing a remote install, it may be necessary to specify the name of the server on the ISQL command line. Use the /S switch to do so. For example, if the name you passed to the NPDAEMON.EXE program in the CONFIG.SYS was SQL_SERVER, use the following command line to run the INSTMSTR.SQL script: ISQL /Usa /P /SSQL_SERVER < INSTMSTR.SQL Running SQL Server in the NetWare Environment SQL Server v1.1 Installation Installing SQL Server v1.1 into a NetWare environment is made much easier by the use of the SQL Server SETUP program v1.1. In a normal installation, SETUP still assumes the existence of a LAN Manager environment, and will attempt to install SQL Server as a LAN Manager service. At the beginning of the SETUP routine, indicate that you want to install the database itself. You will then be asked if you want to install SQL Server on a network or on a standalone system. To install the SQL Server 1.1 on a non-LAN Manager LAN such as NetWare, you must select the standalone install option. Standalone installation of the SQL Server is fully documented in the Microsoft SQL Server Installation Guide. Running SQL Server in the NetWare Environment Operation Issues Starting and Stopping the SQL Server You can start the SQL server either directly from the OS/2 command line or by adding the SQL Server as a program item under the OS/2 Program Manager. Remote starting and stopping of the SQL Server is not possible due to the lack of the LAN Manager NET START and NET STOP commands. The SQL Server can either occupy a screen group or be run as a detached process. To start SQL Server in its own screen group, use the following command: sql -d c:\sql\data\master.dat -e c:\sql\log\error.log To start SQL Server as a detached OS/2 process, use the following OS/2 DETACH command (typed on a single line): detach sqlservr -d c:\sql\data\master.dat -e c:\sql\log\error.log The -d parameter is the fully qualified pathname of the SQL Server Master database device, which is usually installed in the \SQL\DATA directory. This is the bootstrap device that contains the SQL Server master database as mentioned earlier in the SQL Server v1.0 installation procedure. The -e parameter is an optional path name of the error log. If this parameter is not supplied, output generally goes to the display (this is not visible when the SQL Server is running as a detached process). To stop the SQL Server, you can use the SAF menu option or the TRANSACT-SQL SHUTDOWN command. To create an OS/2 command file which will stop the SQL Server automatically, first create the following TRANSACT-SQL script file with a text editor. SHUTDOWN GO Name this file SHUTDOWN.SQL and save it in the \SQL\BINP directory. Then create the following OS/2 command file: @ECHO OFF ISQL -Usa -P, 4. Press at the title screen. 5. Type the name of your SQL Server and press . (Note: This is the "computer name" given at the time the NPDAEMON.EXE is executed in the CONFIG.SYS file.) "computer name" 6. Type the System Administrator user name: user name 7. After the workstation attaches to the SQL Server, select the PUBS database by entering: USE PUBS 8. Clear the screen by entering: 9. Enter a query. For eample: SELECT * FROM AUTHORS 10. View the results of the query by typing: 11. Exit the SAF by executing the following keystrokes in this order: Running SQL Server in the NetWare Environment Summary With the introduction of SQL Server v1.1, Microsoft has made a conscious decision to separate the database functions from the network functions. Unlike SQL Server v1.0, SQL Server v1.1 loads the network protocol functions as a separate dynamic link library (DLL). This DLL implementation paves the way for developers to use network interfaces other than Named Pipes (such as Novell IPX/SPX). Novell will continue to support industry-standard interfaces such as Named Pipes, NetBIOS, TLI/Streams, and sockets, allowing distributed applications based on these interfaces to function in a NetWare environment. Running SQL Server in the NetWare Environment Appendix A: Bibliography Books Microsoft LAN Manager: A Programmer's Guide version 2, Microsoft Corp., 1990 Microsoft LAN Manager: Programmer's Reference version 2, Microsoft Corp., 1990 A Technical Overview of SQL Server, Advanced Consulting Group, Ashton-Tate 1988 Technical Documents Using Microsoft SQL Server on a NetWare Network, Microsoft Corp., 1989 Installing SQL Server 1.0 Under Novell NetWare, Ashton-Tate Professional Services Division, 1989 Articles Bolt, Robert, "Battle of the DataBase Servers", LAN Technology (January 1991) Editor's Note: The author accepts written feedback at FAX (801) 429-5511.