The Top 10 Mistakes Made by SQL Server DBAs

Aug 10, 2015

By Kevin Kline

I had the recent good fortune to undertake a speaking tour in which I talked to eight chapters of the Professional Association for SQL Server (PASS) throughout the American Midwest and South. (You can read details about this speaking tour, called the SQLVacation, here.)

One of the most popular sessions of the speaking tour was Top 10 Admin Mistakes on Microsoft SQL Server. (You can get all of the presentation slide decks and demo scripts from my blog.) The SQL Server world has its own set of challenges when it comes to database administration. One of the biggest challenges is that there are never enough skilled DBAs. Consequently, many people who are not true DBAs are pressed into service, perhaps a Windows SysAdmin or the lead developer on an application using a SQL Server back end, so-called “accidental DBAs.” That means that people doing DBA work on SQL Server frequently do not know the fundamental technical details or the processes and practices commonly used by experienced enterprise DBAs.

What ARE the Top 10 Mistakes of SQL Server DBAs?

Now, keep in mind there are a lot of duties that DBAs frequently perform that aren’t truly administrative in nature. For example, index tuning and query tuning are frequent tasks for skilled DBAs. But I would argue that both of those activities are part of the application development process, not administration. To me, administration is all about the care and feeding of your SQL Server databases—securing them, backing them up, ensuring optimal configuration settings, and so forth.

When we scope our mistakes accordingly, only the first three DBA blunders are due to inadequate technical skills; the following seven DBA blunders are due to a lack of strong process and incomplete understanding of business issues.

Thinking about the storage subsystem as space, not I/O throughput: Most DBAs are under a lot of pressure to control costs so they frequently think about their storage only in terms of the volume of data it can hold compared to its cost. Sadly, that often leaves us with remarkably slow storage that cannot keep up with the workloads.
No troubleshooting methodology: Many inexperienced DBAs do not have a checklist to follow when things go wrong. The good news is that you don’t have to create one from scratch. Several top SQL Server bloggers have provided this information, as does one of my slide decks.
Blindly accepting the SQL Server default settings: SQL Server defaults are designed to make the database as easy to set up as possible, but not to ensure maximum performance or longevity. Inexperienced DBAs tend to leave all of these settings as is, resulting in long-term performance degradation.
Security as an afterthought: Lots of DBAs do not pay attention to security. It’s not in their job charter. On the other hand, databases are frequent targets of hacks, such as SQL Injection attacks. So if you don’t pay attention, who will?
Wrong feature for the job: A common behavior of many IT people is to focus on the next cool new thing. However, DBAs shouldn’t care about “new” nearly as much as they should care about “stable.”
Apathy about change management: Databases are not Visual Studio projects. So a lot of DBAs don’t put any thought into managing their databases as if they were code repositories when, in fact, they are absolutely code repositories. Manage them as such!
Lack of preventative maintenance: SQL Server needs a variety of preventative maintenance jobs to run on a regular basis (for example, updating index statistics and defragmenting indexes). DBAs must set up and configure these jobs to keep their SQL Servers running in tiptop condition.
Lack of automation: Without automation, DBAs risk having to deal with error-prone processes, omissions, and forgetfulness; the inability to scale the environment to multiple servers; and time constraints imposed by frequently being in “firefighting” mode.
Backups do NOT mean recovery: Just because you’ve taken a database backup does NOT mean you have a recoverable database. The only way to be certain you can recover and use a backup is to actually recover it. Make sure recoverability testing is part of your routine!
Business ignorance: DBAs sometimes don’t realize that they’re more closely aligned to the business than most every other IT staffer. Because of this, DBAs don’t realize that it’s critical that they know all about the natural cycles of their business, the nature of their data, and need for strong customer services in their profession.