10 SQL Server Mistakes That Database Administrators Should Avoid

We all make mistakes, even experienced DBAs (database administrators).

 

 

In fact, not all of the mistakes DBAs make are related to technology.

 

Many of the most common DBA mistakes occur because of undeveloped policies and practices.

 

Consider the following:

 

• Which are the busiest times of day, month, or even year for your database?

 

• How does a typical process work?

 

• Is there a cost associated with downtime for your company?

 

Our focus will be solely on common administrative errors made by DBAs, especially related to how teams interact and build processes, rather than errors related to database design and development.

 

The following are the top ten SQL Server errors that DBAs should avoid.

 

1. Automation is insufficient

 

Small organizations with a half dozen SQL servers are prone to insufficient automation.

 

The thing is, it has more implications than it appears.

 

By automating SQL Server or Azure SQL databases, a DBA can enhance accuracy, reduce errors, and greatly scale up the number of databases he or she can manage.

 

Working smarter, not harder, is always my advice to others.

 

 

This can be achieved through automation through scripting.

 

For example, once you've developed a PowerShell or T-SQL script, you can deploy it to as many applications as you like.

 

Best practices in automation are widely promoted and shared by the SQL Server community.

 

dbatools.io is a brilliant example and I love evangelizing it since it's an Open Source community effort involving many people worldwide.

 

Over 400 PowerShell scripts are included in it. I recommend you check it out.

 

2. Inappropriate feature or strategy for the task

 

A lot of times you make this mistake if you have a junior DBA, an accidental DBA, or a jack of all trades DBA.

 

They often get excited about testing and implementing new features.

 

It is important to remember that as a DBA, you are also the performance and reliability engineer for the databases you handle.

 

The most important thing is the data, not the latest cool feature.

 

Whenever you implement a new "shiny" feature, it carries a certain amount of risk.

 

Make sure the data is safe, doesn't break, doesn't get corrupted, and doesn't become too complex.

 

Stay away from the latest fads.

 

Microsoft has historically rolled out new capabilities but did not support them all in the long run.

 

There are older examples such as English Language Query and Notification Services, and more recent examples such as Master Data Management and Quality Services.

 

Individually, those features haven't been updated or improved in a long time.

 

When using a new feature set inside SQL Server, you need to hold off on using it until you know it is supported in the long run.

 

3. Disinterest in change management, development, and DevOps

 

Code is often overlooked by DBAs, but it plays a critical role in SQL Server databases.

 

T-SQL may belong to your developer team, but it affects how they perform.

 

The best way to identify the worst practices is to demand code reviews.

 

Code reviews are particularly important if your organization ever moves to the cloud since, on the cloud, you pay for CPU, I/O, and data egress. 

 

As long as your end users are satisfied, it doesn't matter if your database code is poorly written in an on-premise database.

 

However, in the cloud, if it fails to perform, you end up paying more since it uses so many resources.

 

If you can identify the issue and prevent it from happening, it is your responsibility to ensure your team avoids using worst practices.

 

I've also noticed that many DBAs are unconcerned about change management, which is a huge mistake.

 

There have been instances where DBAs have spent weeks and even months resolving issues that came up as a result of changes in the database code, for example when a deployment broke an app's behavior that forced a rollback.

 

Maintaining a constant awareness of change management and treating database artifacts as well as regular application artifacts with equal respect is crucial. 

 

4. Preventative Maintenance Is Inadequate

 

DBAs often overlook the importance of preventative maintenance.

 

They don't realize that SQL Server requires regular care and attention.

 

Maintenance has a lot of benefits, but the only downside is finding time to do it when it does not affect users.

 

You typically need to perform corruption checks and backups and restores as preventative maintenance steps.

 

In addition to checking for corruption in the database, you need to maintain fragmentation and index statistics.

 

5. Recovery and backups

 

 

In SQL Server DBAs, the number one mistake is believing that if you have a backup, you can recover it.

 

But this is not the case. DBAs often backup their systems and trust them, but then discover three months later that it won't work if they need to recover.

 

