Installing and Configuring Pervasive.SQL 2000
for WebApp™ Server

A Data Access Worldwide White Paper
by Marcia Ferreira

March 2001
Last Edited: April 19, 2001

Overview

The main objective of this White Paper is to list all the installation steps that one needs to go through on both Server and Client (using Windows NT), and some configuration involved when preparing Pervasive.SQL 2000 version 7.82 to be the backend database of a WebApp Application.

Contents

1. Pervasive.SQL

   ·         Installing Pervasive.SQL Server

   ·         Installing the Pervasive.SQL Client

   ·         Testing Client/Server Communication

   ·         Configuring Pervasive.SQL Server Environment

    

2. WebApp Server

   ·         Installing WebApp Server

   ·         Installing the Connectivity Kit for Pervasive.SQL

   ·         Converting Data Files to Pervasive.SQL

   ·         Preparing WebApp Server to Access Pervasive.SQL Database

   ·         Testing a Sample from the Studio and from a Browser

    

3. Database

   ·         Creating a Database on the Server to be Accessed through the Control Center

   ·         Registering an Existing Server

   ·         Creating a Client Interface to Access a Database on the Server

    

4. Troubleshooting

   ·         Common Problems and Error Messages

   ·         Monitor Utility

   ·         Tracking Down a Problem

    

Pervasive.SQL

Installing Pervasive.SQL Server

Important Notes:

1. Before installing any Pervasive.SQL component, check the System Requirements for both Server and Client machines.

2. All product installation should be performed using your Administrator account

Pervasive.SQL Server should be installed from the product CD. The version of Pervasive.SQL used as a reference in this White Paper is 7.82 (Pervasive.SQL 2000 Service Pack 2).

 To install Pervasive.SQL Server, observe the instructions on the screen and the ones noted in the Getting Started with Pervasive.SQL book.

 Note: Your Pervasive.SQL Server needs to be registered using the diskette with your registration code

^ top  

Installing the Pervasive.SQL Client

If you installed Pervasive.SQL to the default location, the directory C:\PVSW\CLIENTS is created. This directory should be used as the source for Client installations.

The machine where you will install WebApp Server will need to have the Client engine installed. It is not recommended that WebApp Server and Pervasive.SQL Server reside on the same machine.

 Note: Client machines need to have access to the installation directory on the Server

 At the Client machine:

• From the Start Menu, select the directory where the files are located on the Server

• Select Setup.exe and press OK

• Follow the instructions on the screen and refer to Pervasive.SQL 2000 – Getting Started with Pervasive.SQL book for more information

^ top  

Testing Client/Server Communication

During the installation process, a standard test will be performed to check your communication with the Server using both Transactional and Relational engines. You can rerun those tests after the installation by using the Install Scout utility.

To run the Install Scout utility:

• From the Programs menu, select Pervasive

• Select Pervasive.SQL 2000 / Utilities / Install Scout

• Follow the instructions on the screen – the results will be displayed after each step and a log file will be created with more detailed information

Note: The user account that will be used to access your Pervasive Server is Guest. Make sure the Guest user account is enabled on your Pervasive Server machine

Configuring Pervasive.SQL Server Environment

To work with your application and WebApp Server, some configuration settings should be changed in Pervasive.SQL 2000 Server. The settings can be adjusted using the Control Center utility installed with Pervasive.SQL.  

Control Center

To open the Control Center:

• From the Programs menu, select Pervasive

• Select Pervasive.SQL 2000 / Utilities / Pervasive Control Center

• A tree view will be displayed

The configuration of the Server can be accessed by expanding the tree under the respective Server Name:

• Under Pervasive.SQL 2000 Engines, the available Servers will be listed

• Click on the Server Name

• Double-click on the Configuration icon and the various configuration groups will be displayed.

The groups can be displayed in two different ways: Function View (default) or Component View.

The Function view will present 9 folders: Access, Communication Buffer Size, Communication Protocols, Compatibility, Data Integrity, Debugging, Directories, Memory Usage and Performance Tuning. Each folder contains the current setting for each configuration item.

