Why 9 Out of 10 SQL Servers Aren’t Configured with Best Practices

“The nice thing about standards is that you have so many to choose from,” quipped renown computer scientist Andrew Tanenbaum.

In the SQL Server world, we have industry best practices. These are guidelines that most every knowledgeable database professional will agree is a good idea or a good baseline. Sure there are exceptions, specific tweaks to accommodate certain workloads, but the best practices are a generally a good starting point. Then you can customize for your needs.

As part of our practice, we regularly examine SQL Server instances to ensure they are configured with industry best practices in mind. We check configuration settings, maintenance jobs, and alerts, among other things, to help ensure the systems are protected and highly available.

Our findings: 9 out of 10 SQL Server instances we assess do not adhere to these industry best practices. Now, this isn’t a scientific study conducted by Forrester Research. Rather it’s our direct experiences working with hundreds of SQL Servers and clients.

Why Aren’t Most SQL Servers Configured with Best Practices?

We’ve found five predominant reasons for this.

SQL Server is so easy to set up

Many years ago, I taught a bunch of Oracle DBAs how to install, configure, and administer SQL Server. The hands-on lab allocated an hour for the installation, They were incredulous. “It can’t be done that quickly.” But it was. Find the media, double-click Setup, click Next-Next-Next, and it’s installed. Unfortunately, that’s where many people stop. They accept all of the defaults and never go back to configure the instance properly. The defaults are not best practices.

SQL Server just works

Even with the defaults, SQL Server is pretty responsive and resilient. It’s a testament to the quality of the product. You can neglect it, take it for granted, and still it keeps on keeping on. We’ve assessed (and subsequently remediated) systems that haven’t been touched in years. No patches applied. No maintenance performed. Nothing but users hitting it every day. Yet, it still works. That doesn’t mean it’s good or safe. In fact, we are frequently called into a new client because something has gone terribly wrong and the company needs help getting SQL Server out of the ditch. The better care you take of your SQL Server, the better it will be to you.

Introducing the accidental DBA

Many IT organizations don’t have the need for a full-time, dedicated Database Administrator. They only have a few SQL Server instances with 10 or 20 databases. Hiring a DBA isn’t in the cards. But someone has to care for the databases, has to think about backups and restores, disaster recovery, and high availability. Someone has to throw hardware at the problem when performance suffers. That’s when the CIO, a network admin, or an application developer steps up to do it in their “spare” time. These are smart, capable people with no spare time to give to SQL Server. “If an ain’t broke, don’t fix it” becomes the defacto approach since the accidental DBA is super busy with her day job.

Duct-taping with hardware

When a performance problem creeps into a production server, often the first recourse is to simply throw hardware at the problem. Reports taking too long? Add some CPUs! Imports not processing fast enough? Get faster disks! Application not responsive enough? Increase the RAM! Hardware can cover a multitude of SQL sins. But adding hardware gets expensive. Although the cost of hardware continues to go down, licensing costs do not. Adding an additional 8 CPUs to an Enterprise Edition of SQL Server can be expensive. And even if you have the really deep pockets to do it, hardware doesn’t address the root cause or protect your system any better.

Everyone has their own standards

Many IT organizations hire capable DBAs and let them do their jobs. That’s good. However, without a common playbook and set of standardized scripts, each DBA will handle things slightly differently, or worse, manually, and the result will be widely varying configurations. No two instances are the same. One instance may have 1 tempdb file on the C:\ and another has 8 somewhere else. One instance may have a patchwork of scheduled jobs and another may not have any. It’s like a box of chocolates. That makes troubleshooting difficult and performance unpredictable.

So, What Should You Do?

Best practices are exactly that, best practices. They are settings and techniques that should be adopted and applied to your SQL Server instances. Then you can tune based on your specific needs.

So, what should you do?

The first step is to know what you’re dealing with. Just how big is this elephant you’re about to eat? Assess your SQL Servers to see just how far from the best practices they really are. Then you can make a plan for updating them. Check out Is My SQL Server Configured Properly? for more information on assessing your SQL Server.

When we do assessments for companies, we have a standard script we run on each instance that looks for departures from best practices. That allows us to effectively collect, analyze, and report on the state of each instance. Sometimes there are good reasons for the variances and we discuss the rationale behind those decisions. Often we find that some remediation is recommended and we help to prioritize the findings so you’ll know where the biggest bang for your buck is. Learn about our SQL Server Assessments.

Whether you use our services or not, I’d recommend that you adopt a similar approach: identify what needs to change, prioritize the items, create a plan to remediate, and then work the plan.

In a coming post, I’ll share some of the things we look for when we assess an instance.

Want to work with The Sero Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a call with us to get started.