>I have a large client base using my dos accounting software package. I have been rewriting this package using VFP for the last couple of years. I have stores using my software that range to having 1 stand alone machine to stores that have 40 computers on a Novell network to Stores using citrix across a wan of 8 stores consisting of close to 100 computers.
>
>Right now I have been developing my package with just the base VFP tables.
>
>Will this work for all of my clients? Should I be looking into some kind of backend server? If so is there a general rule of thumb of how many computers will work with just the VFP tables as opposed to a backend server?
>
This largely depends on what you expect to gain from going to a backend database engine. If you're finding that lots of data is travelling over the wire, and things noticably slow down as traffic increases, moving to a backend can reduce the amount of network traffic by limiting what is moved to requests and results.
Other issues may drive the decision. Is data security or integrity a large issue? VFP native files must be readable to be usable at all - and that means that the can be copied. The OS doesn't have a native mechanism to limit access to a portion of a file - if it's readable, it's readable. A backend can provide finer granularity for access control, and direct access for copying the data is much more limited.
Most backends offer transaction logging and journalling - this can make it easier to back out accidental changes, improves the ability to audit access and gives stronger accountability for data.
Backends can improve integrity - for one thing, workstations don't directly perform their file I/O, so the risk of somethign dying in the midst of actively altering the files is reduced. Also, the system records what it plans to do before doing it, does it and confirms completion. By keeping a running log of changes made to the data, the results of a failed disk drive are reduced - if you have to go back to the last backup and the transaction journal is intact and accessible, you can reapply the recorded changes andspend less time reentering stuff.
Many added services such as OLAP may make it easier for users to view data in the database in ways you haven't planned for using tools like Excel. Some programs that don't like VFP native formats may well be happier talking to SQL Server, too.
Sounds great. Now the drawbacks. Top of the list - cost. You pay per seat, or spend big bucks per server on a site license. Server hardware requirements generally demand faster systems with more RAM and better disk subsystems. Plan on spending on some fault-tolerance for the server, too - right now, if your PC dies, you can run down the hall to Sally's and as long as the LAN is up and file server is functioning, you can work from there. If the backend database fails, everyone is down.
Backends require care and feeding beyond what a native VFP file system needs. Someone has to fill the role of database administrator, doing things like managing access rights, performing routine maintenance tasks, monitoring logs, etc. If you use SQL Server, you commit to using NT Server or Win2K Server for the platform - if this is a Novell shop, someone needs to know about the care and feeding of NT Server, too, and additional network protocols and clients may be needed. If the site runs peer-to-peer, then figure that someone needs to learn about network administration in general as well as NT Server operation.
In all probability, your code will need to change to work with a backend; how much impact this will have depends largely on how you work now. At a minimum, there is another language involved in programming stored procedures on the backend, and new tools will be involved in creating and maintaining the database schema as well. If you now operate against base tables, use filters a great deal, write queries that rely on internal VFP functions rather than SQL constructs and rely on quirky little VFP-specific tricks for handling files, the work is significant. Many of the handy-dandy commnd box data fixers you've developed over time may not work.
If lots of involved queries are submitted simultaneously that are processor-intensive, the server can become a bottleneck for the system. Moving to a faster system with more memory, or adding CPUs and memory, or adding servers and clustering them may be necessary to fix the problem. If your problems in performance are related to the speed of the processor and not network traffic or disk bandwidth, you need to keep this in mind when specing the server.
Not all of the application needs to use the backend; I've left many configuration files in native form, as well as lookups not used for primary database functions. Once I've pulled things down to the station, I build temporary cursors, indexes on the fly and other xBASE-ish things to make the VFP UI easier to maintain. I have several apps where only a few of the central, critical tables reside on the backend.
