SQL Server Monitoring On-premises and Cloud for Management and DBAs
By: Eric Blinn | Updated: 2023-01-24 | Comments (2) | Related: More > Monitoring
Problem
The use of SQL Server in the cloud continues to grow, but there is still the need to manage on-premises installations. This creates the need to have tools that can manage and monitor SQL Server database performance both in the cloud and on-premises. The challenge is to eliminate the need to introduce more tools and their associated costs -- both in direct financial terms and less-direct training costs.
Solution
There are various reasons why a unified toolset is beneficial for database monitoring. From a management perspective, this does not limit the deployment location of SQL Server, therefore providing management with many choices for each database application. This flexibility leads to both cost and time savings. From a database administrator's perspective, the ability to not have to continuously learn new monitoring software allows the DBA to focus on monitoring and improving the database issues instead of spending precious time learning how to identify and capture the problem.
Why choose the unified database monitoring tools from Idera
Most critical applications have a database back end. That means it is critical to keep that database server running smoothly, quickly, and without outages. Outages can cost real money. A serious application performance degradation can be almost as bad as an outage to your end- users.
Enter the Database Administrator or DBA for short. It is the job of the DBA to manage database servers in a way that gets the highest possible real-time performance and uptime. A good SQL Server DBA is hard to find and even harder to keep -- not to mention afford.
As an IT manager, you need to get the most out of your DBA for value and retention purposes.
Once you have a DBA, you can give your DBA no special tools, but that's a recipe for an inefficient and disgruntled DBA. You can give them multiple tools, one that covers the operating system and another that covers the SQL database. Those tools may not expand into the cloud, meaning yet another tool will be required if the company moves any IT infrastructure or database instances to the cloud. Will those tools easily expand to cover new servers as the company grows? Many tools that do performance monitoring on the operating system don't track SQL Server-specific performance metrics (CPU, memory, disk bottlenecks, indexes, locking, blocking, etc.) that the DBA will want to track for root-cause analysis. All that while paying twice the licensing costs. Why would you want that?
Idera SQL Diagnostic Manager is the tool that puts all of the information that is most important to the DBA right at their fingertips.
This unified tool monitors the operating system and the SQL Server at the same time. There will be no need for the DBA to have two screens up trying to see what the CPU utilization is from the operating system monitor while looking at long-running queries on another -- all the while hoping that the times on both screens match exactly.
Since Idera SQL Diagnostic Manager was written with SQL Servers in mind, the operating system monitoring that it does is geared toward database performance monitoring. It will capture and monitor those specialized performance counters that come with SQL Server and that are important to solving performance issues.
The SQL Server performance monitoring features of Idera SQL Diagnostic Manager constantly scan the database for performance-altering issues such as long-running SQL queries, blocking, or high resource utilization impacting the user experience. It can then alert the DBA of potential problems to begin troubleshooting -- hopefully before the users even notice! If the issues happen after hours, the DBA will be able to come in afterward and review the historical performance problems. This allows them to take action to keep the issue from happening again.
The tool also looks for database problems related to uptime and reliability. This includes High Availability (HA) and Disaster Recovery (DR) solution monitoring and backup verification.
When your SQL Server estate grows, Idera SQL Diagnostic Manager will grow with it by supporting up to 2000 instances. Adding new servers to the Idera dashboard can be completed in just a few clicks. Even if your estate expands into the cloud, Idera is ready to assist in having the ability to connect to Azure and AWS database environments.
If your goal is to have highly reliable and well-performing applications managed by happy and efficient DBAs that are able to:
- Be proactive in monitoring your SQL Servers
- React quickly when trouble arises
- Keep downtime to a minimum
Then the functionality from the unified toolset of Idera SQL Diagnostic Manager is right for you.
Operational Advantages
Watch Many Servers Easily
As a DBA using Idera SQL Diagnostic Manager; you'll be able to find and fix SQL Server problems from your entire estate all from a single interface. Imagine being able to organize your inventory so that you are only looking at SQL Servers related to a single project, location, or status. With Idera SQL Diagnostic Manager, this level of database management is a reality.
This server monitoring works by applying tags to servers based on whatever criteria you like. Then, you just make a view to show only the servers that have that tag.
There are built-in views to show servers that are in a particular status. This view of servers will seamlessly integrate servers that are on-premises, in a colocation facility, or in a popular cloud provider such as Microsoft's Azure or Amazon's AWS.
Adding a new server to that list is as easy as a right-click and clicking "Add". If you need help identifying when a new instance of SQL Server comes online, Idera has tools for that too!
Proactive Database Performance Alerting
The Idera tool will be watching every one of those SQL Servers 24 hours per day, 7 days per week, and 365 days per year. The tool will compare performance counters to predefined and customizable warning and critical levels. There are many counters available, so it may seem daunting to have to set them up for every instance. Luckily, the warning and critical alerts are set up with defaults based on a template to ease the step of registering a new server.
If Idera detects a metric that is out of line, it can email you to warn you. That email could also be pointed at a ticketing system to create an action item. This kind of proactive monitoring can be a lifesaver. You'll know something is acting up before the users even notice and think to notify you. Getting a head start on working on a performance issue can be the difference between a blip on the radar and a major incident or outage.
Proactive Operational Alerting
The list of metrics isn't only related to performance issues. Idera SQL Diagnostic Manager will warn about operational issues as well.
This could include if backups haven't been taken recently. It will send notifications if a logical volume is getting full. If you are running an asynchronous Availability Group or Log Shipping, it can watch the logs and warn if the delay between nodes is longer than acceptable.
Slow Query Capture
One of the main reasons SQL Server slows down is an inefficient database query or perhaps several inefficient database queries. These slow queries can require huge memory grants that hurt memory metrics which impact overall response times.
They can perform table scans that block other users that want to utilize the tables. SQL Diagnostic Manager captures long-running queries allowing you to review them and optimize the code as improvements for your Microsoft SQL Server database.
If you get an alert for blocking or a call from users that things are running slowly, you can review this list and see if any queries are hogging resources. Then you can work to tune that query so it doesn't bother you again. If you need help in that department, Idera can help there too.
Historical Data
Have you ever come to work on a Monday or after an off day to have someone tell you that the system was slow yesterday? Then they ask what you can do about it. Without a cache of historical database metrics and query information, this task is impossible.
Luckily, Idera records this resource consumption in its own database and makes it available via snapshots. The default is to keep 10 snapshots per hour or one every 6 minutes. This can be raised or lowered as needed. Now you can go right to a snapshot of the server at 2:36 pm yesterday and get a complete picture of the performance data. You'll have OS-related metrics like CPU utilization and disk queuing, and right next to them will be a list of SQL Server sessions open at that time.
Another tab has a list of queries that were running. You'll know all about locking or blocking that may have been occurring. If any metric had reached a warning or critical threshold, you'll know that too.
There will be no need to check the OS stats from a perfmon or an OS-only monitoring tool (if you even have an infrastructure monitoring tool) and compare that to the results of a long-running-query trace or extended event session (again, if you even have one). Now you can make an effort to diagnose why query performance was slow in the past.
You can even compare yesterday at 2:36 pm to the day before that or a week ago at 2:36 pm to see if there is a pattern.
Final Thoughts
Idera SQL Diagnostic Manager is an all-in-one solution for monitoring SQL Servers no matter where they sit. It saves money by doing the job of 2 applications in one. It saves time by making DBAs more efficient.
DBAs love it because it helps to immediately pinpoint performance issues, whether they are caused at the SQL Server instance or at the OS level. It does this both live and via historical snapshots. The same tool helps manage operational issues by monitoring HA/DR solutions, backups, and maintenance tasks.
Next Steps
- To learn more about functionality, pricing and to get a free trial, check out the links below:
MSSQLTips.com Product Spotlight sponsored by Idera, makers of SQL Diagnostic Manager.
About the author
Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.
View all my tips
Article Last Updated: 2023-01-24