You can take a backup of a corrupted database using SQL Server. But you can't restore a corrupted database via SQL Server! 

 

I highly suggest you follow the lead of emergency first responders.

 

Drills at full scale! Make a regular backup of your database every quarter and perform a full recovery test as part of your normal workflow. 

 

At least twice a year, backup data and test full recovery.

 

Remember that Sarbanes-Oxley (SOX), GDPR, and other compliance laws require that you have access to older data so that you may perform these tests. 

 

For certain types of financial data to be recoverable, you have to go back seven years. Test the data from that long ago! You will be relieved of a lot of anxiety.

 

6. Storage: I/O Speed vs. Space

 

It is common for DBAs to think of storage as only raw disk space, not I/O speed.

 

Whether it is hard disks, SSDs, or cloud storage, we pay for that space per gigabyte.

 

Therefore, DBAs rarely discuss the throughput, workload, or performance of applications.

 

DBAs often don't balance different applications' I/O requirements. 

 

Application developers need fast random I/O for OLTP applications, and design and development teams need it for BI and backup/recovery processes.

 

Whenever we only consider space, we are not considering the performance level required by our end users.

 

7. Ignorance in Business

 

Many DBAs believe that solely working with databases is their duty.

 

The mistake of business ignorance occurs in two different ways.

 

The majority of users don't consider SQL Server as a whole; they only think about backing up databases, restoring them, securing them, performing preventative maintenance operations on them, etc.

 

As a result, they don't fully understand SQL Server.

 

Many DBAs overlook the responsibility that they have as guardians of the company's greatest asset: data.

 

You need to know how the company uses the data and who is affected when an application goes down. 

 

Do these downtimes cost the company anything? Are there busy or slow cycles with these systems? How busy is your database on any given day, month, or even year? DBAs cannot add value to their workplace without knowing this information.

 

8. There is no methodology for troubleshooting

 

One of the biggest mistakes that DBAs make is that they don't have a method for troubleshooting.

 

How would you describe your typical process? Are you able to ensure optimal outcomes when SQL Server or Azure SQL experiences problems? 

 

 

A successful DBA doesn't need to be an expert at troubleshooting.

 

But they must have a step-by-step checklist to perform root cause analysis.

 

You'll waste time and energy if you don't, and you'll miss all kinds of errors and problems.

 

It's easy to miss the warning signs that a system is about to fail.

 

If you have service-level agreements, you'll miss them and have poor response times.

 

9. Leaving the Defaults in Place

 

You shouldn't just rely on the default SQL Server settings.

 

These are designed for reliability and not for database performance.

 

The program is designed so that even a non-DBA can buy it, install it, and use it for years without it crashing. 

 

Due to your position as a DBA, you have many options for changing these defaults.

 

You can configure server- and database-level settings, such as the maximum degree of parallelism, the threshold for parallelism, or optimize for ad hoc workloads, according to your needs. 

 

Whenever possible, I change the default settings on the production systems I manage.

 

For example, you should never use the default settings on memory settings, files/filegroups, and how a database grows.

 

Keep in mind that the defaults are designed to give SQL Server maximum uptime, but not maximum performance.

 

10. Leaving security to the last minute

 

IT teams sometimes make the mistake of assuming that security is the responsibility of the development, database, and network administration teams.

 

Because of this, security is not being dealt with. Basically, everyone points fingers, but no one takes the initiative.

 

Obviously, you don't have to be an expert in the field, but you should make sure someone is in charge of security.

 

You should pay attention to the security basics because SQL Injection attacks are among the most damaging kinds of security breaches.

 

Keep These Scenarios in Mind to Avoid Mistakes

 

 

Remember that your job as a DBA is to be the data's guardian and protector at all times.

 

While only a few of the errors we discussed were caused by a lack of technical knowledge, the majority were caused by administrative, method, and business concerns.

 

You'll be a much more successful employee and a very valuable asset to your company if you accomplish these tasks.