Copyright: © Simon Bates


Help Contents
Contents

Introduction

The Database Options

What is a Client Only Database?

What is a Client/Server Database?

So What is the Difference?

So Why Use a Client Only Database?

When Should I Consider Client/Server?

Can I Use JET Now and Migrate Later?

Installing SQL Anywhere as your Database Platform

Server Installation

Client Installation

Installing SQL Anywhere Server in a Mixed 16-bit and 32-bit Environment

Running SQL Anywhere Server on a Novell Netware Server Machine

Migrating from Jet to SQL Anywhere

Converting from OfficeTalk 1.5x to OfficeTalk 98 using SQL Anywhere


The Database Options

OfficeTalk has been designed so that it may be used with different types of databases as its means for storing data. This means that you may decide which database platform is right for your organisation.

OfficeTalk will work with the following databases:

Database	Type
• JET	Client only
• Sybase SQL Anywhere	Client/Server
• Microsoft SQL Server	Client/Server
The factors which influence your decision are discussed in the sections that follow.


What is a Client Only Database?

In the table above, the JET database is described as a Client only database. A Client only database is simply one or more files which store data. The files are always located on a shared file system to which everyone has access. All computers, running OfficeTalk, will share the same database files.


What is a Client/Server Database?

In the table above, the Sybase SQL Anywhere and Microsoft SQL Server databases are described as Client/Server databases. A client/server database is simply a file, however, all accesses to the database file are made by one computer (the database server). All computers, running OfficeTalk, will read and write data to and from the database by making the appropriate requests to the database server. The database server directly accesses the database file and sends only the results of the client requests back to the client.


So What is the Difference?

The main difference is network traffic! Client only databases, such as JET, rely on each client to access the database file directly over a shared file system. This can be network intensive, and network traffic will increase proportionally to the number of clients.

A client/server database, such as Sybase SQL Anywhere causes far less network traffic since all file accesses to the database file are done by the database server and the database file will be located on the local drive of the database server. Only the database requests and the request results are transmitted across the network and this is usually done in an efficient way by using network protocols, such as TCP/IP.

Additionally, in the case of a client only database, when a user changes data in the database, a small part of the file is locked for the duration of the change. During this time other users will be prevented from making changes to this part of the file. In the event of two or more users attempting to change the same record at the same time, OfficeTalk will automatically try to re-apply the database changes, a few moments later, for those users that failed to change the record. Usually, the users will not be aware of these database locking issues, however, for large numbers of users, the problem may become more significant.

A client/server database also uses database locking, however since all locking is handled by the one database server, it is more robust and creates far less network traffic.


So Why Use a Client Only Database?

A client only database is simple. It is straight forward to install and straight forward to administer. Furthermore it is a low-cost solution. Provided you have a local area network with a reasonable bandwidth, a client only database will be as reliable as your file system. Furthermore, a client only database does not require an additional machine to be set aside as the database server.

The JET database, which is installed as standard with OfficeTalk 98 has a maximum limit of 128 concurrent users. Depending upon the usage of OfficeTalk within your organisation and the bandwidth of your local area network, JET will work comfortably with up to 20 users.


When Should I Consider Client/Server?

If you have in excess of 20 OfficeTalk users or you have a slow network, then you should consider adopting a client/server database.

OfficeTalk 98 comes supplied with Sybase SQL Anywhere which is a scaleable and robust client/server database.


Can I Use JET Now and Migrate Later?

Yes. OfficeTalk is supplied with a migration tool which will let you convert your JET database to a Sybase SQL Anywhere database at any time. The procedure for doing this is described later on in this document.


Server Installation

Begin the OfficeTalk installation in the normal way, as described in the on-line Help or the User Manual. After the License page, the Database Platform page is displayed. From the Database Platform page of the setup wizard, choose the Sybase SQL Anywhere (client/server) radio button and press Next.

The SQL Anywhere Options page is displayed. Choose the database platform which will host the SQL Anywhere Server.

If you plan to run the SQL Anywhere Server on a Windows 95/98 or NT machine then select the Windows 95/98/NT radio button. If you plan to run the SQL Anywhere Server on a Novell NetWare server then select either the Novell 3.x or Novell 4.x radio button, depending upon the version of Novell NetWare that you are running.

If you selected to run the SQL Anywhere Server on a Windows 95/98 or NT machine then you must enter a directory to which SQL Anywhere Server files will be copied. Do this by pressing the [image] button.

Pressing Next will display the Optimisations page. This page lets you choose various settings which may optimise your SQL Anywhere Server.

More:

Choosing the Transport Protocols

Advanced Options


Choosing the Transport Protocols

By default, SQL Anywhere Server for OfficeTalk will use TCP/IP as the transport protocol for Windows 95/98/NT installations and IPX for Novell Netware installations. If you are running SQL Anywhere Server on a Novell Netware machine over IPX, then you should ensure that the IPX check box is checked. If your network runs neither TCP/IP nor IPX then you can choose to use NetBIOS as your transport protocol, but this is not recommended for large user installations.


Advanced Options

Additionally, you may choose the number of threads the server uses when in operation. You should specify a number of threads equal to the number of users in the Workgroup, up to a maximum of 100 threads. The default is 20 threads. You may also choose whether or not to use an increased packet size. You can do this by checking the Use increased packet size check box (default). This can improve the performance of your SQL Anywhere Server, but will lead to a very slight increase in network traffic.

If you chose to install the SQL Anywhere Server onto a Novell NetWare server machine then pressing Next will display the Novell Server Directory page. From this page, choose the location on the Netware server to which the SQL Anywhere Netware files will be copied.

Pressing Next will display the Database page. From now on, follow the instructions in the on-line help or the User Manual which will guide you through the remainder of the Server installation.


