January is typically the time where many of us decide to get into shape. New year, new us! But what about our beloved Microsoft Dynamics GP? Look for these signs:
- Are transactions taking forever to post?
- Is running the reconcile process taking a long time?
- Are batches taking longer and longer?
- Do you find your inquiries are taking too long?
If you’ve noticed your Microsoft Dynamics GP system running slower and slower, then it’s time to take a look at your database size. Over time, databases build up large amounts of information and can hinder performance.
Big Database = Slow Performance
When we take on a new client, one of the first items on our checklist is to conduct a business process review, where we document the client’s system and identify any critical tasks and map a plan for future growth. Often, during these reviews, we find ways to streamline operations and improve system performance. Most often, slow performance is caused by a bloated database.
By “bloated database,” we mean databases that have accumulated years of detail which isn’t always necessary. At the most risk are clients with high sales order transaction volumes. The image below shows the volume of automated sales documents coming into Dynamics GP over a two-week period for a high-volume customer.
It’s easy to neglect data management, only handling it on a repair and maintain basis, with no data archiving or purging. As a result, databases grow exponentially over the years. Database archiving and purging is a critical task that can greatly improve daily performance, help avoid data corruption, and system crashes. Look at how the database size progression grows over 6 years, and its estimation for an additional 2 years.
The transaction counts consuming the storage were heavily weighted to sales order processing and receivables management-based transactions (87%). Within their current environment, available disk space was nearly nonexistent, which means, whenever you run maintenance routines, processing speeds ground to a halt.
To add insult to injury, frequently, the customers who are most affected also are running on older versions of Dynamics GP and older versions of SQL server, which compounds the problem.
Get Your Speed Back with CDA
There are purge routines available in Dynamics GP, but for some businesses, that’s not an option. Others who have large databases or limited processing power of their server environment found these tasks could take days to complete.
What Crestwood recommends, and has put into action for many customers, is an application called Company Data Archiver (CDA). CDA keeps your data in an archived location so that you can free up your database to run at optimal performance. Recently, we used CDA to take a database of 103GB and reduce it to 35GB, while maintaining the data in an “archived company.”
This provides access to all the data (as needed), while removing the closed/old data to a separate company on a routine basis. Additionally, Company Data Archiver provided the necessary SQL scripting to reallocate the unused space once completed.
Crestwood strongly recommends that routine data maintenance be performed to minimize the data contained within the live database, using the CDA program. Moving ahead with this approach will have an immediate increase in efficiencies of your users and system resources. As a bonus, it provides for an easier upgrade to both Dynamics GP and Microsoft SQL, should you be on that track.
Enabling CDA accomplishes 4 key objectives:
- Reduced hardware costs (no need to upgrade)
- Increased performance
- Faster upgrades and backups
- No more clutter
Crestwood has many lifelong GP users seeing success with CDA to improve performance on reporting and inquiries. Professional Advantage has different plans for varying needs – some clients will want their CDA Plus for Receivables, Distribution modules, or third-party product archiving, but the Express version lets you archive Master Data, GL, and Payables information. The Crestwood team has a great relationship with Professional Advantage, and we are available to write scripts to move custom tables, if necessary. We have experts on staff to help you fully implement this product and whip that database into shape. Talk to your Account Manager or sales@crestwood.com to get the ball rolling.
