QuickBooks Data Protection in Virtualized Environments

We have a lot of customers running Intuit's QuickBooks accounting software. Customers often use this simple application to prepare invoices, purchase orders, and perform payroll for their new businesses. As business booms and more employees are hired, QuickBooks starts to become unreliable and sluggish. Intuit advises customers to migrate to their Enterprise package, but flexibility and data protection features are still limited. Migrating to another accounting platform such as Microsoft Dynamics comes with substantial downtime, learning, and migration costs.

 

In this guide, I would like to go over the weaknesses in QuickBooks and present some possible ways to ensure your company data is protected and made as "highly available" as possible.

Part I: QuickBooks Data Vulnerabilities

Part II: Highly-Available Strategies

 

 

Part I: QuickBooks Data Vulnerabilities

 

QuickBooks Multi-User Back End Limitations

QuickBooks uses a flat file database to store most company information and transactions. The database is written by "Sybase" and is a major component of a QuickBooks .QBW company file. In a multi-user environment, the file is placed on an SMB share (Windows) or Linux export. The single file approach is a blessing for small companies as it is very easy to backup, transport, and restore. However, the company file often grows to hundreds of megabytes to gigabytes in size. That hundred+ megabyte company file is essentially opened and cached by all computers on a network, making it almost impossible to run over a VPN for remote access. This is in stark contrast to using a transactional database back end (SQL Server, MySQL, Oracle, etc.) like Microsoft Dynamics where transaction data is stored in tables on highly available database clusters.

 

QuickBooks employes File Locking rather than Record Locking. Ever see a warning like the one below? The way QuickBooks prevents race conditions (i.e. multiple users updating table records at the same time) is to boot all users off the network except for the Admin to make a change.

 

 

This annoying error message pops up when trying to adjust inventory (some versions), change list item types, edit preferences, etc. Intuit realizes that nobody would use their software if every action involving a list had to be performed in single user mode, so some leniencies are implemented which can lead to user error and / or data corruption.

 

Messages like the one on the right frequently pop-up after employees enter or alter new records in the customer / vendor list. After Employee A keys in contact info, they may lose their inputs if Employee B is also work with that customer unless they heed the warning Intuit posts to double check their inputs.

 

 

Buggy Code

 

No program is perfect, including QuickBooks. Intuit does a decent job releasing service packs to fix bugs(using around 10 per year, designated as R<Release Number>) but some of their bugs can be very harmful to the database.

 

Consider the way QuickBooks Aborts when it loses connectivity to the server for as much as a second. You could be in the middle of editing an invoice and receive message such as the one on the right. This is not a graceful shutdown that does some integrity checks, but complete shutdown of the .EXE.

 

Similarly here is a dreaded "COM" error that prevents QuickBooks from loading. This is less a database vulnerability and more of a system vulnerability.

 

Here is a Payroll Add-On bug where "E-Payment" transactions are marked as checks "To Print." Accidently e-paying and printing checks may require reverting to prior backups to undo.

 

User Error due to Unintuitive Design

 

Certain functions in QuickBooks can only be done via ODBC calls to the database (and not through the actual QuickBooks GUI.) For example, we run into many customers that have POs and Invoices marked "To Be Printed" but are never printed. Over the years, thousands of transactions accumulate that need to be cleared. Intuits Answer? Print them to a PDF. Well, that's not a great suggestion considering that QuickBooks tends to crash after a few hundred documents enter the print spool (admittedly, not totally Intuit's fault.) The other method involves using third party utilities to clear this flag using SQL statements. Any third party access to your database should be preceded by backups!

 

 

The lack of good user permission control is another reason for increasing the number of daily backups performed. You would think that giving user "Partial Permissions" to "View" a checking account would prevent them from editing the register. Well, notice how the "modify" function is greyed out in the picture to the right ("Company" -> "Users" -> "Setup Users and Roles."). You can only grant users NO access to an account register, near full access (just limiting their ability to print), or FULL access. Contrary to what you might think, the settings on the right do let users modify and view checking account registers!

 

