A key finding from Redgate’s recent State of Database Monitoring Survey of over 2,500 IT professionals was that 79% of respondents reported using either a third-party or in-house monitoring tool. It’s notable because it’s an increase of 10 percentage points from the same survey last year – and the satisfaction rate with third-party monitoring tools also saw an increase of 18 percentage points to 86%.
But when should you use an in-house tool, and when should you take the plunge and invest in a third-party monitoring tool like SQL Monitor?
At Redgate, we’ve found that manual, in-house monitoring tools are more common in companies with under 100 employees, with fewer than ten servers, when they deploy changes to the database only a few times a year. However, companies often struggle to scale these solutions as the number and size of their databases and servers grows.
For larger estates where there are likely to be different versions and editions of SQL Server, as well as instances in the cloud, it saves time and resources to use a third-party monitoring tool. It removes the heavy lifting of data collection and management, analyzes the data and provides baselines to spot trends over time. It should also offer an easy-to-digest picture of activity, issues and alerts across all of your monitored servers, whether on-premises or in the cloud:
This is SQL Monitor’s global dashboard, which you can take for a test drive online. It shows the real-time performance of the servers that run the #1 education and community site for SQL Server, SQLServerCentral, and Redgate’s technical journal Simple Talk.
5 things to look for in a third-party monitoring tool
At the heart of monitoring any SQL Server estate is the requirement for tailored alerts specific to your particular requirements, baselines to provide a comparison of current performance over past performance, and trends to help with future capacity and infrastructure planning.
A good third-party tool should also provide a customizable at-a-glance overview of an entire SQL Server estate, support hybrid cloud environments, large estates, and Availability Groups; show trends over time; and provide guidance on any performance issues that arise and how to resolve them.
More specifically, when you reach the point where your in-house monitoring capability is leading to resource issues or ongoing problems managing your server estate, there are five factors to consider.
1. What does it monitor and how does it provide alerts?
The first thing you’ll want to know is that it provides the coverage you need. Aim for 95%, because the other 5% will come from processes like backups and restores, SLQ Agent jobs, and reporting failures.
The solution will need to highlight queries having the biggest impact and feature a focused set of performance metrics, along with customizable alerts for the most important operational and performance issues. A nice-to-have is alerts that can be grouped to avoid the alert ‘noise’ scenario that is common when first introducing a monitoring tool.
If you’re deploying changes more frequently, look out too for a solution that marks on the performance timeline when deployments were made and which database they were made to.
2. Does it provide baselines and trending information?
Baselines are important because they provide a quick guide to understanding the significance of events like performance spikes and whether they need attention. Trends are also valuable because they can show, for example, the probable point in the future when new resources will be required, which is essential for effective planning.
3. Does it have a global overview?
As SQL Server estates grow and become more complex, a global overview on one central web-based interface can provide a handy way to check the status of every server in seconds, not hours. It’s also worth checking if it offers a way of grouping servers so that you can, for example, group servers with business-critical or sensitive data.
4. Does it offer distributed monitoring?
Connected to the growth of estates is the changing nature of those estates, with servers in different data centers, or on different networks with distinct security protocols. Any third-party monitoring tool should be able to handle this, and also be able to monitor servers, instances and databases on-premises, on Virtual Machines, Azure, AWS, or Google Cloud at the same time, on the same screen.
5. Does it pose a risk to performance?
A significant problem with legacy and in-house monitoring solutions is the performance overhead. They often execute complex data collection queries, set trace flags to capture ‘verbose output’, request specialist metrics that are complex to interpret, and so on. This can lead to the observer effect, where actions required to collect the monitoring data degrade the performance of the monitored server.
A good third-party monitoring tool should limit the data collection to lightweight, efficient SQL operations, exploiting lightweight frameworks such as Extended Events. The installation should also not require agents on each monitored SQL Server instance. This minimizes their exposed surface area and reduces risk, and all data processing is done on a separate server. Finally, it should be easy to view the actions taken by the tool itself, to capture the monitoring data.
We started with a key finding from the State of Database Monitoring Survey about the high adoption rate of monitoring tools. There are many connected insights from the survey about why satisfaction with estate monitoring is at an all-time high, who else in the organization now wants access to data and why, and what added value DBAs can offer with a competent monitoring tool in place
To explain them in more detail, we’ve produced an insights report based on the findings from the survey, ‘The value of a monitoring tool for the entire organization’. It looks at how using a competent monitoring tool appears to reduce frustration, save time and allow DBAs to focus their time and effort on contributing value to the business elsewhere.
Download the free report and find out more.
Was this article helpful?