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.
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.
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