Everyone makes mistakes, even experienced DBAs (database administrators). And the interesting thing about the mistakes DBAs make is that they don’t all relate to technology. As you will see, many of the top DBA mistakes happen due to immature policies and practices.
Take these as an example:
- How much does downtime cost the company?
- When are the busiest periods of activity for your database during a given workday, month, or even year?
- What are the steps of a typical process?
Rather than including errors relevant to database development and design, we’ll strictly focus on common administrative errors made by DBAs, especially regarding how teams interact and build processes.
Here are the top 10 SQL Server mistakes DBAs should avoid.
10. Storage: Space vs. I/O Speed
The first mistake on our countdown is that DBAs typically think of storage only as raw disk space and not I/O speed. This happens because we pay for that space by the gigabyte, whether it is for HDDs, SSDs, or cloud storage.
Consequently, DBAs rarely discuss what the throughput, workload, or performance needs to be. Many DBAs don’t balance the I/O needs of different kinds of applications. For example, OLTP applications need fast random I/O, while BI applications and backup/recovery processes need fast serialized IO.
When we are only talking about space, we aren’t talking about the specific level of performance that our end users require.
9. Business Ignorance
The mistake of business ignorance comes in two different forms. Many DBAs feel that their job is to only work with databases. They don’t think about the machine as a whole; they only think about backing up databases, restoring them, securing them, performing preventative maintenance operations on them, etc. Ultimately, they don’t know how SQL Server works.
Secondly, many DBAs don’t consider that they are actually guardians of the true corporate asset of IT: data. You need to know how the business uses the data and who will care when a particular application is down.
How much does the downtime cost the company? What are the busy and slow cycles of these systems? When are the busiest periods of activity for your database during a given workday, month, or even year? Without this knowledge, DBAs are IT drones who cannot add business value at their workplace.
For more tech info on learning about the internals of SQL Server, visit PASS.org, SQLPerformance.com, and TechRepublic.com.
8. No Troubleshooting Methodology
Another common mistake that DBAs make is that they often don’t have a methodology for troubleshooting. What’s your checklist? What steps do you follow in a typical process? Do you have a process that ensures optimal outcomes when SQL Server or Azure SQL starts to have problems?
You don’t have to be a mastermind at troubleshooting to be a successful DBA. But you need to have a step-by-step checklist that you follow to perform root cause analysis. If you don’t, you’re going to waste time and energy and miss all kinds of errors and problems. You’re going to miss a clue that a system is heading towards a catastrophic failure. If you have SLAs, you’re going to miss them and have poor response times.
You also lose credibility when you can’t solve problems. If you want your opinion as a DBA to be respected and valued, don’t make this mistake.
7. Going With the Defaults
Another big mistake is going with the default configuration settings. The SQL Server defaults are designed to focus on reliability and not database performance. It’s designed so that a non-DBA can buy it, install it, and run it for years and not have it crash.
But because you’re a DBA, you can do many things to change those defaults and improve performance. There are server- and database-level configuration settings that you can leverage, such as max degrees of parallelism, cost threshold for parallelism, or optimize for ad hoc workload.
I have defaults that I change on any production system I manage. For example, you should never go with the defaults on memory settings, files/filegroups, and how the databases grow. Remember—the defaults are designed to give SQL Server maximum uptime, but not maximum performance.
6. Security as an Afterthought
One of the biggest mistakes within IT teams is that the development team, DBA, and network admin team, all think that the other groups are responsible for security. Consequently, no one is actually taking care of security. It’s essentially a lot of finger-pointing with no one taking the reins.
I’m not telling you to be a security expert, but you need to make sure someone is responsible for security. And since SQL Injection attacks are one of the most damaging of any kind of security breach, it is very important for you to pay attention to at least the security basics.
To get an idea of the minimal levels of database security basics, check out my video and slides from SQLBits VIII entitled Understanding and Preventing SQL Injection Attacks.
5. Inadequate Automation
Inadequate automation happens a lot in small enterprise shops where you have a half dozen SQL Servers. But it’s the kind of thing that has more implications than it first seems. In a nutshell, automation enables you to improve accuracy, reduce mistakes, and greatly scale up the number of SQL Server or Azure SQL databases that a single DBA can manage.
I always encourage people to work smarter and not harder. Automation through scripting lets you do that. Once you have a working PowerShell or T-SQL script, for example, you can roll it out to as many applications as you want.
The SQL Server community widely promotes and shares automation best practices. One fantastic example that I love to evangelize is dbatools.io, an Open Source community effort by folks worldwide. It contains more than 400 scripts, all written in PowerShell. Be sure to check it out.
4. Wrong Feature or Technique for the Job
This mistake happens a lot when you have a junior DBA, accidental DBA, or jack of all trades DBA who is also a developer, etc. They tend to get excited when there are new features to test and implement. To be a good DBA, you must remember that you’re the performance and reliability engineer for the databases you manage. The data is the most important thing, not the new cool feature that was just released.
You manage the risk to which your databases are exposed, and every new “shiny” feature carries a measure of risk when implemented. Your job is to ensure that the data is safe and isn’t going to get corrupted, break, or get overly complex—so avoid new fads.
Don’t forget that historically, Microsoft has rolled out new feature sets but not supported all of them in the long term. Older examples include English Language Query and Notification Services, while newer examples include Master Data Management and Data Quality Services. Those features haven’t seen a new release or innovations in a long while.
As a DBA, hold your fire and wait until you know that a new feature set will be supported for the long run inside SQL Server.
3. Apathy About Development, DevOps, and Change Management
Many DBAs don’t care about code, but they should. T-SQL code might be in the realm of your dev team, but it makes a big difference to how any SQL Server database performs. I encourage you to demand code reviews to identify the worst practices. Code reviews become essential if you ever move to the cloud because in the cloud, you pay for CPU, I/O, and data egress.
If you have poorly written code in an on-premise database, it’s all sunk costs, so it isn’t as important when it doesn’t perform optimally—as long as end users are satisfied. But in the cloud, if it doesn’t perform well, you actually pay more because of all the resources it consumes. You usually don’t have to be the person who fixes the code, but you should be able to identify it and make sure your dev team avoids worst practices.
I also see that many DBAs don’t care about change management, which is a big mistake. I’ve seen DBAs spend days and even weeks trying to fix things when change management of the database code goes sideways, for example, when a code deployment breaks important behaviors in an app that then requires a roll back. I encourage you to stay on top of change management and ensure that you treat your database artifacts and regular application artifacts with equal respect.
I recommend reviewing my book SQL in a Nutshell, as it contains the experiences of professional database administrators and developers who have used variants of SQL to support complex enterprise applications.
2. Inadequate Preventative Maintenance
Another big mistake I see DBAs make is around preventative maintenance. They don’t realize that SQL Server requires regular care and grooming.
Preventative maintenance provides all kinds of upsides. The only downside is that you must find time to do it when it doesn’t impact the system’s users.
The common preventative maintenance steps you typically need to take are corruption checks and backup and restore verifications. You need to perform database consistency checks for corruption, as well as fragmentation maintenance and index statistics maintenance.
1. Backups and Recovery
The number one mistake of SQL Server DBAs is believing you can recover a database just because you have a backup. That is not true. DBAs tend to perform the backups (and trust them), and then find that three months later, when they need to recover it, it won’t work. SQL Server will allow you to take backups of databases that have corruption in them. But it won’t allow you to restore a corrupted database!
I strongly encourage you to follow the example of emergency first responders. Full-scale drills! On a regular basis (quarterly in my case), make a database backup and perform a full recovery test as part of your normal processes. Perform backups and full recovery tests at least twice a year.
Also, keep in mind that you must perform these tests because of Sarbanes-Oxley (SOX), GDPR, and other data compliance laws that require you to have access to older data. SOX says you have to be able to go back seven years to recover specific kinds of financial data. Make sure you test data from that long ago! It will be a huge anxiety reliever.
Avoid Mistakes by Keeping These Scenarios in Check
As a DBA, don’t forget your job is to be the guardian and protector of the data—all the time, every time. While only a few of the blunders we discussed were due to tech skills, most are administrative, process, and business issues. If you keep these scenarios in check, you’ll be much more successful and become an extremely valuable asset to your company.
