SQL Server monitoring

How to optimize the performance and ensure the availability of your databases and servers

Why SQL Server monitoring is important

With SQL Server estates growing faster than ever, deployment rates increasing, and constant demand for fast fixes and high availability, effective SQL Server monitoring it the only way to keep track of what’s going on across all of your databases, instances, and servers.

Whether you build it yourself or buy a third-party solution, a SQL Server monitoring tool should give you a single pane of glass from which to see all database instances on your estate.

At a glance, you should see any critical issues on your servers which need addressing, with the ability to quickly drill-down into the detail and analyze performance metrics.

100+ servers - fastest growing SQL Server estate size 3+ average deployments per week 575 minutes mean time to detection without SQL Server monitoring

3 common SQL Server monitoring mistakes

1. Manual monitoring

With the right scripts and enough time it may be tempting to avoid the overheads of a SQL Server monitoring tool and try to keep on top of your estates manually. While this may be possible for managing one or two servers, it soon becomes too big a task when you have multiple instances to stay on top of. You also will lack historical data which can give you trends and baselines, essential for identifying when and why performance has been impacted.

2. Too much information

Some SQL Server monitoring solutions provide you with every piece of information your databases, servers, and machines spit out. This means you will spend hours sifting through irrelevant details to find the data you need to do your job effectively. Look for a SQL Server monitoring tool that gives you just the right information, when you need it.

3. Not enough information

Some monitoring tools, particularly those not designed with the database in mind, fail to give the right level of detail you need to properly diagnose issues or optimize performance. They may tell you something is wrong, but not what is wrong or indeed how to fix it. Look for a tool that captures the detail you need to maintain availability and improve performance.

4 benefits of automated SQL Server monitoring

1. Maintain availability

Rather than checking availability of each server, get alerted to issues or see any problems from your estate overview dashboard. By customizing metrics and alerts to trigger in anticipation of an event, such as a disk reaching capacity, you can mitigate against outages and fix problems before they impact your users.

2. Proactively improve performance

Baselining and trend analysis will mean that the time saved not manually monitoring or firefighting can be spent optimizing performance. From long-running queries through to missed back-ups, you can use a SQL Server monitoring tool to refine the performance of your estate.

3. Enable DevOps through better alignment between Development and Operational teams

By giving visibility of your monitoring data to Development teams as well as your DBAs, you can encourage ownership, openness, and alignment. Choose a SQL Server monitoring solution that can integrate with popular deployment and ticket management tools to take this process a step further to automate responses to things such as bad deployments.

4. Reduce overheads

As well as reducing downtime which can be very expensive to your business, SQL Server monitoring can also help you identify areas where efficiency improvements can actually save the business money. For example, with the right tool you can see where disk usage is low or reducing, and plan to shift the costs to another server that requires more space or cut the cost of that server down. This is of particular benefit for cloud-hosted databases.

SQL Server monitoring: what to focus on

Monitor everything from a single pane of glass

A SQL Server monitoring solution will allow you to keep on top of your instances wherever they are hosted. Monitoring on-premises will require some machine-level statistics alongside database performance, where as cloud-hosted databases will require you to focus on slightly different metrics.

The key, however, is to use a SQL Server monitoring tool that can provide parity in coverage regardless of how the database is hosted, and to show their status through a single pane of glass.

Easy scalability

We know that SQL Server estates are continuing to grow so it’s likely that you will regularly need to add new servers to be monitored.

You will need to easily add new servers, preferably automatically, as required and any increase in estate size should not degrade the performance of your SQL Server monitoring tool.

See the right data at the right time

You will need to know instantly the status of all your servers and databases in one place. From here you will want to drill down into issues so you can take immediate action.

Try not to become overloaded with irrelevant detail. Instead, get hold of the information you need to diagnose and resolve issues. This also means that you should find your daily checks reduce while your SQL Server monitoring solution automates a lot of these manual tasks.

Estate-wide monitoring

As well as the individual server and database metrics, it is helpful to get an overview on how your estate is performing as a whole.

Be sure to analyze data regarding installed versions, backups, disk space, SQL Agent Jobs, and licensing, across every server and database on your monitored estate. This will not only help daily management, but will give you a head start in proactively improving performance.

SQL Server monitoring from anywhere

You are also going to want a solution that is available to use everywhere – that is, it needs to be web-based. To access your monitoring data when you need it, wherever you are, the SQL Server monitoring tool you use needs to be lightweight, fast, and accessible via a standard internet browser.

Monitoring for security and compliance

All current security and compliance legislations (GDPR, HIPAA, SOX etc) require organizations to have a system in place to continuously monitor and analyze database activity, in real-time. This system must detect anyone, inside or outside the organization, accessing or tampering with information in any database-driven applications that handle personal, financial, HR, or other business data.

A SQL Server monitoring system can collect metrics such as syntax errors, denials of access, permission changes, and unusual access patterns, and then aggregate and report on them. Immediate notifications of unusual patterns can mean database owners can move quickly to act on breaches, and historical data collection can ensure a complete record is made available should it be required.