The Component View can be selected by clicking Component View on the View menu. Three folders will be displayed: SQL Connection Manager, MicroKernel Database Engine and Btrieve Communications Manager. The configuration items will be organized in separate folders under each one of the three afore-mentioned folders.

Configuration Items

The main setting to be changed when working with WebApp Server is the Number of Sessions. The default in Pervasive.SQL 2000 is 15 and it should be changed to the number of concurrent sessions (network connections that can access the server at any given time) you desire to handle. The upper limit is dictated by the system memory – the memory required is 32KB per session.

This configuration item can be found in the Access folder, if you are using the default view, or under Btrieve Communications Manager / Server Communication Configuration, if you have the Component View active.

Other settings need to be verified and be set at the level your application requires. Some of them are:  

Maximum Open Files – default value 10,000

Number of different files that the MicroKernel can have open, including Data Dictionary Files (DDFs).

This configuration item can be found in the Access folder, if you are using the default view, or under Btrieve Communications Manager / Server Communication Configuration, if you have the Component View active.  

Active Clients – default value 10,000

Specifies the number of Clients that can access the MicroKernel at the same time. Each MicroKernel-based application represents a Client.

This configuration item can be found in the Access folder, if you are using the default view, or under MicroKernel Database Engine / System Resources/Directory, if you have the Component View active.

Logical File Handles – default value 100,000

Product of Maximum Open Files x Active Clients. This should include multiple file handles to the same physical file in case the same file is open by different applications/Clients.

This configuration item can be found in the Access folder, if you are using the default view, or under MicroKernel Database Engine / File Settings, if you have the Component View active.

Cache Allocation Size – default value 26,096KB

Cache the MicroKernel uses for all data file accesses. Allocate a cache size (multiple of 16KB) no larger than the sum of the sizes of the files you are using.

This configuration item can be found in the Performance Tuning folder, if you are using the default view, or under MicroKernel Database Engine / Memory Resources, if you have the Component View active.

Largest Compressed Record Size – default value 5KB

Memory (2,048 bytes) allocated for compression buffer – either used by compressed files or 7.0 version files with record length greater than 4,076 bytes. The recommended value for this item is 16KB, since this is DataFlex’s record size

This configuration item can be found in the Performance Tuning folder, if you are using the default view, or under MicroKernel Database Engine / Memory Resources, if you have the Component View active.

Make sure the following configuration items are set to ON:

At the Server: Accept Remote Requests – default value On

Enables the Btrieve Communications Manager to accept remote requests.

This configuration item can be found in the Access folder, if you are using the default view, or under Btrieve Communications Manager / Server Communication Configuration, if you have the Component View active.

At the Client: Requester – default value On

Specifies whether the MicroKernel Router will allow access to a MicroKernel Server Engine running on a remote server.

This configuration item can be found in the Access folder, if you are using the default view, or under MicroKernel Router / Access Control, if you have the Component View active.

Note: For your changes to take effect, you must shut down and then restart the Pervasive.SQL components at the Pervasive.SQL Server. This can be achieved from within Control Center or from your NT Services panel.

From the Control Center:

• Start Control Center

• Expand the tree until you see the name of the Pervasive server

• Right-click on the server name

• Select Tasks and then Restart Pervasive Services

From NT Services panel at your Pervasive.SQL Server machine:

• Open NT Services panel (from the Control Panel)

• Look for the name of the services: Pervasive.SQL 2000 (relational) and Pervasive.SQL 2000 (transactional)

• Highlight the services (one at a time) and press Stop

• Highlight the services (one at a time) and press Start

• Close the Services panel

^ top

WebApp Server

Installing WebApp Server

Install WebApp Server on the machine that will handle the browsers’ requests and that fulfills all the pre-requisites specified in the WebApp Server Installation & Environment Guide.

After installing WebApp, restart your computer so that the WebApp Server service can be automatically started.

Note: If you have a previous version of WebApp already installed, you will need to uninstall it. Make sure you use the WebApp Administrator to stop the service before you uninstall it.

