An Introduction To Microsoft SQL Server Monitoring

This post is a high-level summary of our white paper on SQL database monitoring. For more depth and detail, you can download the full white paper. Microsoft SQL Server is one of the most widely adopted database platforms in the world. For many years an excellent database engine for Windows-centric data centers, today SQL Server…

This post is a high-level summary of our white paper on SQL database monitoring. For more depth and detail, you can download the full white paper.

Microsoft SQL Server is one of the most widely adopted database platforms in the world. For many years an excellent database engine for Windows-centric data centers, today SQL Server has many more features and capabilities and is used in many more deployment scenarios. It runs on-premises and in the cloud, both as a broadly compatible cloud service and in its traditional form on virtual machines, as well as on Linux servers and containers.

SQL Server is the foundation on which hundreds of thousands of businesses build applications, and every business that relies on SQL Server needs a monitoring solution. In this article, we look at several monitoring solutions for SQL Server, both on-premises and on Microsoft Azure.

System Center Operations Manager

System Center Operations Manager (SCOM) is Microsoft’s universal IT monitoring solution for on-prem and some cloud systems. It is capable of monitoring workloads based on Microsoft software, including Microsoft SQL Server, and the software of third-party providers.

SCOM integrates with applications via Management Packs, each of which adds monitoring capabilities. Microsoft provides management packs for most of their products dedicated to enterprise operation, including ones for the SQL Server product family. This family has been evolving for the last years, and so have the management packs for it.

Now there are management packs for on-prem SQL Server, both on Windows and on Linux, for Azure SQL Database, and for Azure SQL Database managed instance. The latter is currently in preview and will reach its first GA in June 2020. And we can’t forget the management packs for SQL Server Replication, Analysis Services, and Reporting Services with Power BI Report Server.

SCOM is an excellent monitoring solution for medium to large businesses, which has big advantages such as a lot of already-made management packs for almost any enterprise-grade software and hardware as well as the possibility to integrate it with IT service management and incident response systems.

In addition to these advantages, it should be mentioned that each MP has a health model and out-of-the-box alerts that reduce the amount of work and time required to start off the monitoring of the infrastructure.

Azure Monitor

Azure Monitor is Microsoft’s cloud monitoring solution. It provides a wide array of monitoring tools based on telemetry generated by cloud services, including tools for querying, visualizing, and analyzing Azure logs and metrics.

Azure SQL Analytics is a new Azure Monitor solution that allows you to easily monitor all your SQL databases at scale. However, it is still in preview, and does not have out-of-the box alerting so additional work will need to be done to fully configure alerts.

There’s also the SQL Health Check solution, but it is limited to on-prem only and, will also need additional configuration to enable alerting out of the box.

Businesses with on-premises SQL Server instances monitored with SCOM can integrate their SCOM alerts into Azure Monitor with SCOM Alert Management from VIAcode, which automatically creates Azure Monitor alert rules for SCOM alerts.

Our solution helps bridge the gap between monitoring and alerting mentioned above, allowing to be much more responsive and proactive in addressing SQL database problems.

SQL Server Health Check

SQL Server Health Check is a risk assessment tool for SQL Server and Azure Monitor. It generates a series of recommendations based on an analysis of the state and configuration of a business’s SQL Server instances, alongside information about why the issues it highlights are important and guidance about how to remediate them. Data for the analysis is supplied by a monitoring agent installed on monitored instances.

SQL Server Health Check recommendations focus on six broad categories:

Security and compliance
Availability and business continuity
Performance and scalability
Upgrade, migration, and deployment
Operations and monitoring
Change and configuration management

Businesses that don’t use Azure Monitor may want to take a look at BP Check, which is discussed later in this article. It performs similar, although less sophisticated checks on SQL Server instances.

Azure SQL Analytics

Azure SQL Analytics is a relatively new monitoring solution for Azure SQL databases. It is a cloud-only solution that doesn’t support SQL Server hosted on-prem or in virtual machines. But, if your databases run on Azure SQL, it offers a powerful alternative to the other monitoring solutions we are looking at in this article.

Azure SQL Analytics collects and processes metrics and logs, displaying monitoring dashboards with performance data for connected databases. Azure SQL Analytics can also be used with Intelligent Insights to analyze database telemetry with artificial intelligence capable of detecting disruptive incidents.

To learn more about SQL Database monitoring, download our free white paper: SQL Database Monitoring — The Complete Guide or contact an SQL Server and Azure SQL specialist for a free initial consultation.