Client Installation

The client installation for SQL Anywhere is as described in the on-line help or User Manual. Run Setup.exe from the Setup32 sub-directory of your chosen database directory and let the wizard guide you through the remainder of the client installation.


Installing SQL Anywhere Server in a Mixed 16-bit and 32-bit Environment

If you wish to install SQL Anywhere Server in a mixed environment of 32-bit and 16-bit clients, you should do the following:

•	Run the SQL Anywhere Server installation on a 32-bit machine or to a Novell Netware server as appropriate. This will install the SQL Anywhere Server, create the database directory and the Setup32 sub directory. 32-bit clients may be installed by running Setup.exe from within the Setup32 sub directory.

•	Run the 16-bit Setup.exe from the installation media. This will simply create the Setup16 sub directory from which 16-bit clients may run Setup.exe.


Running SQL Anywhere Server on a Novell Netware Server Machine

Once the Server installation has installed SQL Anywhere to a Novell Netware server, you must load the SQL Anywhere NLM. You can do this by typing the following at the Netware Server console:

load dbsrv50 –c 20M –n OTDB x:\x\x\otsqldb.db

where 20M is the cache size (20 megabytes), OTDB is the name of the SQL Anywhere Service and x:\x\x\ is the full path leading to the database file.

Alternatively, add this command to the AUTOEXEC.NCF to automatically load the SQL Anywhere NLM every time the Netware Server boots.

If the command line exceeds 255 characters, then you must create a text file containing the parameters. For example,

–c 20M

–n OTDB

x:\x\x\otsqldb.db

should be saved to a file called x:\CONFIG.TXT (where x is an appropriate drive letter). You should then type the following at the Netware console:

load dbsrv50 @x:\CONFIG.TXT

More:

Other Database Server Switches


Other Database Server Switches

The database server switches that may be specified along with the load command are described below:

-c  cache-size	Sets the size of the cache. The database server uses extra memory for caching database pages if it is set aside in the cache. Any cache size less than 10000 is assumed to be kilobytes. Any cache size 10000 or greater is assumed to be in bytes. The default is 2 megabytes.
-gn  num	Sets the number of execution threads that will be used in the database server while running with multiple users.
-n  name	The database name.
-p  packet-size	Set the maximum size of communication packets. The default is 512 bytes. The minimum value is 200 bytes.
-x  list	Comma separated list of communication links to provide, e.g. -x TCPIP,IPX.  If this option is omitted, the default is to try all settings supported by the database server on your operating system.


Migrating from Jet to SQL Anywhere

Once you have installed the SQL Anywhere Server, you can migrate an existing JET workgroup database to a SQL Anywhere database in the following way:

1.	Install the AcToSSA (Sybase Access Migration Wizard) by running the 'migratewiz.exe' file (provided on your installation media) and extracting to a temporary directory, then running Setup.exe from within that directory.

2.	Make a copy of your current JET database 'otdb.mdb' into the location you specified for the SQL Anywhere database in the SQL Anywhere setup.

3.	Rename the copied file to 'otsqldb.mdb'.

4.	Start the Migration Wizard program by selecting the 'AcToSSA' item in your Start/Programs menu.

Clicking on the [image] button will show the Select the MS Access DB to Migrate page.

Browse for the otsqldb.mdb file and click on [image]. The SQL Anywhere's ISQL Selection page is displayed.

Browse for the isql.exe file, located in the SQL Anywhere installation directory (by default C:\Program Files\OfficeTalk 98 SQL Anywhere\isql.exe) and click on [image].

The next page shown is the Create a SQL Anywhere Database page.

Click on the CreateDB button to create the new database. If you are prompted whether or not to overwrite the existing file, select Yes and wait for the database to be created, then click on [image] to proceed.

You must now start the OTDB SQL Anywhere Database Server.  On Windows NT do this by starting the OfficeTalk SQL Anywhere service in the Services control panel applet.  On Windows 95 click on the Start OfficeTalk Database Server item in the OfficeTalk 98 SQL Anywhere program group within the Start Menu. If running Netware, load the Sybase SQL Anywhere NLM. A typical command would be load dbsrv50 –c 20M –n OTDB x:\x\x\otsqldb.db, where 20M is the cache size, OTDB is the name of the SQL Anywhere Service and x:\x\x\ is the full path leading to the database file.

Once the server has started and 'Now Accepting Requests' has been displayed in the Messages window of the OTDB Server monitor program, click on the Migrate button in the Migration Wizard and, when prompted, select the OTDB data source.  If this is not in the page displayed it will be in the Machine data source or System data source page.

This will have started the conversion.  When complete, the window displaying the conversion status will close and you will be returned to the Migration Wizard. Click on Finish.

From the Start Menu select Run and browse for the modify.bat file located in the SQL Anywhere installation directory (by default C:\Program Files\OfficeTalk 98 SQL Anywhere\modify.bat). Select and run this file. After the Isql window is displayed and subsequently closes, the conversion will be complete. You may receive "error messages" stating that files are not present. This is normal behaviour. Press the Continue button that is displayed each time.

The JET database has now been converted and you are ready to install and run OfficeTalk on client machines.


Converting from OfficeTalk 1.5x to OfficeTalk 98 using SQL Anywhere

If you are upgrading from OfficeTalk 1.5x to OfficeTalk 98 and you wish to convert directly to SQL Anywhere, then ensure that the Upgrade v1.x from CMP file radio button is selected in the Upgrade page of the Setup wizard.

At the end of the server installation, depending upon the server platform, you will either be asked to start the SQL Anywhere Server prior to the database conversion, or Setup will start the server automatically.

For more information on converting your OfficeTalk 1.5x workgroup database, refer to the on-line help or the OfficeTalk User manual.