Starting with the release of Peachtree 2007, Sage Software replaced the database engine which manages the Peachtree application, moving from Btrieve Version 6.15 to Pervasive SQL (PSQL v.10 for the 2010 product). From the 2007 Edition through to the present 2010 Edition (the only Peachtree versions now supported by Sage), Peachtree is powered exclusively by Pervasive SQL. Both database products (Btrieve and Pervasive) are owned by Pervasive Software (www.pervasivesoftware.com), and are licensed to Sage for use with Peachtree on a “per user license sold” basis.

 So, what is this mysterious critter that sits lurking quietly beneath the surface of Peachtree?

 In overly simple terms, it is what is called a “database manager”, storing the data that users enter into Peachtree and “managing” the process of “locating and serving” the right information to users based on their activities in Peachtree (and their security settings). Pervasive manages the byzantine process of updating the Journal Header, the Journal Row, the Customer, and the Item tables every time a user enters an invoice in Peachtree, making sure that all the data goes where it belongs and is properly indexed so it can be located again when needed.

 Pervasive SQL, unlike its more well-known “big brothers” such as Microsoft SQL, Oracle, and MySQL, is designed specifically as a “low cost of ownership” self-administering database manager, with no dedicated Database Administrator (DBA) required, and no real technical knowledge needed to initially install and configure the product. In fact, the Peachtree installation routine makes all of the Pervasive installation and configuration settings transparent. This is a good thing, to a point, and fits nicely with the pricing model and target audience for the Peachtree application.

 However, veteran computer users should recognize that there is no such thing as “one size fits all” when it comes to configuring a database. Some Peachtree users hit the database very lightly, while others process thousands of transactions in many different tables within the database. Hardware and other “environmental” configurations can be vastly different from company to company, which can have implications for performance, and therefore for database configuration. Sage has to account for all possible variations in uses when setting up default configuration settings.

 Because Sage Software has no way of knowing in advance exactly how you plan to use your Peachtree system, or what your transaction volume will be, the only logical response from Sage for initial database configuration is to make the settings conform to a “lowest common denominator”, which addresses the published minimum hardware and overall “technology environment” requirements. That is really the only way to go to insure that the product will work “out of the box.” Keep in mind, however, that the Pervasive SQL engine can only work with what is made available to it, via its configuration settings, and the minimum, automatically-set system configuration does not necessarily insure an optimum setup for your specific accounting software needs.

 So, what is a user to do to wring a bit more performance out of this powerful-but-low-maintenance-oriented database manager?

 There are four factors to consider in “tuning” your Peachtree setup. Three of these are relatively straightforward, are independent of the Pervasive database itself, and can be addressed by most competent IT professionals. The fourth factor involves “performance tuning” for the actual Pervasive PSQL engine on the server, and is a little more involved (including modifying Registry settings on the server, which makes some folks get sweaty palms right away – for good reason).

Server Hardware
Don’t have a dedicated server? Get one. Peachtree doesn’t need a dedicated server to operate, but if the performance of your accounting system is a concern of yours, start here. Using a so-called “peer to peer” network for running an accounting product requires acceptance of lots of performance concessions, which most users grow tired of pretty quickly. Servers today are more reasonably priced than they have ever been.

Look for at least a dual-core processor, around 3 Gigahertz or better. Pervasive does not take advantage of dual processors yet, but if you run other applications from the same server (as most small businesses do), using dual processors will allow the operating system itself to manage some of the “load balancing” between the various apps.

Purchase the fastest disk drives you can afford. Disk access time can make a huge performance difference for a heavily input-output intensive application such as accounting. A “Raid 0” stripe array, while pricey, will allow much faster disk writes than a more “conventional” disk setup. Your money or your time…..

Server Memory
Server RAM is not terribly expensive, and is relatively simple to install these days. The question of “how much” RAM to install can be complicated, and the answer is generally dependent of what apps will run on the server. Of course no one ever has good answers to all of these questions when purchasing a new server, so there are two rules of thumb here.

1. Never buy less than 4 Gb of RAM for a modern server.
2. Always ask your server vendor about memory “deals” (2-for-1, additional discounts for larger chipsets, etc. etc.) and then purchase the most memory you can afford within the confines of  that “deal”.

Windows servers have some limitation on how much memory a user can address, but having plenty of memory will insure that there is always more than enough left for other server purposes after the user has maxed out the user-addressable memory available.

Network Environment
Many apparent database performance issues are actually a consequence of the network “infrastructure”, and these can really be the devil to diagnose. There are a few obvious things to consider here.

1. Network interface cards (NICs), routers, and network switches are all critical components of the computing environment, and it rarely pays to “skimp” on those components. Purchase high quality components from known manufacturers, and have them configured by knowledgeable IT professionals.
2. Don’t have your network cabling installed by your brother’s nephew (unless he is a competent IT guy).
3. Don’t use a wi-fi connection for a large database; wi-fi is neither fast enough nor reliable enough (with microwave ovens, cell phones, etc. interfering) to trust for accounting transactions.

 Configuring the Peachtree Pervasive Database Engine
This is the fourth and final piece to focus on to “tune” your Peachtree install for optimum performance. I will address this final piece of the puzzle in my next post on PeachFuzz.