Microsoft Dynamics GP runs on the Microsoft SQL Server database which is a scalable platform that can run a high transaction volume with little support. SQL enables you to do query, search, synchronize, report, and analyze your data easily and effectively.
Having your business data in SQL Server ensures that you have the latest advances in data integrity, performance, and manageability. You can take advantage of SQL Reporting Services to create Web-enabled reports, use integration tools like ACE eConnect templates to communicate seamlessly with other database applications, and restore your company database to a specific point in time with SQL Server’s backup and restore functionality.
However, even Microsoft SQL is not maintenance-free. Here are some common items ACE can recommend or assist you with in maintaining a stable and well performing Dynamics GP system.
Schedule 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.
Determine your 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.
Select the right 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.
Understand 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.
Do not locate 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.
Use correct 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.
Don’t assume 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. At a minimum, both an on-site and off-site backup strategy should be implemented.
Properly back up the transaction log. Fortunately, a full backup will not 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.
Microsoft SQL Server does have the ability to recover from problems. Built into the application are several data recovery and data protection mechanisms designed to protect your business data in the event of hard drive or server failure. SQL Server stores all information and changes in the transaction log, which allows you to backup and restore it to any point in time, not just the time you last took a backup.
Microsoft SQL provides a powerful, yet flexible framework to store and access your business data. Unfortunately many companies purchase the software, install it and never look at the server again until it runs out of space or they realize that they need to restore to a backup and there isn’t one because the backup plan is no longer running.
Protect your investment. ACE can assist with one-time or ongoing preventative maintenance of your SQL Server. Initially, we will complete a Microsoft Dynamics GP SQL Installation and Configuration Audit where you will come away with a comprehensive document of your current installation and configuration as well as recommendations to become complaint with Microsoft recommendations. From there we can set up a schedule that makes sense for your organization to review your server and complete preventative maintenance procedures to protect against data corruption and ensure that backups are running properly and are reliable. Take the first step toward protecting and maintaining your software investment today and engage with us for an audit of your GP and SQL Installation.