^ top  

Installing the Connectivity Kit for Pervasive.SQL

The Connectivity Kit needs to be installed on the same machine that will run your applications and to which you installed the Pervasive.SQL Client. In this case, the application will be on your WebApp Server machine.

To install the Connectivity Kit on the WebApp Server machine:

• Run the setup program to install the Connectivity Kit

• On the ‘Select Components’ screen, make sure you check the ‘Driver for Visual DataFlex’ box

• When the installation prompts you for the directory where VDF is installed, point to the directory where you installed WebApp Server

• Follow the instructions on the screens

• At the end, register the Connectivity Kit

Note: During installation you will be asked if a copy of the set of DDF files should be placed in your ‘Usr\New’ directory. Place a set there if you want to have DDFs present at all new workspaces you create.

^ top 

Converting Data Files to Pervasive.SQL

To be able to successfully convert the files to the Pervasive.SQL Server machine you will need first to:

• Create a directory on the Pervasive.SQL Server machine where the files will be created

• Share that directory, giving rights to it to the users or groups that will be accessing the files (including the user used by WebApp Server service log on)

• Map a network drive to the machine running the Pervasive.SQL Server (your path length cannot be longer than the length VDF can handle - 40 characters long)

• Copy the data files you want to have converted to the directory you created on the Pervasive server machine (you can leave your filelist in your local directory)

• Change your workspace DataPath registry entry to point to the directory now holding the files on the Pervasive server

The conversion process may be done through Database Builder:

• Start Database Builder

• Select ‘Load Database Driver’ from the Database menu

• Double-click on DFBTRDRV.dll – locate in the Bin subdirectory from where WebApp Server was installed

• Select ‘Convert to Btrieve’ from the Database menu

• Check the boxes for the files you want to convert and click ‘Convert’

• Adjust the conversion options and press OK, the conversion process will start

^ top

Preparing WebApp Server to Access Pervasive.SQL Database

To start using Pervasive with WebApp Server you will need to adjust the following items:

• Windows NT registry on the WebApp Server machine

• WebApp Server service Log On information

• Workspace DataPath

• Number of Sessions on the Pervasive.SQL Server

The first thing that needs to be changed is the entry HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\SubSystems (Notice that there is also a SessionManager key with no space in its name, but the one that we need to adjust is the one with a space between Session and Manager) in the Windows NT registry from the machine where WebApp Server is installed.

To change that entry in Windows NT:

• From the Start menu, select Run

• Type REGEDIT and press OK

• Expand the tree until you find the afore-mentioned entry

• Look for an entry called Windows on the right panel and double-click on it

• Look for "SharedSection=1024,3072,512" and modify 512 to 3072

• Click OK and exit REGEDIT

• Restart your computer

Note: For more information on this change, read the Knowledge Base article 1158 at http://www.dataaccess.com/KBPrint.asp?ArticleID=1158

The second thing that needs to be changed is the WebApp Server service Log On information. The default log on is set to run the service under ‘System Account’. That needs to be changed to a user with enough rights to run the service and that has access to the shared directory where Pervasive files are located.  

To change the Log On information:

• Start WebApp Administrator

• Select ‘Stop Service’

• Select Control Panel from Settings in the Start menu

• Double-click on Services

• Scroll down till you find ‘WebApp Server’ and highlight it

• Click on the Startup button

• In the Log On area, check ‘This Account’, the fields for entering the UserName and Password will be available

• After entering both UserName and Password, click on OK and then Close the Services panel

• Start WebApp Server service from the WebApp Administrator

The third item to be changed is the DataPath. It needs to be changed to the corresponding UNC name pointing to the Pervasive.SQL Server path where the data resides.

To change the DataPath:

• Start Database Builder

• Select ‘Modify Workspace’ from the Workspace menu

• Expand the Workspace key and highlight the name of the workspace being used

• Double-click on DataPath

• Change the path to the corresponding path using UNC name

• Press OK and then exit DataFlex System Explorer

