Azure SQL Server Monitoring/Alerting for Multiple Databases (v12)

Question

What is the best way to monitor a certain metric and alert on it over multiple databases. For example I want to trigger an alert if a database size grows larger than 10 GB.

I know you can select a single database and configure this in Azure Monitor but when there are hundreds this doesn't seem viable.

My Databases are created with Azure Resource Management

You'll probably need to invest into 3rd party monitoring products like CloudMonix @ cloudmonix.com for things like templatable metrics and alerts, sophisticated reporting, etc. — Igorek

pim pim · Accepted Answer · 2018-05-31T10:55:50

I think PowerShell, specifically the Get-AzureSqlDatabaseUsages command, is your best friend here. I've done this in the past as a modified "heartbeat". Whereby we also analyzed index fragmentation, and triggered a REBUILD if needed.

An example of the PowerShell command from above:

C:\> Get-AzureSqlDatabaseUsages -ServerName "Server01" -DatabaseName "Database01"

This will require that you be authenticated, but that itself is simple enough as well.

You could execute the script on a schedule (i.e. either as a WebJob or a Scheduled Task) and report/notify based on certain metric thresholds.

EDIT: For v12 databases use the following query (which can be execute programmatically using PowerShell and sqlcmd.exe)

SELECT SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) AS DatabaseSizeInBytes,
       SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 AS DatabaseSizeInMB,
       SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 / 1024 AS DatabaseSizeInGB
FROM sys.database_files
WHERE type_desc = 'ROWS';

Azure SQL Server Monitoring/Alerting for Multiple Databases (v12)

2 Answers