Microsoft Dynamics GP and SQL Server – 8 Considerations to Maximize your Database Maintenance

A significant consideration when evaluating ERP software choices is the other technologies the ERP software runs with.  Microsoft Dynamics GP users are able leverage the Microsoft ‘stack’ of technologies for simple yet robust integration with other Microsoft infrastructure and business applications.

For example, Dynamics GP runs on the Microsoft SQL Server 2008 database which is a scalable platform that can run with little support a high transaction volume. SQL enables you to do query, search, synchronize, report, and analyze your data easily and effectively.

However, even Microsoft SQL is not maintenance-free.  Here are some common things to avoid on your path to understanding and maintaining a stable and well performing Dynamics GP system.

1. Not scheduling regular database maintenance.  As databases grow, it is important to allow SQL to re-tune the database for optimal performance. All the tools you need to do this are part of a SQL database maintenance plan.  How often it needs to run depends on your environment, but generally weekly maintenance runs are sufficient.  Be sure your maintenance plan includes tasks to check integrity, rebuild indexes, reorganize indexes, and update statistics.
2. Not having a server performance baseline.  Shortages in memory, disk, or CPU can all cause major performance problems.  Windows Server comes with everything you need to monitor a server out of the box.  Check out Windows Performance Monitor and its scheduling capabilities to get a clear view of what’s happening on your server.   Often times major bottlenecks can be simply rectified, such as dropping in some additional memory in a RAM starved server. 
3. Selecting the wrong RAID level for the job.  RAID comes in many flavors and each has its strengths and weaknesses.  RAID 5 is popular because it is usually the most economical since you lose only one drive worth of space in your array for the parity data.  However, it usually has the slowest write performance of just about any type of RAID.  Selecting RAID levels with good write performance like RAID 1 and RAID 10 can provide better performance.  Avoid RAID 0 as it does not provide any fault tolerance.
4. Not understanding the difference between simple and full recovery model in SQL.  SQL writes all changes to the database to the transaction log.  In simple recovery mode, this transaction log is automatically truncated periodically. In full recovery, it is your responsibility to manually truncate the transaction log, usually by performing a transaction log backup. The benefit of full recovery mode is that you can restore to an individual point in time, not just to the time you perform a full backup.  If you have problems with your transaction log growing constantly, and you are not concerned with point in time recovery, choose simple recovery mode.
5. Locating transaction logs and database files on the same physical drive.  Many know you should place database and log files on separate drives.  The science behind this is that SQL must write to both files at the same time when transactions are applied to the database.  Putting the files on separate physical disks allows these activities to happen in parallel. And consider that a single physical drive or array partitioned as two drives may not provide the security of two separate drives. 
6. Using improper auto-growth and auto-shrink settings.  It’s important to understand that SQL files can be logically and physically fragmented. Frequent shrink and growth commands executed on the database will cause fragmentation of the database files.  It is better to set the size of the files to a reasonable size right away, thereby reserving a contiguous area of disk platter. This way, the logical amount of data inside the files can grow and shrink without having to alter the physical size of the database. 
7. Assuming a flat file backup is sufficient for recovery purposes.   The maintenance plan wizard in SQL management studio may be sufficient for many applications, but it may not provide the same type of comprehensive reporting and monitoring you would get from a centralized backup with a commercial utility. To back up a SQL database properly, you should strongly consider using commercial backup utility which is SQL aware.
8. Expecting a full backup to truncate the transaction log. This is one of the most misunderstood aspects of using SQL, even among database administrators.  SQL does not truncate the transaction log when performing a full backup. Doing so would break your transaction log chain and remove your ability to do point in time restore, which is a central point of the full recovery model. If you are unsure, just switch to simple mode and take full backups.  This way SQL will manage the transaction log automatically.

We have decades of experience working with Microsoft SQL and we would like to help you implement Dynamics GP with the right SQL setup so you can maximize your benefits.  Please contact us at sales@crestwood.com.

By Ben Borger, Project Manager, at your Chicago Microsoft Dynamics Great Plains Partner, Crestwood Associates.

Related Posts

1. 5 Ways to Make Sure Your Microsoft Dynamics GP Data is Secured
2. Microsoft Dynamics GP Achieves 18% Performance Increase Using Microsoft SQL Server 2008
3. ERP Software Maintenance Fees: Insurance You Actually Need