I was recently asked about which tools I used back when I was a full-time SQL Server database administrator. I had a tool to compress backups and a monitoring tool with licenses for just a fraction of the servers I was responsible for. Over time, I figured out ways to gather information or automate processes by scripting, either with T-SQL or VBScript, which was popular at the time. As a consultant, I abandoned VBScript and started using PowerShell. I spent a lot of time, especially as a DBA, creating tools to try to be proactive, but I could have been much more productive if I had the products I needed in place. I often found myself scrambling to figure out what was going on when a user called about a performance problem and spent a great deal of time coming up with creative ways to keep track of the many servers in my care.

Many shops have just one DBA responsible for dozens of SQL Server instances. Larger companies will often have a team of DBAs to spread the workload and on-call responsibilities. In either case, there is a lot of time spent troubleshooting performance issues, configuring high-availability and disaster recovery solutions, ensuring security, applying updates, and much, much more. As companies move to DevOps methodologies, DBAs must be more involved with Agile teams to ensure smooth deployments. Having the right tools in place ensures that DBAs are more productive, could save money in the long run, and possibly decrease turnover in the DBA team.

Having a monitoring solution in place is probably the most important tool for the DBA. A monitoring solution can provide many benefits. Here are a few ways that a monitoring solution will help a DBA or DBA team:

• Quickly understand the physical characteristics of each SQL Server instance
• View performance information about individual databases
• Receive alerts when something is amiss
• View history to be able to find the root cause of problems after the fact
• Head off small issues before they become big ones
• See trends over time, file growth for example
• Monitor scheduled job success and unusually long run times
• Find poorly performing queries
• Be notified about suspicious activity that could mean a breach
• Have one tool that is compatible with multiple versions and editions of SQL Server
• See the information about dozens or hundreds of servers in one dashboard

Without a solution in place, the DBA may be spending more time writing the scripts to gather information and creating reports than working on the issues. After receiving the data, there is still so much work to be done to improve performance and security and to correct issues such as failing jobs. Having a monitoring tool in place means that the DBA can be focused on work that is important to the company such as project work, improving performance, and DevOps.

Do you have a story to tell us about how your monitoring solution saved the day? Tell us in the comments.