Keeping an Eye on All of Your SQL Servers

by Nov 13, 2020

The majority of organizations that use SQL Server as one of their database solutions have more than one instance in their environment. In some respects, SQL Server databases are like potato chips. You can’t have just one. In part due to the ease of installation and flexibility of the database solution, what was originally planned as a controlled environment can rapidly grow as systems seemingly proliferate overnight.

Adding to the challenge of managing multiple SQL Servers is the fact that there is a very good chance there will be more than one location in which they are implemented. Hybrid environments are the norm and a DBA may be responsible for physical and virtual systems spread throughout an on-premises data center and several cloud providers. It can quickly become difficult for a database team to effectively keep tabs on the availability and performance of their databases. A comprehensive monitoring tool that can keep the entire environment in view would be an excellent solution to this problem.

Monitoring provides the information a database team needs to maintain optimal performance and ensure that users can access the systems at all times. But monitoring is a broad term and many software products can address a diverse database environment. The right tool can make the life of a database team substantially easier while using the wrong solution can be the source of additional confusion and turmoil.

Identifying the Right Monitoring Solution

Some potential issues that can impact the usefulness of a monitoring tool should be addressed before introducing it into your SQL Server environment. 

  • Generic monitoring tools will not be capable of delivering the types of insights required to maintain your SQL Servers. The intricacies of a platform are better addressed by a solution that focuses on it rather than one that attempts to work on the least common denominator type of rationale.

  • Many tools do not adequately monitor query performance. Without the ability to identify problem queries, they will continue to degrade a database’s response time.

  • Generating a flood of false alerts or warnings that cannot be customized to the needs of an enterprise can end up dragging down team productivity while falling short of any promised performance gains.

The problem of alert overload often results in team members ignoring potentially critical warnings as they attempt to control the amount of irrelevant communication they deal with daily. They may become deaf to the alerts that matter.

Taming Alert Overload

A monitoring tool gains most of its utility from its capacity to generate alerts. But without the ability to fine-tune alert generation, a database team is at risk of being overwhelmed by an overload of warnings. Let’s look at a typical SQL Server environment to illustrate how quickly things can get out of control.

The DBAs at Acme Business Consulting handle a SQL Server environment that fluctuates from between 150 and 200 instances in their data center and a cloud provider. The systems are categorized as production, Q&A, development, and test. After implementing a rather inflexible monitoring solution, they were faced with having to make difficult choices that negate the tool’s potential.

The tool required setting thresholds that apply to the complete environment. Very soon, team members were receiving critical alerts regarding test or development systems that did not require immediate action from a business perspective. When a DBA received a message regarding a test or development system, it could be ignored until the next business day whereas issues with production systems needed to be addressed at once. Luckily, the production systems were fairly stable and the majority of alerts were from less critical SQL Server instances.

A quick look at the incoming message was all a DBA needed to ascertain the name of the system in question and determine if action needed to be taken quickly. Unfortunately, the on-call team members still had to look at the communication coming in on their phone at 3 AM whether their response was warranted or not. This led to a lot of fractured sleep and reduced productivity the following day.

As the situation continued, some beleaguered and sleep-deprived members of the database team took to turning off their phones or otherwise ignoring the messages they were sure addressed non-critical systems. Real problems that could have been taken care of before they regulated in outages were missed due to the effects of alert overload. This certainly was not the intended outcome when funds were allocated to purchase the monitoring solution. Perhaps another application would have been a better choice for Acme.

A Dedicated SQL Server Monitoring Platform

IDERA’s SQL Diagnostic Manager for SQL Server offers all the functionality and customization required by a database team charged with maintaining a SQL Server environment. Alerts can be customized to conform to enterprise business needs. Advanced control of notifications enables alerts to be sent to different groups based on various criteria that include the affected SQL Server instance and time-of-day. You can make sure that the right people are notified at the right time so they can take the correct actions to keep critical systems running smoothly.

The tool monitors queries and plans so locks and blocks can be identified and resolved before they impact users. Your entire environment can be monitored from a single customizable dashboard and the app can be accessed from a smartphone, tablet, or web browser. It will allow your database team to keep an eye on all of their SQL Servers wherever they are hosted without the unenviable side-effects of alert overload.