Microsoft Azure SQL Database API poller template

Use this SAM API poller template to monitor Microsoft Azure SQL Server Database performance and statistics counters, including utilization, connection database, and storage statistics.

Links and screenshots herein are attributed to © 2021 Microsoft Corp., available at docs.microsoft.com.

Prerequisites

• Use the following parameters to specify the API endpoint in the request URL:

  • ${SUBSCRIPTION_ID}
  • ${USERGROUP_ID}
  • ${SERVER_ID}
  • ${DATABASE_ID}

  Use the following example to locate values in Azure:

  

• Configure OAuth 2.0 Azure credentials with the following values:

  • Scope: https://management.azure.com/.default
  • Access Token URL: https://login.microsoftonline.com/{TENANTID}/oauth2/v2.0/token
    
    

  Although "(optional)" appears next to the Scope field in the UI, this value is required for API pollers based on this template.
  

• The SQL Server has access control to the Application with at least the Reader role.

  

Notes

• Default thresholds are not set for this template.
• You can add the following metrics to this template:
  • cpu_limit
  • cpu_used
  • sqlserver_process_core_percent
  • sqlserver_process_memory_percent
  • tempdb_data_size
  • tempdb_log_size
  • tempdb_log_used_percent
  • allocated_data_storage
  • full_backup_size_bytes
  • diff_backup_size_bytes
  • log_backup_size_bytes
• Here is a request example: https://management.azure.com/subscriptions/6a4208fe-5200-417e-9365-99781c6133c3/resourceGroups/Monitoring/providers/Microsoft.Sql/servers/jr-server/databases/JR_DB/providers/microsoft.insights/metrics?interval=PT1H&metricnames=cpu_percent,physical_data_read_percent,log_write_percent,storage,connection_successful,connection_failed,blocked_by_firewall,deadlock,storage_percent,xtp_storage_percent,workers_percent,sessions_percent&aggregation=Maximum,Average,Total&api-version=2019-07-01

Available metrics

Average CPU utilization

The average percent CPU used for the given SQL database during the time period specified as the polling frequency.

Unit: Percent

Average IO utilization

The average percentage of data IO used for the given SQL database during the time period specified as the polling frequency.

Unit: Percent

Average log utilization

The average percentage of log IO used for the given SQL database during the time period specified as the polling frequency.

Unit: Percent

Total database size

This metric provides the average for the total database size for the given SQL database during the time period specified as the polling frequency.

Unit: Bytes

Successful Connections

The average number of successful connections established for the given SQL database during the time period specified as the polling frequency.

Unit: Count

Failed Connections

The average number of failed connections established for the given SQL database during the time period specified as the polling frequency.

Unit: Count

Blocked Connections

The average number of firewall blocked connections established for the given SQL database during the time period specified as the polling frequency.

Unit: Count

Deadlocks

The average number of deadlocks established for the given SQL database during the time period specified as the polling frequency.

Unit: Count

Database size percentage

The average percentage of storage used for the given SQL database during the time period specified as the polling frequency.

Unit: Percent

In-Memory OLTP storage percent

The average In-Memory OLTP (Online Transaction Processing) storage percent for the given SQL database during the time period specified as the polling frequency.

Unit: Percent

Workers percentage

The average percentage of available workers used for the given SQL database during the time period specified as the polling frequency.

Unit: Percent

Sessions percentage

The average percentage of available sessions used for the given SQL database during the time period specified as the polling frequency.

Unit: Percent