MS Access Backend - When it's time to go to SQL

Summary
After the MS Access database reaches a certain size, you may start to experience performance issues and also database corruption. To resolve this, you can run a repair and compact procedure to shrink the database and also can purge (delete and archive) old documents. If these efforts to not shrink the database enough, you will need to re-host to a MS SQL database.

Understanding the MS Access Database

The default QuoteWerks database that works with every edition is the Microsoft Access database backend. The main QuoteWerks database is the DOCS.MDB. The DOCS contains your licensing and quotes, and is the most important of all the databases within QuoteWerks. The average size of an empty DOCS database is 1MB (megabytes).

As you create more documents, the database will grow in size. There is no direct correlation between the amount of documents in the database compared to actual file size of the database. There are several factors that contribute to the size of the database; i.e. the amount of documents created, the number of items on those documents, etc. Once a database reaches 250MB, your QuoteWerks Administrator will need to adhere to regiment of running the Maintenance on the DOCS database at least once a month.

Should the database reach 500MB in size, it would be time to consider upgrading to the Corporate Edition with the SQL backend database. Continuing to use QuoteWerks on the Access backend once the database is larger 500MB with likely result in users experiencing performance issues and a greater susceptibility to database corruption.

Maintaining the MS Access Database

What is Maintenance?

Microsoft Access is a relational database application with which a desktop user can efficiently create and manipulate database systems. Access targets the desktop category and works best for individuals and workgroups managing megabytes of data. For multi-user access to the same database, Access uses file-server architecture, rather than client-server architecture. Theoretically, Access is limited to a maximum of 255 users and 2 gigabytes of data; however in practice, acceptable levels of performance begin to degrade around 15 concurrent users.

As you use QuoteWerks, records get added/deleted/edited from the main DOCS database, and the database becomes larger. Some of this growth comes from new data, but some of it comes from inherit functions of Access itself:

•Access creates temporary, hidden objects to accomplish various tasks. Sometimes, these temporary objects remain in your database after Access no longer needs them.

•When you delete a database object, the disk space that the object occupied is not automatically reclaimed — the database file still uses that disk space, even though the object is deleted.

As your database file fills up with the remains of temporary and deleted objects, its performance can degrade. Objects may open more slowly, queries may take longer than normal to run, and typical operations generally seem to take longer. In particular circumstances, a database file can become corrupted. If a database file is shared over a network and multiple users work directly with the file simultaneously, that file has a small risk of becoming corrupted.

In order to correct these inherit issues; Maintenance must be run on the database. The Medic Utility’s Maintenance option runs what is called a Compact and Repair on the Access database. Compacting does not compress the data; it simply makes the database file smaller by eliminating the hidden objects and unused space, and undeleted objects within the database.

How to Run Maintenance?

Database Maintenance can be run via the Medic Utility. The Medic Utility can be found in your Tools & Information program group in the Windows Start menu, or directly in the QuoteWerks directory as MEDIC.EXE. Database Maintenance should be ran on the DOCS.MDB file on a regular basis. At a minimum, this should be run at least once a month to promote a "healthy" database.

Depending on your database usage, you may need to run maintenance more frequently.

Important: No users should be logged in QuoteWerks at the time Maintenance is run.

To launch the Medic Utility:
1. Click on the Start Menu->Programs->QuoteWerks 4.x/5.x ->Tools & Information -> Medic Utility (it can also be found in the QuoteWerks directory folder and is called Medic.exe).
2. Click on the Maintenance Button.
3. Right-click on one of the database files and Select All
4. Press the Start Button.

Options for a Large Database

Document Purge Wizard

If running the Database Maintenance is not reducing the database size enough, you can try to Purge the database. You may wish to purge older documents that you no longer reference. You can do this from the Utilities -> Document Purge Wizard. During the purge process, the end user does have the option to archive the documents in case they are needed at a later time. It is recommend that that the end user purges documents incrementally and then run Maintenance after each purge, using the QuoteWerks Medic Utility.

Going to Corporate Edition and Re-hosting to the SQL back-end

If purging documents did not shrink the database enough, then the only option left is to upgrade to the Corporate Edition of QuoteWerks and rehost the access database to SQL.

What is the QuoteWerks Corporate Edition?

The Corporate Edition of QuoteWerks includes all the features of the Standard and Professional Editions and adds support for hosting the data on a Microsoft® SQL Back-end database. The Corporate Edition includes the ability to integrate with ACT! for Web, Autotask, ConnectWise, MS Dynamics CRM and SalesLogix® contact managers in addition to the other contact management software. The Corporate Edition also includes quote synchronization functionality as with the Professional Edition. Additionally, the Corporate Edition includes the Rollout functionality that rolls out product and other non-quote related information to remote installations such as laptops. Corporate Edition pricing does not include Microsoft SQL Server licensing.

What is Microsoft SQL Server?

Microsoft SQL Server is an enterprise relational database application with which a desktop user can efficiently create and manipulate database systems. MS SQL targets enterprise environments that require scalability, performance, stability, and security. Furthermore, SQL allows access for individuals and workgroups managing up to terabyte levels of data. Users access data through client-server architecture that allows for point-in-time data recovery. Finally, MS SQL virtually supports an unlimited number of concurrent users.

Why the SQL backend?

Once a DOCS database becomes larger than 500MB, it starts to run the risk of corruption and performance issues. Without maintaining the database on a monthly basis, the corruption level could leave the database beyond repair. This is why it is highly suggested to get the Corporate Edition and re-host to SQL. Re-hosting to SQL allows for a larger, more stable database. It does not require maintenance. Microsoft SQL server is a very powerful, robust product that requires significant training and certification to have the knowledge to correctly maintain. It would be advised to have someone on staff that is familiar with the maintaining and administration of MS SQL.

It should be noted that the cost of the QuoteWerks Corporate Edition does not include the cost of SQL. Customers would need to provide their own copy of SQL. QuoteWerks supports up to Microsoft SQL 2019. QuoteWerks also support SQL Express as an option for re-hosting. It is often best to have QuoteWerks reside in its own instance of SQL in order to prevent conflicts with other systems.

Operating on the SQL backend allows for increased speed, unlimited product database sizes, better operations with multiple users, and stability. Operating on the SQL back-end also allows for 1500 line items per quote, as opposed to the limit of 600 line items on the Access back-end. In respect to remotes connected to the master database, operating on the SQL back-end allows for the deployment of practically all of the features and setup through the synchronization.

Acquiring Corporate Edition

Please contact the QuoteWerks Sales Team in order to acquire the upgrade to the Corporate Edition