Finding and fixing problems typically takes up a lot of your time as a database administrator. Both users and developers make mistakes that can keep you pretty busy. Despite these distractions, you can’t neglect the critical day-to-day responsibilities that enable you to protect enterprise data and keep SQL Server instances running smoothly. Not all of these day-to-day tasks are equally critical. Neglect of some daily tasks and procedures can lead to headaches, others can lead to major financial losses and a DBA looking for a new job. Let’s take a closer look at four of the most critical mistakes that DBAs must avoid.
1. Not testing your backups – Every DBA knows the importance of taking database backups. No matter what high availability or disaster recovery technologies that you may have in place, backups are the foundation of your data protection strategy. Database backups are your last line of defense. The worst time to find out that your backups don’t work is when you’re trying to restore them. To make sure you don’t make that mistake, you should use the BACKUP VERIFYONLY command to verify that a backup can be restored. In addition to verifying a backup, it’s also a good idea to use the CHECKSUM option with all your backups and restores. The CHECKSUM option performs extra data validity checks to determine if a database is corrupted. Finally, it’s also a good idea to be sure to perform regular database restores on a non-protection system to be sure your backups are working.
2. Not restricting security access-- Security is a critical component for all enterprise level SQL Server implementations. However, security is often overlooked and not given the proper level of consideration by some database professionals. Granting higher levels of permissions to people or applications than are really needed can result in data loss, data exposure, data theft or even unintended database corruption. One common mistake is granting developers administrative privileges and allowing them to directly access production data. This is often done to expedite the development or debugging process but it is not a best practice. Database security should follow the principle of “least privilege,” where only the lowest levels of permissions are granted to users, developers and even administrators.
3. Not keeping up with regular database maintenance – While SQL Server has many auto-tuning and self-healing capabilities, you can’t make the mistake of thinking the database will run itself. There are a number of important database maintenance tasks that you need to perform on a regular basis. First, you should be running basic integrity checks using the DBCC CHECKDB command for all your production databases. You should also regularly check your indexes for fragmentation as well as regularly updating statistics on your production databases. To help automate these tasks, you should use a SQL Agent job or create a maintenance plan to handle these activities. There are also several scripts and third party tools that can help keep your database operating efficiently. Of course, you need to be sure that you don’t run these high-resource consuming activities during your peak production working hours.
4. Not monitoring server utilization – Regular system monitoring and benchmarking is another vital activity that needs to be performed regularly. It’s a mistake to wait until your system is having problems to start monitoring your database activity levels and workloads. By periodically checking your system’s performance statistics you can get an idea of your normal workload and patterns which can later help you to pinpoint changes and abnormalities. You should periodically check CPU utilization, memory, I/O, locks and blocking as well as how much free space is available in for your data and log files.
Underwritten by HPE and Microsoft