Learn more about SQL Server security monitoring on the Redgate Hub.

The right tool to monitor your SQL Server estate

Redgate Monitor

Get an estate-wide overview with fast deep-dive analysis with the world’s most popular tool for SQL Server monitoring.

Download Redgate Monitor

How a SQL Server monitoring tool fits into your tech stack

When adding a monitoring solution to your tech stack, it is important to try to reconcile the different requirements of your tech teams, to be clear about what you’re trying to solve with it, and who will be the primary user.

Your SQL Server monitoring solution should integrate with some tools, such as deployment and ticketing systems, and work alongside others such as infrastructure and application monitoring.

Learn more about how SQL Server monitoring fits into your tech stack.

Choosing the right SQL Server monitoring tool

When going beyond manual monitoring, the decision to use a tool tends to come down to a binary choice: do you build or do you buy?

Build your own SQL Server monitoring tool

With a wealth of T-SQL monitoring scripts available and the likelihood of having some talent in house that could build a basic UI, it is tempting to try to “save money” and build your own SQL Server monitoring tool.

This can be a false economy, however. First you need to consider the basic elements that make up a SQL Server monitoring solution:

  • Collecting and processing performance data.
  • Analyzing the data based on specific metrics.
  • Raising alerts at the right time to the right people.
  • Monitoring SQL Server databases across a variety of platforms on premises and in the cloud.
  • Mapping performance to historic data to assessing variations to the baseline.
  • Remaining current with SQL Server patching and version updates.
  • Maintaining a small footprint while providing high performance.
  • Remaining secure and compliant.

The cost in terms of time not only in building a tool that can do all of the above will quickly exceed the cost of buying a third-party solution. That is before you consider maintenance of your tool, including training and retaining employees to specialize in it.

Significant time and resources can go into creating something that could have easily been bought off the shelf. For comparison, Redgate has three full-time in-house development teams dedicated to Redgate Monitor, not to mention the product managers, product support engineers, and account managers who provide the ongoing industry-leading support our customers have come to expect.

If your company can afford to maintain that level of investment in your own SQL Server monitoring tool, then build may be an option for you. Otherwise, you should look at buying a SQL Server monitoring tool.

Buying a SQL Server monitoring tool

When buying a SQL Server monitoring solution, you will need to ensure you have a set of priorities you need to get from the tool to help you narrow down a shortlist of vendors.

Ask yourself questions specific to your organization, such as:

  • Where are my databases hosted currently and are there any plans to migrate them elsewhere in the future?
  • What metrics do compliance regulations in my industry and/or region require, and does this tool provide them?
  • Who in my organization needs access to monitoring data?
  • What tools in our current tech stack do we have that will need to integrate with the SQL Server monitoring tool?

When you have established the priorities for your solution, you can begin to search for the right vendor.

Choosing a vendor

There are a number of SQL Server monitoring solution providers offering tools of varying capabilities, from the basic overview through to the intricate deep-dive analysis. As a minimum, you will want a tool that performs the tasks we highlighted in the “build” section earlier:

  • Collecting and processing performance data.
  • Analyzing the data based on specific metrics.
  • Raising alerts at the right time to the right people.
  • Monitoring SQL Server databases across a variety of platforms on premises and in the cloud.
  • Mapping performance to historic data to assessing variations to the baseline.
  • Remaining current with SQL Server patching and version updates.
  • Maintaining a small footprint while providing high performance.
  • Remaining secure and compliant.

You should also ensure you look for tools built especially for SQL Server monitoring, rather than general database monitoring, as these tend to be jacks of all trades, masters of none.

All specialist SQL Server tools will all claim to do the above well, and that may be true, however the best way to ensure they meet your requirements is to download the free trial and evaluate them.

Evaluating SQL Server monitoring tools

During your evaluation, you will want to robustly test all the main functions of the tool. Specifically look out for:

  • Does it capture all errors at the right threshold?
  • How efficiently are alerts delivered?
  • How accurate is the information provided?
  • What level of resources are being used by the monitoring tool? (Test this with the maximum number of servers and database sizes you wish to monitor).
  • How does the monitoring tool perform, and is performance affected by increased workloads?
  • How quickly can you drill down from estate overviews to specific analytics?
  • How easy is it to customize alerts, metrics, and reports?
  • How easily does the tool integrate with your existing systems?
  • Test out the learning content provided.
  • Raise a support ticket and assess the response you get.

Benefits of a SQL Server monitoring tool

90% of organizations with 50+ instances use a SQL Server monitoring tool
28% faster mean time to detection with SQL Server monitoring
68% are satisfied with their SQL Server monitoring tool

SQL Server monitoring is easy with Redgate Monitor

Redgate Monitor can manage your entire SQL Server estate, with instant problem diagnosis, intelligent and customizable alerting, and a single pane of glass that keeps pace with your growth.

Whether your databases are hosted on premises, in the cloud or a mixture of both, you can be sure you can always have the answers to the health of your estate, and proactively find potential problems before they impact your users.