Regular Maintenance Tasks for SQL Server and Dynamics GP
The data for Microsoft Dynamics GP is contained in Microsoft SQL Server, a Relational Database Management (RDBM) system. Because of the nature of the way data is written to the database from any application, there are certain aspects of SQL Server that need to be maintained to keep it running properly and at top performance levels.
Dynamics GP was originally written by Great Plains Software out of North Dakota. Version 1.0 of Dynamics was released in 1993. Microsoft purchased this company in 2001. Dynamics was recognized to be the first accounting package written for a multi-user environment and the 32-bit version of Windows. The data file structure used in the original application has been improved upon in SQL Server but is still largely intact. For this reason, there are some maintenance tasks that should be performed within the Dynamics GP application, as well.
How Often Should Maintenance Be Performed
The frequency of performing regular maintenance on Dynamics GP really depends on the number of transactions that are entered into the system on a daily basis. Smaller businesses that do not input a great number of transactions may only need to perform these maintenance tasks monthly, or even quarterly.
After completing the maintenance tasks you may notice an increase in performance of the Dynamics GP application…and you may not. Most of the maintenance tasks are to maintain the efficiency of SQL Server. If you do notice a significant boost in performance after every time you execute the maintenance tasks, you may be waiting too long between intervals. You want to notice a slight boost, if any, but not a tremendous difference from before and after running the maintenance tasks.
Some of the tasks mentioned below can, and are recommended to, be performed nightly while others can be conducted much more infrequently.
SQL Server Maintenance Tasks for Dynamics GP
The maintenance tasks for SQL Server can be performed with SQL Server Management Studio and/or Query Analyzer. A word of caution, if you are not familiar with these tasks or these SQL Server applications do your research or consult a qualified expert before conducting any maintenance tasks on your production database. When in doubt, you can always conduct the task in a test environment first.
Data and log file management
All of the data that is used by Dynamics GP is stored in a database file. There is a separate database file for the Dynamics GP data and another database file for the company(ies). The database file for the Dynamics GP data is comparatively small and fairly consistently stays that size. The database file for your company(ies) can grow to be significant in size. They should be configured properly to minimize auto-growth and any manual file manipulation including shrinking.
The log file contains information about the data in the database files. If a record is added to the database there is a log file record that contains information about this insert action. In fact, any action that is performed on a database record is recorded in the log file. If there is a large amount of adding, editing, moving, deleting activity in the database it is not uncommon for a log file to be considerably larger than the database file.
SQL Server has some built in tasks that help keep the size of these files under control but they should be enabled and configured as a “safety net” only. Manually growing these files by a fixed amount is the preferred method to handle the data and log files.
An SQL Server index, like and index in the back of a book, displays the data in a table in a certain sorted order. As data is added, updated, or deleted from the table these indexes can become fragmented. A fragmented index is one where the information contained within the index tells SQL Server how to find the data in the table in the desired order but the data contained within the index is not contiguous.
Rebuilding the indexes for a table rearranges the information inside the index to look like a brand new deck of cards with all of the suits together and in the proper hierarchical order.
This task can be performed nightly on fairly active databases and less frequently in databases with a lower level of activity.
Statistics keep track of what kinds of data are in a column and how much of each there is. Statistics are maintained automatically by default. However, statistics can be generated by a sampling or a full scan of the data in a column. When the maintenance task to regenerate statistics is performed, the type of scan used when the statistics were last updated is used again. So, knowing how to check this and change this can be very important.
Typically, rebuilding indexes and statistics in a nightly maintenance job is adequate. Being able to identify when this is not adequate is important to the performance of the database queries (A query is the script that is run to get the data from the database tables and present it to the Dynamics GP form). Also knowing which columns are scanned when an index is rebuilt and exclude those columns from additional scans can save valuable resources.
Recompile Stored Procedures
If the database is changed by adding a new index or by changing the columns contained in an index, the query execution plans also need to be changed. Recompiling the stored procedures that provide data to and from the Dynamics GP forms and reports will update the execution plans for each query.
This is not typically a regular maintenance item but it should piggy back any index changes other than rebuilding an index, as mentioned above.
The items listed above deal mostly with performance of the database. Corruption detection has to do with the integrity of the data that is contained in SQL Server.
How does data corruption occur and what causes it?
If the power is cut off to the server while it is in the process of writing a page of data and that page cannot be written in it’s entirety, you have an incomplete or “torn” page that results in corrupt data.
Other and more common causes for corrupt data are all of the hardware and software that SQL Server is installed on and along side of. This can include hard disk drives, controllers, drivers, networks, and even the operating system.
So what can be done to detect corrupt data?
SQL Server has a some built in functions that will look for and create notifications for corrupt data. An “alert” can be created that will let the database administrator know when corruption has occurred. The administrator can also run the functions to determine if any torn pages exist in the database.
If corrupt data is found, how do we fix it?
This is where a comprehensive backup plan becomes a business requirement.
There is no need to panic if the database becomes corrupted and you have a backup of the database available. Without one, the chances of accurately recovering the database without experiencing some loss of data that interferes with the business logic and the integrity of the data are virtually non-existent.
G&C Technical Services has a detailed article on how to create a comprehensive backup and recovery plan (BRP) for your critical business data and electronic assets. The article covers the types of media where the data could be stored, what types of data should be included in the backups, how frequently to backup which kinds of data, how to store or archive the backups long term, and how to recover the data in the event of a disaster. The Dynamics GP databases are a critical part of this BRP plan.
There are two ways that SQL Server databases can be backed up: Full and full + differential.
A full database backup contains the entire database and log file and should be made regularly. “Regularly” is a frequency that is different for every business and can be determined by the transaction volume posted to the database. The full backup provides you with a “point in time” version of your database.
A full backup can be done every time a backup is performed. For larger databases or more frequent backups, a differential backup may be taken in conjunction with a full backup.
As an example, to explain how a differential backup is used, a full database is taken on Friday night to capture the entire database as of that point in time. A differential backup is then taken Monday through Thursday. Each daily backup only contains the data that was added or changed since the last full or differential backup, or in this example, one day’s worth of data. Differential backups can be taken more frequently at multiple times during the day, as well.
How often a database backup is made is determined by the threshold of pain your business can tolerate if the database becomes corrupt. If one day’s worth of data is what your business is willing to re-enter in the event the database needs to be restored, then nightly backups are in order. How long to retain the backup files is also subjective. Several days for full backups or two full backups plus the subsequent differential backups might be a starting point for your business to consider.
One last note about backups: To minimize the risk of losing any data, you should always have three copies of the backups. This triple redundancy will virtually ensure that you will be able to restore your database in the event it becomes corrupt.
Data Maintenance Task From Dynamics GP
Dynamics GP has some built in data maintenance tasks built in that may need to be run from time to time. There is no harm in including these tasks in a regular maintenance schedule.
This process runs through the database tables that are related to each other (Relational Database Model) and attempts to locate any orphan records or other inconsistencies that may exist.
The reconcile process is almost a continuation of the Check Links function. Reconcile compares the data in the detail records to the summary and makes any necessary adjustments.
Although it is not specified anywhere in the Dynamics GP books online, it should be fairly logical that Check Links be executed first followed by Reconcile.
Run the Check Links process in a test environment first to determine if it even needs to be run on your production data.
NOTE: As a precautionary measure, create a database backup before running any of these maintenance procedures. This backup can be discarded if no issues occur while running the maintenance tasks.
We’re Here to Help
G&C Technical Services is an authorized VAR of Microsoft Dynamics GP with more than a decade of experience. We welcome the opportunity to speak with you and answer any questions you may have or discuss your options with you.
We are here to serve you, so please feel free to contact us.