Security issues aside, we have seen clients mistakenly post and reconcile incorrect accounts that required reverting to prior backups to fix.

 

 

 

In Intuit's defense, QuickBooks is considerably easier to use (and less expensive) than an SAP or Microsoft Dynamics Solution. Intuit never intended QuickBooks to be used by Fortune 500 companies. Below are some tips to minimize downtime and risk of data corruption.

---

Part II: Protecting QuickBooks Data

In a multi-user environment, the QuickBooks company file (.QBW) should be hosted on a server. Users can either access the company file by using QuickBooks client software running on their PCs, or they can use a Windows Terminal Services Session to run the actual QuickBooks software right on the server. A terminal services / RDS session is the ONLY supported way to grant remote access to QuickBooks to home / branch office users.

 

Like most server applications, we recommend isolating QuickBooks from other server applications and roles as much as possible. We have found that QuickBooks works well with Microsoft Hyper-V, including virtual machines running 2012 R2 (though not currently on Intuits Support List). Using QuickBooks in a Virtual environment has several advantages:

-You can minimize the number of services running in the Virtual Machine, reducing the risk of the COM and DLL errors shown above.

 

-While minimizing the roles and applications around QuickBooks, you can still use the physical server for other tasks (while placing those roles in other VMs.)

 

-If QuickBooks patches break the installation, you can use Virtual Machine Shadow Copies to restore QuickBooks back to a working configuration without having to do a re-install.

 

In an ideal world, we would like to place the .QBW file on a dedicated shared storage device so we could have standby server installations ready to boot and host the file. We have tried using Microsoft DFS (Distributed File System) to replicate the .QBW file to a second server but we notice frequent application aborts. The idea was again to have a synchronized copy of the database on a second server that could be hosted with the most recent database copy.

 

We are in the process of evaluating the use of an entry-level SAN such as an EMC VNXe3150 to store the actual .QBW file. If hosted on an iSCSI LUN (block level), our theory is that QuickBooks will not be able to distinguish between a local HD and a SAN. The VNXe3150 allows multiple servers to access a single LUN over multi-pathed iSCSI connections for optimized performance. [NOTE: No matter what, only ONE instance of the QuickBooks Server should ever access the .QBW file at once or data corruption WILL occur. Our goal is to design a solution where the server role can fail over to a secondary VM or physical host such that the primary can go down for maintenance.]

 

Backups

 

There are two levels of backup that should be made several times per day to protect against patch corruption and user error.

-Backups of the company file (*.QBB files generated from scheduled or manual backups initiated from the File menu)

 

-Backups of the virtual machine holding QuickBooks Server Installation

 

The *.QBB files can get pretty large (it includes your company database and custom forms.) Typical sizes range from 100MB to over one Gigabyte. You can use Intuit's Data Protect to send a copy to the cloud, but this is a SLOW process and should be done late at night. We recommend making backups to a NAS appliance, possibly multiple times per day, for maximum protection. Larger customers that require the retention of multiple weeks of data may use something like an EMC DataDomain DD160 or DELL DR4000 deduplication appliance which will only store changed blocks between backup files. Local NAS backups are also easier to restore for Disaster Recovery testing.

 

The virtual machine backup can be performed with tools like PHD Virtual, Veam, EMC Networker, or even with some basic scripting tools like PowerShell to leverage the native snapshotting features in Hyper-V. Often the QuickBooks company file resides within the Virtual Machine, which can be problematic during a restore since you can't be sure that the database is in a quiesced stated. Therefore, it is best to use schedule VM shadow copies during maintenance windows. The real purpose of the VM backup is ensure you have a working configuration to revert to after you apply updates to QuickBooks and find COM / DLL errors.

 

Further Testing

This Storage Networks solution is still a work in process. Over the next few months, we plan to test data integrity while running QuickBooks on iSCSI SANs (such as the HP MSA2040 / P2000 and EMC VNXe3150) as well as using tools like VMWare Fault Tolerance or third party synchronous replication products for Hyper-V.