Database Management is a Team Effort
Let’s say you just put the kids in the car and took off for sunny Florida for vacation. 200 miles down the road, the engine light starts to blink. Another 20 miles and the engine begins to sputter. By the time you reach the next town, your car has stalled. It’s 100 degrees out and the kids are hungry and you’re thinking to yourself, “I should have had a tune up before we left.”
Yes, you probably should have.
Your system is just like a car. The database is the engine. Junk builds up inside the carburetor and it needs to be cleared out periodically or bad things happen. In the software world, these bad things include time-outs, screen locks, slow downs, server crashes, etc.
MoversSuite is akin to the software running your car. It strives to make sure that everything runs efficiently. We attempt to make our software error free and perform at its highest levels, but there’s only so much that we can do on our end, so we need your help. For example, MoversSuite can become more efficient in its use of keys when performing searches and can make changes to the schema to enhance performance, but we cannot control an application server if it happens to become filled with fragmented indexes, i.e. flat tire on the freeway.
Another benefit of proactively managing your system is the safety and integrity of your data. With the correct backups happening periodically, you may avoid losing a day or more of valuable data if the power goes out. Going back to the car analogy, this would be like testing your brakes to make sure that they do work every time.
So, while we try and improve MoversSuite’s efficiencies and keep your data safe, we would like to make sure that you take similar steps to ensure things run as smooth as possible on your system.
In brief, we recommend, at a minimum, the following things that you can do to improve system performance and keep data safe and recoverable. This is only a short list of recommendations and we really hope that you take it upon yourself to find out more about what you can do to keep your system running smoothly. Each car is different, therefore each system is different and can be configured differently, so the impetus is on you to do your part.
Maintenance Plans
SQL Server provides a tool that can be configured to ensure that your database is optimized, backed-up, and running in a consistent fashion. The Maintenance Plan performs many tasks and should include a complete daily backup, transactional log backup (every three hours), rebuilding indexes and updating statistics.
Manage Auto Growth
The Auto Growth feature of a database server, such as those servers hosting Dynamics GP and MoversSuite, must be set correctly based on your data and environment. For many of these databases, the default is a certain percentage of memory growth, such as 10%. We recommend setting this to a physical value as opposed to a percentage. For example, verify that the Auto Growth feature on the server hosting MoversSuite is 100 MB.
Verify that SQL Server Agent is Active
The SQL Server Agent is responsible for starting and stopping various jobs associated to your Maintenance Plans, data rollover, and various Dynamics GP processes, as well. It is critical that this service is running at all times.
Please feel free to contact us if you need any assistance or direction in managing a safe and productive database environment.
Here are few links to some articles we came across related maintaining your databases and more.
https://technet.microsoft.com/en-us/magazine/2008.08.database.aspx
http://justgeeks.blogspot.com/2012/07/sql-server-maintenance-plan-best.html
https://technet.microsoft.com/library/Cc966523
Thank you!