Azure SQL Database Monitoring

Deprecated in Opsview Cloud and Opsview Monitor 6.4
This Host template is now contained within the main Azure Opspack.

Azure SQL Database is a cloud database service from Microsoft. SQL Database provides web-facing database functionality as a utility service where you pay only for what you use. Cloud-based database solutions such as SQL Database provide many benefits such as rapid provisioning, cost-effective scalability, high availability, and reduced management overhead.

What You Can Monitor

Opsview Monitor's Azure SQL Opspack helps you diagnose your performance and cost metrics quickly. Opsview Monitor provides cost metrics to manage scalability costs such as dtu limit, used and dwu consumption percent. SQL Performance metrics include the ability to track connections, CPU percent, firewall blocks and more.

Service Checks

Service Check	Description
cpu_percent	The current amount of CPU resources being consumed.
physical_data_read_percent	Average I/O utilization.
log_write_percent	Average log utilization.
dtu_consumption_percent	Database throughput units utilization.
storage	Total disk capacity of the database.
connection_successful	Number of succesful database connections.
connection_failed	Number of failed database connections.
blocked_by_firewall	Number of connections blocked by firewall.
deadlock	Current count of deadlocks.
storage_percent	Percentage of storage used.
xtp_storage_percent	Percentage of In-Memory OLTP storage used.
workers_percent	Percentage of workers being used.
sessions_percent	Percentage of sessions currently being used.
dtu_limit	Database throughput units utilization limit.
dtu_used	Database throughput units used.
dwu_limit	Data warehouse units utilization limit.
dwu_consumption_percent	Data warehouse units utilization.
dwu_used	Data warehouse units utilization.

Azure SQL Database Monitoring Prerequisites

The monitoring plugin for this Opspack has been tested with Python 2.7. In order for the Opspack to run, you will need to have some Python packages installed by running the pip python package tool.

If a cryptography error occurs when trying to install the Azure packages, you can run the commands which should fix the problem.

Debian and Ubuntu

sudo apt-get install build-essential libssl-dev libffi-dev python-dev python-pip

CentOS and RHEL

sudo yum install gcc libffi-devel python-devel openssl-devel python-pip

Common

When python-pip is installed, you can then run:

sudo pip install --upgrade pip==9.0.2
sudo pip install --upgrade setuptools
sudo pip install --upgrade requests
sudo pip install nagiosplugin
sudo pip install azure
sudo pip install azure-monitor

Azure SQL Database Monitoring Setup

To monitor you Azure environment, you need to configure it for monitoring

This requires Administrator access on Azure. You need to retrieve the following credentials:

Subscription ID
Tenant/Directory ID
Client/Application ID
Secret Key

Step 1: Find Subscription ID

The Subscription ID can be found in the Subscriptions section under the All services section from the Azure dashboard

Step 2 : Find the Tenant/Directory ID

The Tenant/Directory ID can be found in the Azure Active Directory under the Properties section from the Azure dashboard

Step 3: Find the Client/Application ID for your application

You need to create and register your application if you haven't already. Use the following documentation from Microsoft: Create an Azure Active Directory application

The Client/Application ID can be found in Azure Active Directory under the App registrations section from the Azure dashboard

Step 4: Generate the Secret Key for your application

You will need to create a Secret Key for your application, once this has been created its value will be hidden so save the value during creation

To create the Secret Key, select your application from the list, select the Settings within your application and then select the Keys option

There you can create a new key by adding the description and expiration period and the value will be generated

Step 5: Provide access to the subscription you wish to monitor

Navigate to the Subscriptions section and select the Subscription you selected before

In the Subscription to be monitored, click Access Control (IAM)

Then click the Add button, select Monitoring Contributor and select the application

If you are running more than one subscription these steps will need to be done for each one you wish to monitor

Setup and Configuration

To configure and utilize this Opspack, you simply need to add the 'Cloud - Azure - Virtual Machines' Opspack to your Opsview Monitor system

Step 1: Add the Host Template

Ensure the Primary Hostname/IP field value matches the name of your Azure SQL Database (the resource name).

Open the Advanced section at the bottom and change the Host Check Command to Always assumed to be UP.

Step 2: Add and configure variables required for this host

Add 'AZURE_CREDENTIALS' to the host and set the Resource Group as its variable value, then override the Subscription ID, Client ID, Secret Key and Tenant ID to match the values retrieved earlier.

Add the 'AZURE_EXTRA_PROVIDER' variable and set the variable value to be the name of the Azure SQL Server (the resource name) which hosts the SQL database.