September 12, 2018 | Category: Articles

As I work on various MS SQL databases and look under the hood, I often see databases that are neglected and are not properly maintained nor cared for. Just like any other IT System, proper maintenance needs to be performed to ensure optimal performance. The most common areas where there is room for improvement is the version of SQL being used, inadequate backup frequency, indexes not being rebuilt, and improper log file maintenance.

SQL Express Edition

Although this database server offering from Microsoft is free, it comes at the cost of not being able to create maintenance plans (a feature that allows for automated database backups, database index rebuilds, etc.). If you know the SQL command line syntax you can create your own custom scripts to perform these tasks, but this can be cumbersome and hard to maintain. If having an optimally running database is important to you, then it might be wise to invest in the paid versions of SQL (Standard or Enterprise).

My database is being backed up once each day. Isn’t this good enough?

In some cases, having a daily backup of your SQL server might not be good enough. Consider an e-commerce website that tracks and stores thousands of orders daily. If you were only backing up your SQL database once per day you run the risk of having to re-enter and recover all of the transactions. That would be a nightmare! Now consider the reverse scenario where the data hardly ever change. In that case you could get away with one backup per day. Your backup strategy largely depends on how many changes are performed daily and what the business impact is if data must be re-entered. If losing 1000+ entries will have a major impact, then it is best to do hourly backups or, better yet, enable full recovery mode, which will allow recovery to any given moment in time.

My Indexes are not being rebuilt. So what?

What is an index and why must it be rebuilt? Think of an index as a table of contents for a database. Each index is a quick pointer to the data (or page) you might be looking for. Over time the indexes become fragmented because data changes or increases in size. To get an idea of what I’m talking about just think about reading a book. Reading a book is easy and straight forward, right? As you read the story you finish one page and then move on to the next. Think about how much harder it would be if the pages were not in sequence. Let’s say the story starts on page 1 but then forces you to page 13, then back to 2, page 56 and so on and so forth. This is how your database can behave over time when proper maintenance is not performed. It becomes less and less efficient, just as it would take a lot more time to flip through random pages to finish reading a story. By rebuilding your indexes, you ensure the story can be read sequentially resulting in a much better — and ultimately shorter — story.

Help, my SQL log files are running away!

Often, I see databases that have runaway logs. This is a condition where the database log files grow and use up free space. In some cases, these log files grow enough to fill up the hard-drive causing application crashing or unresponsive behavior. What causes this? The primary cause is full recovery mode and lack of log backups. This condition is easily avoidable, but you might need to spend some money for the paid version of SQL, which will facilitate the backups.

So now you know

You don’t know what you don’t know. Many companies just don’t maintain their databases when some easy changes could ensure things will run optimally. It might be necessary to pay a little up-front for SQL licensing to prevent unnecessary downtime later, such as slow database performance or crashed applications due to disk space issues, all of which can easily be prevented. Also, by knowing your business application and the amount of changes that occur daily you can set your backup and recovery strategy accordingly to minimize business impact.

E Squared C is a managed service provider (MSP) providing professional IT services for businesses in Nevada and California. By partnering with E2C, your business gains a team of experts who solve IT problems with reliable, efficient, and secure IT management services. Contact us to find out how our experts can help your business!