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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Get an estate-wide overview with fast deep-dive analysis with the world’s most popular tool for SQL Server monitoring.
Download SQL MonitorWhen 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.
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?
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:
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 SQL 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.
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:
When you have established the priorities for your solution, you can begin to search for the right 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:
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.
During your evaluation, you will want to robustly test all the main functions of the tool. Specifically look out for:
Source: State of Database Monitoring
SQL 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.
Compliant Database DevOps
Standardize team-based development
Automate database deployments
Monitor performance and availability
Protect and preserve data