Avoid These 4 Common DBA Mistakes

Data is king in today’s economy, and DBAs are on the front line of data security and protection. Because of the sensitive nature of the work, database administration requires extreme attention to detail and leaves little room for error. But DBAs are human and humans make mistakes.

Here are four common mistakes DBAs make that can negatively affect database performance and data security and how you can avoid them.

Mistake #1: Not Testing Backups on Regular Basis

Ensuring databases are backed up and readily accessible in the event of a catastrophic failure is a DBA’s biggest responsibility. Follow these best practices to keep your backups secure, accessible, and, most importantly, working in the event they are needed.

Store your backups in the cloud: Keeping your backups in the cloud is ideal because they are easily accessible from any location and your cloud storage provider likely has automatic failover capabilities that protect your backup in the event of fire or natural disaster.

Restore your backup in a test environment regularly to ensure it will work if/when you need it: Your backup is only helpful if it works, and the middle of a crisis is the wrong time to find out there’s a problem and your data is lost.

Document the backup strategy and process in plain language anyone, not just a DBA, can understand and follow: When disaster strikes, you never know who will be around to get the system back up and running. Err on the side of too simple when writing your disaster recovery and backup plan, just in case.

Mistake #2: Disabling Page Verification

There is a reason SQL Server’s page verification default is never “none.” Setting PAGE_VERIFY to none leaves your data files open to undetected corruption.

Instead, set PAGE_VERIFY to CHECKSUM so SQL Server writes a CHECKSUM to each page as it goes out to storage, then recalculates and compares the CHECKSUM again when the data is read from the disk.

To help keep things simple, Brent Ozar explains how to generate a script to change the page verification setting in all of your databases.

Mistake #3: Not Ensuring Proper User Authentication/Security

User authentication and stringent security practices are crucial to maintaining the integrity of your databases. To create a secure environment, at the bare minimum, you should require the following practices:

• Limit the number of sysadmins
• Force strong passwords
• Grant the least amount of permissions possible
• Implement multi-factor authentication
• Put databases behind a firewall

Mistake #4: Not Continually Monitoring Your Database

If you aren’t closely monitoring your databases, there is a 100 percent chance performance will suffer. 

Although it is impossible to completely eradicate slow queries and less-than-ideal system performance, there are steps you can take to minimize their negative effects on your users.

Set up alerts to notify you when thresholds are exceeded: Properly configured alerts not only improve performance but also save your sanity. Today’s monitoring tools allow you to set specific rules so you don’t spend your nights responding to false alarms or low-impact issues that could easily wait until morning. Many solutions also offer mobile monitoring, so you can assess and start diagnosing problems from any location.

Adopt a proactive rather than a reactive monitoring strategy: Conducting regular system health checks and tracking performance analytics over time are two of the top proactive measures you can take. Preemptively addressing potential problems or stopping a performance issue quickly and efficiently before users are affected is always preferable to non-stop firefighting.

Take full advantage of database monitoring: To get the most ROI and protection from your monitoring solution, be sure you focus on five key areas:

• Understand the workload
• Pick a tool that meets your business needs 
• Track the key performance metrics
• Avoid bottlenecks
• Collect (and use!) performance data

Other Ways to be a Superstar DBA

Avoiding these four common mistakes will cement your reputation as a company MVP, but there are a couple of additional habits DBAs can adopt that will help you contribute even more to protecting your organization’s valuable data and keep your system performing at an optimal level.

Automation: Automating recurring and repetitive tasks like database provisioning, patching, and diagnostics lowers the risk of human error and frees up the DBA to work on higher-value tasks that require human input.

Maintenance: It is easy to overlook or put off maintenance when higher-priority work crops up. Create checklists to help you stay on top of routine maintenance tasks such as backups, alarm management, and defragmentation. Keeping database maintenance up to date is a great way to mitigate performance issues before they start.