In most of the organizations there are some monitoring tools which will monitor all the servers and sends alerts to the DBAs and other groups when a certain alert condition is met. It is very important to have some alert mechanism because it is not possible to manually monitor all the servers 24×7 and the problems can arise at any time. No matter how proactive a DBA team is, still there are unexpected problems with SQL Server either directly caused by SQL Server or due to issues related to Hardware, Network, Operating System or other applications or tools.
One of the important role of SQL Server DBA is to respond to alerts received and fix them in timely fashion based on the severity. Some very senior DBAs may be involved in making decisions on which monitoring product to be used or if a custom monitoring solution is to be developed. Most often or not most organizations prefer using third party monitoring tools to avoid unnecessary costs of developing and maintaining the in house tools.
There are various products or tools available in the market for monitoring SQL Server for various issues and raise alerts by sending E-Mails which later are converted into tickets for DBAs and other teams to handle and resolve the issues.
Below are some of the Tips or Best processes or Criteria that can be followed to determine which Third party monitoring tool best suits your environment.
precision of alerts – It is important to test and make sure that the monitoring tools detects the problems correctly and categorizes it into correct severity and sends the alerts without any fail. There should be mechanism which keep check for the issue periodically and raise the alerts again if it has not been resolved. Monitoring tool should not send false alerts or it should not keep sending alerts even after the issue is resolved.
Defining Alerts – There may be many predefined alerts provided by most of the monitoring tools, but it may not be useful for all organizations, so there should be options to choose which alerts to be activated and which can be left disabled. Also, there may be special requirement in some organizations to check and raise alerts for issues not defined in the monitoring tools, so the tools should have provision to define our own alerts too.
Types of Alerts – The alerts raised or checks performed should not be just limited to SQL Server, but it should also contains related alerts like Operating System, Network, Disk Space, Server Performance, Memory etc. SQL Server is dependent on the underlying server and OS, so the monitoring tool should also check and raise alerts for these issues too. The alerts should be categorized into different types like critical or warning and the alerts are to be raised accordingly and should be resent on a particular frequency based on the alert type.
Usability – Installing the tool, Configuring and managing it should be easy and clear instruction and documentation should be available. Also, it is important that the tool should allow different alerts to be sent to different groups. For example, OS, Network issues need to be routed to those groups along with SQL team and SQL Server issues to be routed only to the SQL teams.
Scalability – As organizations grow, there may be more number of servers implemented, thus can lead to the requirement of the monitoring tool to support large number of servers and should work seamlessly.
Reporting – There should be provision to have good reporting options where in the DBA and other teams can see the reports including current activity, and historical data. There should be provisions to create custom reports too.
Support – It is important to have a strong support for the product as if there is some issues with the product, then monitoring may be down for a particular server or for many servers leaving the environment exposed and can cause large outages.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.