The Big 4 SQL Server Mistakes Made by DBAs

Managing an entire SQL server and database can be an incredibly stressful job position, but many DBAs make it harder on themselves by not figuring out simpler solutions for certain problems.

In this article, we’re going to go over 4 common, big mistakes made by DBAs, and alternative methods of DBA management.

Not considering I/O load

While disk space is important for DBAs to consider, it becomes a problem when it’s the only consideration. Thinking only about the volume of data a disk can contain will lead to significantly slowed down workload, and time wasted addressing issues such as:

• Insufficient disk spindles.
• Improper choice of RAID type, channels, and controllers.
• Inadequate fault tolerances.

For best performance, you want to maximize sequential write speed for all the transaction logs (as well as have access to dependable SQL server monitoring tools). If you've got a lot of databases with all their transaction logs sitting on one volume, you'll have an increased amount of seeking as SQL Server writes to different transaction logs, and SSD will become more beneficial.

tempdb should have a single transaction log file on a single volume with the highest sequential write speed possible. There will be little to no random access. You may find 15K drives that beat SSD here.

Data files, rather counterintuitively, are slightly less important on a transactional system, but only if you've supplied adequate RAM to minimize visits to the disk. If this is a large analytical/read-mostly system, it's likely going to be using the disks more here, and SSD will probably make sense.

If you're upgrading an existing system, you should do a bit of I/O profiling to see how it's using the disks currently, and get a better idea of where to maximize performance.

Focusing only on IT

A DBA should not only focus on the IT side of things, but know a bit about the business as well, to provide better support for the business. This means taking into account things like business cycles, benchmarks, the costs of downtime and who it can affect.

Not enough preventative maintenance

Many issues that DBAs face can be mitigated by performing regular preventative maintenance. Having a solid maintenance routine in place will allow you to spend just a few hours per month, more or less, on preventing issues from happening, rather than the much more stressful time wasted of hunting down and fixing those same issues.

While each SQL server is different, this can serve as a general preventative maintenance checklist:

• Regularly update the Windows server and SQL server instances.
• Validate backups and recovery strategy for what-if scenarios.
• Verify the configuration settings of the SQL server and environment.
• Benchmark the server’s performance and health, including the database and instance.
• Go over and configure your server baselines as necessary.
• Review your current capacity plan to predict expansion.
• Submit a status report.

Not tightening security for current threats

The landscape of cyber security is constantly shifting with new and evolving threats, but SQL injection attacks are still a common technique used by cyber criminals. Protecting your database against these attacks relies on a few tweaks and security protocols, which include:

• Parameterizing every query passed on to the database.
• Opt for properly configured stored procedures over direct dynamic SQL.
• Sanitize all queries before they are executed against the database.
• Never pass string values in the front-end application.

Of course, SQL injection attacks aren’t the only way your applications and database can be compromised, so other additional security measures you’ll want to implement are:

• Hardening the Windows server where your SQL server resides.
• Limit SQL database components to only those required.
• Follow the principle of least privilege for service accounts.
• Create groups and roles for managing permissions more easily.
• Disable the SQL server browser service.
• Install SQL server updates as soon as possible.
• Routinely check for logins no longer necessary (former employees, etc.)
• Use stronger authentication such as mixed authentication (Windows auth + SQL server).
• Audit all access and change requests to your SQL server and database.

Treating databases as Visual Studio projects

Even if your SQL database is created in Visual Studio, it’s more effective to treat your database as a code repository. For example, by placing your database on GitHub, you’ll be able to take advantage of things like version control, SQL source control, and database object scripts.

In other words, treating your database as a code repo rather than a simple database will give you a lot more flexibility and control. By putting your database code in a cloud-based service, like Git, you’ll be able to streamline database development and deployment cycles down the pipeline.