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.
Related Posts
As more businesses deploy innovative and market-disturbing IT technologies and software solutions, they also make room for new cybersecurity threats. Cybercrime has gone sky-high in the last three years and continues to grow.
AI has been around for a while and the automation of numerous business processes and systems has arguably been the precursor of the current debate and the basis for practical development of AI.
There is so much information out there to help people struggling with too much clutter in their homes, but not nearly as much about digital clutter.
Confused about the difference between a desktop app and a web app? Look no further! This guide outlines the advantages and disadvantages of both.
As the digital world continues to expand and more businesses are now required to have an online presence, there is no doubt that technology will play an even more prominent role in the future.
Comments
comments powered by Disqus