Are you sure that your SQL maintenance and backup plans are set up correctly in your Microsoft Dynamics GP system?

It is scary how many times we have done a system evaluation on Dynamics GP and found that there is an incorrect configuration to SQL. Or how often we heard from companies that have lost their data because they do not have the appropriate backups.

This could happen because the maintenance plans were originally set up by someone who did not understand how SQL and Dynamics GP run together, and then they were never checked again.

I started working with a Dynamics GP client that had not had any database maintenance done for more than ten years. I found that there were inconsistency errors in the database. A backup will not run on databases that have consistency issues. The previous Dynamics GP consultant he worked with either never found this or was not able to figure out to resolve it, so he just left it this way.

Once we corrected this database inconsistency and the database maintenance was run, we could finally do a proper backup and secure his data. As a bonus, this improved the overall performance and speed of his Dynamics GP system.

We often see people taking shortcuts when it comes to configuring SQL or moving databases to SQL. This may be because they have financial expertise, but not the SQL/technical expertise. It is important for your Dynamics GP consultant to have both skill sets. (Hint: Look for SQL Server Certifications when choosing a Microsoft Dynamics GP Partner).

Our team has a SQL Maintenance and Backup Best Practices Checklist that we have developed over time. For example, here are a few suggestions:



• Do a full backup of the database, separate from any other maintenance. This is very important because if a maintenance process causes any issues or glitches, you have a full backup to use for recovery.
• We recommend that SQL maintenance and system database backups are run weekly, depending on the client scenario. This would include all the maintenance steps, except shrinking the database. We avoid this, unless there is some unusual exception, because it can cause performance issues for Dynamics GP users.
• The master database and the SQL databases should also get a backup. The transaction log backups allow you to recover to a point in time. Usually, depending on the client data and server capabilities, I would schedule hourly backups. However, if the client has a high volume of transactions it can be done more often.