Monitoring SQL

These days, no one can afford to be without monitoring. SQL Server has some cool tools built in for monitoring, most notably extended events, sp_Serverdiagnostics (SQL 2012 and up) and DMV’s. Over the years, I've seen companies use more than one tool to monitor SQL Server. Some have 5 plus tools, which I think is way too many for the same information.

Tools

There are also some great third-party tools (Red-Gate, SQLSentry, System Center, Idera, Solarwinds) to monitor on an enterprise level.

My Gripe

Many companies are not using their tools effectively and, this in turn, causes them to respond after the fact instead of being proactive—or worse they ignore the alerts and blame the product, resulting in another product being tried and the same situation occurring again.

The Solution

Regardless of the tool, the following should be done:

The aim of setting monitoring shouldn’t be just for admins but for everyone that has a hand in DBA / Sys Admin / Developer / System owners. You then have monitoring setup for every business process / component for which each is responsible for. To achieve this, you'll need to sit with each team to define their requirements.

Defining the tool requirements in itself is a big task. I'm currently going through this excerise. My goal is to give the various IT teams within the organization a view of the system they are responsible for. Hopefully, by them having a view of their system they'll be able to fix issues or potential issues before they occur, thus leading to better performance and control of the environment.

The Goal

My goal is always to give the customer a functional and useful system once we have deployed the solution that provides users with information that requires action, ensuring an improvement in performance and reliability of their systems.  

As a SQL Server pro, how do you monitor your environment? What do you look for in a tool?  Personally, I like system center.

Let me know your thoughts and suggestions on how you go about implementing a monitoring solution and which tools you use.