Being a database administrator is much more than knowing how to install SQL Server and set up a database. One of the most important responsibilities is being proactive by monitoring the instances in their care. But, what should be monitored? Here are the top five things to monitor when you are a SQL Server DBA:
#5 Job outcomes: Great DBAs automate everything they can and use SQL Server Agent or some other job scheduler to run the scripts. They understand what the jobs do and the consequences of a job failure or long running job. They also have every job documented so that they can take a day off once in a while!
#4 File growth: Over time, database files can run out of free space as can the volumes where these files live. Transactions must wait while database files grow. Applications can grind to a halt if there is no more space in the files or space runs out on the volume. Nobody wants that!
#3 Backups: There are exceptions, but just about every database should be backed up on a regular basis, including frequent transaction log backups. Unless you have a job in place to back up EVERY database on an instance by default, it’s easy to miss adding new databases. By the way, make sure you have a process in place to test backup files as well.
#2 Security: Are you keeping track of how many accounts are in the sysadmin group? How about failed logins? Keeping a company’s databases secure is the most important task a DBA has, and monitoring can help you spot unusual activity.
#1 Performance: When was the last time someone called you to say that their application was running fast? (I didn’t think so!) Taking baselines, watching resource utilization (CPU, memory, I/O) changes over time, determining the top 10 or so worst performing queries so you can tune them…There is so much to do here, but this is your chance to shine!
Now that you have an idea about what to monitor, how do you do it? The best way is by using a monitoring tool like Redgate’s SQL Monitor. It has best practices built right in, but it’s also highly configurable if you need to deviate a bit from those best practices. The best part is that it’s so easy to use. It can support hybrid environments, large estates, Availability Groups, and more! Take it for a spin on Redgate’s own servers.