Tips to Monitor Azure SQL Databases

May 31 2019

As Microsoft says, “Monitoring is the act of collecting and analyzing data to determine the performance, health, and availability of your business application and the resources it depends on”.

An effective monitoring strategy will help you understand the detailed operation of the different components of your application and to increase your uptime by proactively notifying you of critical issues so that you can resolve them before they become problems.

Monitoring in Azure is primarily provided by Azure Monitor which provides common stores for storing monitoring data, multiple data sources for collecting data from the different tiers supporting your application and features for analyzing and responding to collected data.

As you can see in the image below, Azure monitor uses metrics and logs from applications, Operating Systems, Azure Resource, Azure Subscription, Azure Tenant and custom resource. These Metrics and log data can be looked at from different places all over Azure, like Application Insights, Metrics Explorer, Log Analytics etc.

 

Azure Monitor overview

 

 

Monitoring using Azure is difficult because the data is all over the place. There is no single point of management, and you will have to know exactly where to find metrics, dashboards, views, logs etc.

In Azure, you can use Metrics explorer to analyze collected metrics and plot them on a chart. This way you can track resource performance (such as a VM, website, or logic app) by pinning charts to an Azure dashboard.

When you need to be alarmed on deviations you must configure a static metric alert rule that sends a notification when the metric crosses a threshold.

You must manually route metrics to Log Analytics to analyze metric data together with log data and to store metric values for longer than 93 days.

You can also stream metrics to an Event Hub to route them to Azure Stream Analytics or to external systems.

 

Overzicht van metrische gegevens

 

Log Analytics uses activity logs from Azure resources that include information on their configuration and health and Diagnostic logs that provide insights into their operation.

Application data is collected by Application Insights and can be used to provide insights into a particular application or service such as Container Insights, VM Insights, or Resource Group Insights.

You can use the Log Analytics page in the Azure portal to write queries analyzing log data.

Log alert rules have to be configured manually, before sending a notification when the results of the query match a particular result.

 

Onderdelen van Log Analytics

 

Billing metrics are also collected in Azure, but since every service is charged in a different way it is hard to make sense of some of the data.

The billing metrics are exposed at service level.

About Azure SQL DB

Azure SQL Database is a general-purpose relational database-as-a-service (DBaaS) based on the latest stable version of Microsoft SQL Server Database Engine. SQL Database is a high-performance, reliable, and secure cloud database that you can use to build data-driven applications and websites in the programming language of your choice, without needing to manage infrastructure.

Azure SQL Database provides the following deployment options for an Azure SQL database:

  • As a single database with its own set of resources managed via a SQL Database server. A single database is similar to a contained databases in SQL Server.
  • An elastic pool, which is a collection of databases with a shared set of resources managed via a SQL Database server. Single databases can be moved into and out of an elastic pool.
  • Managed instance, which is a collection of system and user databases with a shared set of resources. A managed instance is similar to an instance of the Microsoft SQL Server database engine.

Why Monitor Azure SQL DB?

As explained earlier in this article, Azure SQL DB is a relational database containing all your important data. Losing or corrupting this data would be a disaster.

Monitoring is also important because SQL Server provides a service in a dynamic environment. The data in the application changes. The type of access that users require changes. The way that users connect changes. The types of applications accessing SQL Server may even change, but SQL Server automatically manages system-level resources, such as memory and disk space, to minimize the need for extensive system-level manual tuning. Monitoring lets administrators identify performance trends to determine if changes are necessary.

Why you should consider using AIMS to monitor Azure SQL DB?

AIMS is built on the fundamental value proposition of automating monitoring and insight into increasingly complex enterprise IT systems with a focus on the applications or business processes supported. With Azure, the number of applications – or services – explodes taking the growth in complexity from linear to exponential.

Each of the services or applications requires monitoring of relevant performance metrics. Taking the number of services, systems or applications from tens to thousands with cloud means that you should expand the metrics you monitor by a similar factor – that is probably about 100-fold. That cannot be done by adding manual monitoring or human resources.

Normal behavior

This needs to be done by using machine learning and algorithms. AIMS’ monitoring for Azure fetches all relevant performance metric for each IaaS or PaaS in Azure. For each service we fetch all available metrics in real time and AIMS build normal behavior patterns for each metric. This normal behavior metric represents the cyclicality of your business by different time resolutions – minutes a day, hour per day or day per week. This means that AIMS builds a digital DNA of how your business lives – represented through these normal behavior patterns.

 

Screenshot 2019-04-29 at 12.04.10

 

Metrics

Now, with this digital DNA that consist of thousands of normal behavior patterns AIMS will monitor in real time the current performance vs the patterns build while also dynamically updating the normal behavior patterns with the new performance data fetched. The effect is that all available metrics are monitoring in real time vs the normal behavior patterns.

For Azure SQL DB the relevant metrics are:

  • DTU/CPU percentage,
  • DTU/CPU limit,
  • Timeouts,
  • QueryStoreRuntimeStatistics,
  • QueryStoreWaitStatistics,
  • DatabaseWaitStatistics,
  • Blocks,
  • Deadlocks,
  • Errors,
  • Database waits,
  • Query duration,
  • Query waits,
  • Physical data read percentage,
  • Log write percentage,
  • Successful/Failed/Blocked by firewall connections,
  • Sessions percentage,
  • Workers percentage,
  • Storage,
  • Storage percentage, and
  • XTP storage percentage.
  • Page IO latch waits,
  • Execution count,
  • CPU utilization,
  • Resource waits, and
  • Total wait time.
  • DTU percentage,
  • Database size percentage,
  • CPU percentage, and
  • Total database size.

Anomalies

Using these normal behavior patterns, AIMS identifies anomalies on each single metric vs normal behavior and correlates the anomalies across the rest of the metrics to create Anomaly Warnings.

Anomaly Warnings provide early notification of trouble with Azure Services or across cloud / hybrid environments.

Auto-detection

You can also use the “Activity & changes” module in the Analytics tab to create reports & dashboards to identify new resources & applications in Azure, which allows early identification of new resources and applications in Azure to ease insight and transparency.

 

 

AIMS will do all this without you needing to manually intervene, and there are no static thresholds needed!

AIMS Azure SQL DB reports & dashboard

All the data collected by AIMS is available to users in the Analytics section that allows you to access default reports or to create custom reports and dashboards. When creating your first report, you will discover that the AIMS Analytics consists of a wide range of possibilities. You can create public or private reports, set a report as a dashboard and define which users will receive dashboard reports.

AIMS stores performance data, making it a valuable data source for later use – such as for root-cause analysis.

To create a report or dashboard focusing on key selected performance metrics for Azure SQL DB could do as follows:

For SQL Performance monitoring:

 

For database utilization:

 

 

So, summing up: with AIMS you can automate monitoring of your Azure IaaS and PaaS through an easy configuration that can be done in minutes. This will allow you to free up valuable time spent monitoring using other tools or manual monitoring. The consequence is that you will have better monitoring and more time to deliver projects for your business.

 

 

Tags: Blog

Integration monitoring tool checklist

How does your Azure, BizTalk or SQL monitoring stack up?