Tips to Monitor Azure SQL Databases

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 up time by proactively notifying you of critical issues so that you can resolve them before they become problems.

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.

Monitoring Azure with Azure's tooling is difficult

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.

Monitoring Azure with Microsoft's built in tooling is difficult because:

1. The data is all over the place
2. Setting up monitoring and alerting is a manual job
3. Maintenance is time consuming
4. Relies mostly on traditional reactive monitoring
5. Does not efficiently solve silo challenges across cloud and on premise systems

As a consequence monitoring with the built in Microsoft tools tends to fall into the same traps as other antiquated tools that sets your organization up for failure.

You can read more about monitoring of Azure with Microsoft's tools here.

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.

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.

Learn more about how to use AIMS to monitor SQL databases or sign up for the free Community Edition and test it out for yourself.