[6/3/08: From an old article we use with our clients. I have additional notes and will update as I have time.-MP] 

[3/24/09: Added a "Use Most Current BDE" under the BDE standards.-MP]

Should you stay with Paradox tables?

A few recommendations from Prestwood IT.

First off, should you use Paradox tables for your data? Our position on this is that you can use Paradox tables safely and effectively but if your data is valuable to you, you may want to move it to a better data storage solution or at least make sure you have implemented robust backup and restore procedures and implemented the best practices in this article. Paradox is a file based system and can be very fast. File based databases are still valuable and actively developed and marketed.

Recommendation 1: Business Database Applications
A business database application is a multi-user application with 2 or more users (A.K.A. an enterprise application). If your data is important to you, we recommend migrating your data to an SQL database or at least start planning on it. Our SQL database of choice currently is MS SQL Server 2005. We've also used many other databases with great success including SQL Server 2000, Interbase, and Oracle.

Specific Recommendations from Prestwood Software:

• Paradox for Windows to Paradox Tables -- You have two choices for when you migrate. Keep Paradox as a front-end and migrate the data to an SQL database but keep the existing Paradox application. We keep Paradox application changes to a minimum. This is a very affordable approach. Although more expensive, the other recommendation from us is to migrate your data to an SQL database and convert your Paradox application to either a Delphi Win32 application or a DotNet application. Although we love Delphi, our slight preference is to migrate to a DotNet application using VS.Net (either C# or VB.Net).
   
• Delphi to Paradox Tables -- Our recommendation is to leverage your existing Delphi application and swap out the Paradox tables for an SQL database if you are having problems with the Paradox tables or want a more robust data storage solution.
   
• Other Systems Using Paradox Tables -- I would have to hear a bit more, but generally I recommend migrating your data from Paradox to an SQL database if you are having problems with the Paradox tables or want a more robust data storage solution.

Recommendation 2: Commercial Applications
A commercial application is a single user or multi-user application that requires easy installation and very little to no maintenance. Intuit would sell very few copies of Quicken if each user had to install SQL Server, Interbase, etc. If your commercial application currently uses Paradox, our position is that you should switch out Paradox for an embedded database such as Advantage, DBISAM, SQL Server Everywhere, etc.

Paradox Tables Best Practices

A best practice is a practice which is most appropriate under the circumstances. Because it is better to be safe than sorry, we've expanded the term best practice to include practices "believed" to be most appropriate under the circumstances.

The following terms are used throughout to help distinguish:

• Standard - a practice reasonably proven to help stability
• Suggestion - a practice generally accepted as helping stability but not proven
• Optional Suggestion - a practice generally accepted as helping improve speed and either has no impact on stability or may have a positive impact on stability.

The following best practices are primarily for multi-user Paradox applications. If you're using a single user Paradox application, scan through these suggestions for applicable best practices. We've only included practices where the benefit out ways any disadvantage and we skipped over setup procedures such as setting the NetDir (although we do offer a few NetDir suggestions). Also included are settings that are known to increase data speed with no-known negative stability effects and may contribute to increasing stability.

File Server Best Practices

1. Use a File Server Standard
Put your data tables on a real file server. If your application is a multi-user application and your data is worth at least $500, buy a file server and put your data on it. Do not use an existing workstation.

2. Disable OpLocks on File Server Suggestion
Opportunistic locks (OpLocks) are a feature of the LAN Manager networking protocol implemented in the 32-Bit versions of Windows. An OpLock is a guarantee made by the server for a shared logical volume to its clients. These guarantees inform the client that the file server will not allow another client to change the content of the file, or if some change is imminent, the client will be notified before the change is allowed to proceed. OpLocks are an additional protocol on top of the file-system locking protocol and was implemented for performance reasons.

Problem with OpLocks include:

• block clients from performing operations immediately
• were designed for file-sharing with medium concurrency
• introduced an additional level of overhead
• known problems with file-based databases such as MS Access.
• some believe the problems with MS Access also apply to other file-based databases including Paradox tables

Our current recommendation is that if you're having problems now with Paradox tables, disable OpLocks on the file server. In addition, if you are not having problems now but you wish to take a conservative approach, disable OpLocks.

To disable OpLocks on your file server: Configuring opportunistic locking in Windows

Vista/Server 2008 Note: OpLocks changed with Vista and Server 2008. Microsoft introduced SMB2. I do not know if OpLocks still causes problems under a Vista to Server 2008 scenario or a 2000/XP to Server 2008 scenario. I do know if you disable OpLocks on Vista, the offline files feature fails. However, since we are recommending turning off OpLocks on the file server only (prevents all OpLocks), this is less of an issue. However, this begs the question, if I switch out my file server for a Server 2008 file server, are OpLocks still a problem? If anyone has information on this, let me know and I'll update this article.

Workstation Best Practices

3. Proper Shut-Down Standard
Some users get used to the idea of just turning off a computer at the end of the day without properly shutting down. Although it is fairly common knowledge that you must shut down your computer properly make sure all users understand that when working with live data on a network, it's critical.

4. No Freeze-Ups Standard
If you have a workstation that freezes up (crashes) every now and then, don't ignore it, resolve the issue. Your goal is to have stable well-running workstations that never crash.

Borland Database Engine (BDE) Best Practices

If your application uses the BDE to access Paradox tables, use the following best practices in order to maximize reliability and to increase speed where reliability does not suffer. Whether or not the BDE files are stored on the network or on each workstation, the BDE runs on each workstation so these best practices are also workstation best practices.

5. Use The Most Current BDE Standard
We recommend using the latest BDE available, currrently BDE 5.202.

• Borland Database Engine 5.202

6. NetDir In-A-Folder Suggestion
Some BDE users have recommended that you do not use a mapped root drive such as "N:\" for Paradox's NetDir. Most consider the default of C:\ a bad idea too that has lasted now for 15+ years and now in Vista is not allowed. So, when moving your NetDir out onto a common location on your network, do not put it in the root of a mapped or shared drive, use a folder.

7. NetDir Same Path Standard
When I worked at Borland, we recommended that all workstations get to the NetDir the same. If one system has the database mapped to drive "N", then every computer with access to that database must also have it mapped to drive "N". For example, although the following network shares resolve to the same location, we recommend all workstations use the same connect path.

•  \\Taz\Network\Data\PdxNetDir\
•  \\Network\Data\PdxNetDir\

My belief is that locking problems are sometimes caused by mis-configuring one of the workstations.

Note: Do not ignore the "use a file server" standard already discussed, but if you do choose to go down that dangerous road, you must be sure that the computer that actually hosts the database does not use a local drive letter for data access nor for the NetDir. If the database is accessed via N:\Data\, on one machine, it must use the same path on all the machines including the computer sharing the data. This note also applies if you are using a file server and occassionally access the Paradox tables using a BDE application installed on the file server.

8. Strict Integrity Standard
Set STRICTINTEGRTY to True (the default). When set to True, only applications that support referential integrity can use the tables preventing others, such as Paradox 4 for DOS, from accessing the tables. When set to False, Paradox 4.0 applications (and similar) can use the tables. If False, you risk the integrity of your data.

9. Local Share Suggestion
Set Local Share to true. In the BDE Administrator, select the Configuration tab. The Local Share setting is located at Configuration | System | Init.

Note: Our Workbench for Paradox add-on utility requires Local Share set to true (Workbench is a Paradox for Windows developer tool).

10. MINBUFSIZE and MAXBUFSIZE Optional Suggestion
The defaults of 128KB and 2048KB for BDE's data cache are low considering the amount of memory available in today's computers. We recommend you raise MINBUFSIZE from it's default of 128 to at least 1024 and raise MAXBUFSIZE from it's default of 2048 to at least 16384 (16MB).

11. MAXFILEHANDLES Optional Suggestion
We recommend you raise MAXFILEHANDLES from the default of 48 to 255. I used to recommend raising it to 100 but enough CodeGear and dBASE users have suggested to me for various reasons that 255 is a better setting.

12. MEMSIZE Optional Suggestion
We suggest you raise the maximum amount of memory that the BDE will use from it's default of 16 megabytes to at least 32MB or larger on computers with more than 1GB of memory (the max you can set this value to is 205MB). Setting it to 25% of available memory is a common recommendation I hear from our developers here at Prestwood Software.

13. SHAREDMEMSIZE Optional Suggestion
We suggest raising it from the default of 2048KB to 16384KB (16MB). If you want to be more precise, there are formulas available on the Internet you can use. My belief is raising it to 16384 is fine for most modern users of the BDE.

Screen shot showing the above INIT specific settings:

Paradox ODBC Best Practices

The following applies if your application uses an ODBC driver to access Paradox tables (meaning you do NOT use the BDE). I have some more notes on this and once I dig em up, I'll add to this list here.

14. Use Paradox 9 Developer's Edition ODBC Driver Suggestion
If the ODBC driver you are using is working for you, stick with it. If you're looking for a recommendation as to which Paradox ODBC driver to use, we recommend the ODBC driver that shipped with the developer's edition of Paradox 9. This is the only ODBC driver we know of that works with Paradox 7 tables (I'm sure others exist) and over time has proven to be the best (in our opinion).

If you don't have Paradox 9 Developer's Edition, you can get the ODBC driver that ships with it from DataDirect Technologies.

Paradox for Windows Best Practices

If you built your application using Paradox for Windows, we offer the following best practices.

15. Working and Private Directory Suggestion
Some Paradox developers are recommending you set your working and private directories at least two folders down from the root.