SQL Server Maintenance: A 6 Point Mechanic’s Guide

As every car owner knows (or should know!), routine maintenance is key to keeping your ride happy. Periodically checking the oil, tire pressure, and alignment, for example, can save you significant time and money. The same can be said for your Microsoft Dynamics NAV system. While it requires a bit more work than just running down to the nearest garage, “tuning-up” your NAV is critical to keeping it running smoothly. Perhaps the most important component of this upkeep is SQL Server maintenance.

In this blog post, we will outline the what, why and who of SQL Server and its maintenance and provide a downloadable PDF with “6 SQL Server Maintenance Tips (Download it here!)”  to get you started on your journey to supercar status.

The engine that runs NAV. SQL Server is the relational database management system that provides the storage and transaction processing system which serves as the back end for Microsoft Dynamics NAV.  This is the lowest level of the 3-tier architecture of the NAV solution and it’s configuration can make or break the end user’s experience and responsiveness.

“Roadside Assistance, are you there?”  SQL Server Maintenance is central to NAV Performance Tuning.  Just think of all of the data stored in SQL. Purchase orders, sales invoices, shipping manifests, and production orders are just a few examples.  All of this data needs to be written to – and retrievable from – your database(s), and therefore requires a healthy SQL server. This is where the risk lies. It’s the actual write process, not just bits flowing down a wire or over a fiber optic connection – which is actually pretty fast, but the write process that poses the most risk.

Failing to maintain your SQL Server can lead to serious problems. Especially when companies have larger volumes of data being written to and read from your database. It’s at these volumes that they start to hit disc and as a result, experience a slowdown in NAV. This not only leads to a loss in productivity and greater use of resources but also increased frustration on the part of employees.

However, it can go from bad to worse and disks, log files or other low level SQL resources can become full or completely consumed. When this happens SQL takes a nose dive and stops in its tracks. NAV is now offline and business processes have ground to a halt. This is the nightmare scenario for companies; everyone is in a panic and the problem often cannot be solved quickly. It’s not as easy as changing the configuration and off you go. If you don’t think about it advance, it’s going to take time and money to fix the problem. You need to hire emergency IT staff and in the worst of cases need new hardware ordered and couriered in. And what if this happens at 4 am on a Sunday morning?

What do you mean, I need to check my oil? SQL Server Maintenance is a routine process that every company should have in place. However, this is not always the case. For example, smaller companies without a dedicated IT staff are often left high and dry after implementation. A good partner should communicate the importance of monitoring and ensure that there is a maintenance plan in place. However, larger companies are also at risk, especially if they have grown quickly. It’s often the case that their business has grown but they haven’t grown their IT team. These companies don’t understand that this is a major risk until it hits them in the face and their SQL stops.

It’s worth nothing that Cloud customers do not need to be concerned with SQL Server hardware maintenance. One of the benefits of the cloud is that backup levels and performance options are settings; in other words, someone else is taking care of this for you. However, if you have servers hosted on your own hardware, you own the problem whether you are aware of it or not. Consequently, companies who don’t want to deal with monitoring and maintenance should consider moving to Azure.

s previously mentioned, SQL Server Maintenance can be equated to taking care of your car. In both cases, performance is something you should be spending a little bit of time on, all the time. This periodic monitoring will go a long way towards preventing a shutdown.

But what are the kinds of things you should be monitoring? Not to worry, we’ve created “6 Tips for SQL Maintenance” to help you in your monitoring and maintenance efforts.

1. Check SQL Disk Configuration
2. Check SQL Server Resource Configuration
3. Maintain Indices
4. Log file management
5. Filegroups Management
6. Database Backup

As Benjamin Franklin said, “An ounce of prevention is worth a pound of cure.” At Catapult we’re here to help. Whether you simply have a question about the above guide or you are interested in learning more about how we can help you maintain the health of your SQL server, we’re here to answer your questions.