The fourth item that needs to be changed is Pervasive.SQL Server Number of Sessions configuration item. The default value is set to 15. Adjust this number to a more appropriate one.

To change the Number of Sessions on the Pervasive.SQL Server:

• At the Pervasive.SQL Server machine (or from a Client machine that has Control Center installed), start Control Center

• Expand the tree until you see Configuration under the name of the database server you need to configure

• Expand the tree again where you read Server

• Highlight Access

• On the right panel, double-click on Number of Sessions

• Enter the new value and click OK

• Close Control Center

• When you are prompted to save the changes, press Yes

• Shut down and then restart the Pervasive.SQL services so that the change can take effect

 ^ top

Testing a Sample from the Studio and from a Browser

In order to make sure all your components are functional and communicating correctly, it is a good idea to use one of the sample applications that come with WebApp for a test.

To test a sample at the WebApp Server machine:

• Make sure you get all the steps described above done to a specified sample (e.g.: WebAppSample)

• Open the Studio and compile the application

• Run the application from the Studio

• Close the Studio

• Open your browser

• Type in the address to get to your website (e.g.: http://localhost/webappsample)

If the steps above can be performed without errors, it means your installation is functional and should work properly when you try to run your own applications. If any problems occur, refer to the next section of this White Paper, Troubleshooting.

^ top

Database

Creating a Database on the Server to be Accessed Through the Control Center

Once you have your DDFs and Pervasive files created on the server, you can create a database in Control Center in order to have access to the tables and manipulate them using SQL statements.

To create a database at the Server:

• At the Pervasive.SQL Server machine, start Control Center

• Expand the tree under Pervasive.SQL 2000 Engines

• Expand the server name

• Right-click on ‘Databases (Engine)’

• Select ‘New Database’

• Enter a name for the database you need to create

• Locate the directory where you have the DDFs and Pervasive files

• If you need to change the Database description or adjust the DSN settings*, check ‘Use advanced settings’ checkbox

* You will be allowed to change the DSN Type (System or User) and Open Mode (Normal, Accelerated, Read Only, Exclusive)

• Click Next until you reach the ‘Completing Create Database Wizard’ screen

P.S.: If you checked ‘Use advanced settings’, you will be displayed an extra panel where you enter your options

• Validate your database definition and click Finish to resume the database creation

• Click Close – and your database will be listed under ‘Databases (Engine)’ tree

 Note: You can create a new database when you still don’t have the DDFs or Pervasive files. You will need to select the ‘Use advanced settings’ checkbox and click on the ‘Create’ button right next to the Database Name combobox on the Advanced Options screen. If no DDFs are found, empty ones will be created for you. If you already have a set of DDFs and Pervasive files, they will be added to your database.

Important!

If you don’t have DDFs in this directory they will be created empty, regardless of whether you have Pervasive files in the directory or not. To add the files to the created DDFs and see them displayed under Tables in your database you will need to use the ‘Use advanced settings’ checkbox from ‘New Table’ option.

^ top

Registering an Existing Server

To have access to an existing Pervasive Server and the databases created on it, you need to register the server from within Control Center.

To register your Server:

• At the Pervasive.SQL Client machine, start Control Center

• Right-click on Pervasive.SQL 2000 Engines

• Select Register New Engine

• Type (or select) the computer name that holds the server you need to register

• At this point you can test the connection to your server. Press Test to perform the test or OK to finalize the registration process

Note: You may be prompted for a login and password. These should be valid user and password on the Pervasive Server machine

^ top

Creating a Client Interface to Access a Database on the Server

To access the databases created on the Server from your Client machine you need to add a Client Interface from within Control Center.

To add a database to a Client machine:

• At the Pervasive.SQL Client machine, start Control Center

• Expand the tree under Pervasive.SQL 2000 Engines

• Expand the name of your local machine

• Right-click on ‘Databases (Client)’

• Select ‘New Database’

• Select the Server name and make sure you have Client selected in the Interface combobox

• Enter a name for the database and select the name of the database at the server that you want to add to your Client Control Center

• Click Next and validate your database definition

• Click Finish to resume the database creation

• Click Close – and your database will be listed under ‘Databases (Client)’ tree

Note: Every database you add using the Control Center will create a new DSN in your ODBC Administrator. Therefore, when accessing your database from the Client, use the DSN (database name) you specified when completing the wizard. The type of DSN and the database description can also be configured by checking the ‘Use advanced settings’ option presented at the second screen when adding a Client Interface.

^ top

Troubleshooting

Common Problems and Error Messages

The list below is only a summary of the most common problems you can face when trying to make Pervasive.SQL and WebApp Server work together. For a complete list of error messages, refer to the products’ manual.

Pervasive.SQL

Problem

The number of sessions being handled by Pervasive.SQL Server is too low

Explanation

The Number of Sessions setting on the Pervasive.SQL Server was not adjusted appropriately or your machine (memory) is not able to handle more than the number presented.

Adjust the configuration item on the Pervasive.SQL Server according to the instructions previously described in this White Paper or add more memory to your machine. The solution could also be a combination of the two.

Problem

Message “The application encountered a permission error” – error 94

Explanation

There could be a permission problem or a problem with your Transactional engine. Make sure all the items indicated by Pervasive in case this error happens are right: 

·        The application tried to open or create a file in a directory without the proper privileges. The MicroKernel does not override the network privileges assigned to users.

·         The designated server is in the server routing table, but your particular client is not logged into that server.

·         The system data source name (DSN) on the server has an error in the pathname to the data files.

·         A NetWare application tried to access a file using NetWare Runtime support with the given username. Specifically, one of the following situations exists regarding the supplied username:

·         The user is not a valid user on the NetWare Runtime server.

·         The user does not have the appropriate rights to access the file.

·         The username is ADMIN or SUPERVISOR. For security reasons, the MicroKernel does not enable you to use ADMIN or SUPERVISOR as a username when enabling NetWare Runtime support.

·         When using the Win32 Requester from a Windows NT/9x client machine to a NetWare server, you must use the same username for logging in to both the client machine and the NetWare server. You cannot be logged in to NetWare as SUPERVISOR or ADMINISTRATOR.

·         When using the Win32 Requester from a Windows NT/9x client machine using NetWare emulation to a Windows NT server, the server cannot use Microsoft File and Print Services for NetWare. This causes the requester to attempt authentication as though the server were a NetWare Runtime server.

·         It is recommended that you keep the default Requester setting of Yes on FPNW servers running Pervasive.SQL. You may receive a Status Code 94 if you change this setting to No when you are running the Btrieve Interface locally on the FPNW server and are using a local FPNW drive mapping or local FPNW UNC path.

 If your installation covers everything listed above and you are getting the error when running InstallScout, try uninstalling your Client (removing all components) and reinstalling it using the setup.exe that can be found in the Clients directory on your Pervasive.SQL Server.

WebApp Server

Problem

Message ‘Failed to access log file for web application <application name>’ in the Event Viewer

Explanation

The log file is corrupted.

You will need to stop WebApp Server service, delete the log file for that application and restart the service. Use WebApp Server Administrator to Stop and Start the service.

Problem

Message ‘Timed out waiting for VDF process’ in the Event Viewer

Explanation

This can be caused by different reasons, but one of them is that the data files are not accessible.

Try to run the application program (WebApp.vd7). If the message ‘Can’t open data file’ gets displayed, check that all data files are in the right directory on the server, that the files are not marked Read Only, that the DataPath is correct and that the data directory can be accessed from the WebApp Server machine.

Problem

WebApp Server service handles more sessions when ‘Interact with desktop’ is selected

Explanation

The registry entry on Windows NT was not adjusted.

Adjust the entry HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\SubSystems according to the instructions previously described in this White Paper.

DataFlex Connectivity Kit for Pervasive.SQL

Problem

Message ‘BTRIEVE: Filename bad FILE.DDF’

Explanation

The Btrieve files are not accessible.

Check your DataPath and the rights to the directory you mapped on the Pervasive.SQL Server.

Problem

Message: ‘Invalid character in Path’

Explanation

You probably have a filelist containing a long path for the file name.

Map a drive to the directory where you have your data files and change the DataPath accordingly before conversion.

^ top  

Monitor Utility

Pervasive.SQL has a set of utilities that could help you in detecting the source of a problem. The main utility is called Monitor

To start the Monitor utility at the Pervasive Server:

• From the Start menu, select Pervasive

• Select Monitor from the Utilities menu

From the Monitor you can see which files are open, what users are connected to the Pervasive Server, what the resource usage is and how the communication is being handled. All these options can be found under MicroKernel menu; they can help you to adjust settings on the server and detect where the bottlenecks you are experiencing are located.

^ top 

Tracking Down a Problem

To track down a problem, you can use the following steps as a guide – the order they are listed does not need to be the order you run through them:

• Check that all the services are started

• Verify that the user specified in the log on information for the services is the right one and the password is correct

• Confirm that the user specified for the services is valid, is enabled and has enough rights

• Check the shared directories and the permissions to them

• Verify that the application is enabled in WebApp Server Administrator

• Confirm that the directory is WebShared correctly (Alias used, Access to read and script)

• Make sure all files are in the correct directory on the server

• Check that the files (.BTR and .INT) on the Pervasive.SQL Server are not flagged Read Only

• Make sure you can connect to Pervasive.SQL Server out of WebApp (use the Install Scout utility described earlier in this White Paper)

• Verify that the files can be opened from within Control Center from both the Server and Client engines

• Verify that other Utilities are able to open the files on the Pervasive.SQL Server (e.g.: Database Builder and Database Explorer)

• Make sure the WebApp.vd7 program can run without errors (double-click on it)

• Check the Windows NT Event log using the Event Viewer and check the description/Event ID of any event recorded

• Make sure all pieces are correctly registered

• Make sure the problem is not a corrupt log file (stop WebApp Server service and try deleting WebApp.log file for that application and restarting WebApp service. When you restart the servivce a new log file will be created)

^ top

Data Access Worldwide
14000 SW 119 Ave
Miami, FL 33186
305-238-0012
Domestic Sales: 800-451-3539
Fax: 305-238-0017
email: sales@dataaccess.com
Newsgroup Server: news.dataaccess.com
Internet: http://www.dataaccess.com

Data Access Worldwide - Europe
Lansinkesweg 4
7553 AE Hengelo
The Netherlands
Telephone: +31 (0)74 - 255 56 09
Fax: +31 (0)74 - 250 34 66
Sales: info@dataaccess.nl
Support: support@dataaccess.nl
Internet: http://www.dataaccess.nl

Data Access Technical Support
800-451-3539 / 305-232-3142
email: support@dataaccess.com
Visit our Support Home page to see all of our Support options: http://www.dataaccess.com/support

Data Access Technical Knowledge Base    http://www.dataaccess.com/kbase
Many answers to technical problems can be found online in the Data Access Technical Knowledge Base. Here, you can access the same live data that Data Access Worldwide technical support and development staff use to enter and track technical articles. 

Copyright Notice
This document is property of Data Access Corporation. With credit to Data Access Corporation for its authorship, you are encouraged to reproduce this information in any format either on paper or electronically, in whole or in part. You may publish this paper as a stand alone document within your own publications conditional on the maintenance of the intent, context, and integrity of the material as it is presented here.

DataFlex is a registered trademark of Data Access Corporation.

NO LIABILITY FOR CONSEQUENTIAL DAMAGES
To the maximum extent permitted by applicable law, in no event shall Data Access Corporation be liable for any special, incidental, indirect, or consequential damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or any other pecuniary loss) arising out of the use of or inability to use any information provided in this document, even if Data Access Corporation has been advised of the possibility of such damages. Because some states and jurisdictions do not allow the exclusion or limitation of liability for consequential or incidental damages, the above limitation may not apply to you.