Azure SQL Database Level (error)Alerting

This article addresses the challenge of effectively managing errors captured within Azure SQL Server databases and tables, along with how teams can be notified about these errors.

Problem Statement

Database engineers and administrators frequently encounter errors in their systems. The task is to capture errors that occur within specific tables and columns. The question is: Are there any alternatives to capture errors within Azure SQL tables? Options like Application Insights, Log Analytics, and Azure Monitor exist, but these capabilities may not provide specific errors at the table or column level. How can alerts be generated when new error entries appear in any table?

As Application Insights can be used only Application is directly consuming Database and then Application has a Sdk/Library installed to captured those errors.

Log Analytics specifically captures wait stats, Avg CPU usage, Deadlocks or only User error (failed connections).

Azure Monitor SQL currently is in Preview and can capture only the below properties However it still lags the capability if something specific has been captured inside DB tables.

• DB wait stats
• Memory clerks
• Database I/O
• Server properties
• Requests
• Schedulers etc.

Current solutions.

1. Manual Queries: One approach is to manually run DMV (Dynamic Management View) queries to retrieve errors from the database or across all tables. However, this manual process can be time-consuming and cumbersome.
2. Azure-Specific Solutions:

• Application Insights: While Application Insights offers comprehensive application performance monitoring, it might not be tailored to tracking errors at the table level within Azure SQL databases.
• Log Analytics: Similar to Application Insights, Log Analytics can capture log data for analysis, but it might lack specificity for table-specific errors.
• Azure Monitor: Azure Monitor provides powerful monitoring capabilities for Azure resources, but its out-of-the-box features might not cover granular error reporting within tables.

Resolution: A Custom PowerShell Solution

Given the limitations of existing Azure solutions, an effective way to address this challenge is by utilizing a custom PowerShell script. This script automates the process of scanning tables at defined intervals, identifying errors, and notifying the database administrators for corrective actions.

The PowerShell script performs the following tasks:

1. Connects to the Azure SQL Server database.
2. Scans tables on a defined interval.
3. Identifies new entries with errors within the tables.
4. Notifies the database administrators about the identified errors.

By running this PowerShell script, administrators can proactively monitor tables for errors and promptly address issues as they arise.

In conclusion, while Azure provides various monitoring and alerting options, addressing table-specific errors within Azure SQL databases can require a customized approach. This article suggests using a PowerShell script to automate the error detection and notification process, enabling administrators to respond swiftly to errors at the table level.

Below mentioned script can be used if you want to scan only 1 specific table which does logging and captures all the activities.

# SQL Server connection parameters

$serverName = "azuresqlservername.database.windows.net"
$databaseName = "AzureSQLDBName"
$tableName = "AZURE_ALERT_LOG"

# SQL Username/Password along with entire Connection string can be added in Azure Key Vault
$cred = "Server=tcp:db.database.windows.net,1433;Initial Catalog=AzureSQLDBName;Persist Security Info=False;User ID=SQLUserName;Password=SQLPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

# SQL query to search for faults in case if it just 1 single table
$query = "SELECT * FROM [dbo].[AZURE_ALERT_LOG] WHERE ALERT_DATE IS NOT NULL AND ALERT_TYPE <> ''"

# Establish a SQL Server connection
$connectionString = "Server=tcp:db.database.windows.net,1433;Initial Catalog=AzureSQLDBName;Persist Security Info=False;User ID=SQLUserName;Password=SQLPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

# Open the SMO Backend connection while using PowerShell
$connection.Open()

# Create a SQL command object
$command = $connection.CreateCommand()
$command.CommandText = $query

 
# Execute the query
$reader = $command.ExecuteReader()
 

# Check if any faults were found as it will do Loop
$faultsFound = @()
while ($reader.Read()) {

    $alertDate = $reader["ALERT_DATE"]
    $alertType = $reader["ALERT_TYPE"]
    $faultsFound += "Alert Date: $alertDate, Alert Type: $alertType"

}


# In case for cross validation you can run below command & it will show any open alerts
$faultsFound

# Close the reader
$reader.Close()

# Close any open SMO connections or else next time when you execute the script it will say Assembly is already loaded.
$connection.Close()
 

this is the actual output from SSMS

checking the table for any existing one’s

This is the actual PowerShell output (query string search).

This confirms our PowerShell Script is capturing error strings as expected.

In case if you have a same column name used in multiple tables then you can customize your script and use below format.

# SQL Server connection parameters

$serverName = "azuresqlservername.database.windows.net"
$databaseName = "AzureSQLDBName"


# SQL query to search for faults
$query = "SELECT * FROM {0} WHERE ALERT_DATE IS NOT NULL AND ALERT_TYPE <> ''"

# Establish a SQL Server connection

$connectionString = "Server=tcp:db.database.windows.net,1433;Initial Catalog=AzureSQLDBName;Persist Security Info=False;User ID=SQLUserName;Password=SQLPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
# connection string can be added to Azure keyvault and call from there

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

# Open the connection
$connection.Open()

# Get a list of all tables in the database
$tablesQuery = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '$databaseName'"
$tablesCommand = $connection.CreateCommand()
$tablesCommand.CommandText = $tablesQuery
$tablesReader = $tablesCommand.ExecuteReader()

# Create an array to store faults found across all tables
$faultsFound = @()

while ($tablesReader.Read()) {
    $tableName = $tablesReader["TABLE_NAME"]
    $tableQuery = $query -f "[dbo].[$tableName]"

    # Create a SQL command object
    $command = $connection.CreateCommand()
    $command.CommandText = $tableQuery

    # Execute the query
    $reader = $command.ExecuteReader()

    while ($reader.Read()) {
        $alertDate = $reader["ALERT_DATE"] # column1
        $alertType = $reader["ALERT_TYPE"] # column2
        $faultsFound += "Table: $tableName, Alert Date: $alertDate, Alert Type: $alertType"
    }

    # Close the reader
    $reader.Close()
}

# Close the connection
$connection.Close()

Finally as its a PowerShell script you can Automate them via

• Azure Automation Account/Runbook
• Azure functions
• Inside Azure VM as scheduled task.
• Even you can add this PowerShell script to your Monitoring Dashboard

To notify DB Admins/ end monitoring teams you can add SMTP script. Email functionality can be replaced by your Org specific preferred email utilities.

if ($faultsFound.Count -gt 0) {

    $smtpServer = "smtp server details"
    $smtpPort = 25
    # $smtpUsername = "<smtp-username>"
    # $smtpPassword = "<smtp-password>"

    $fromEmail = "your.email@example.com"  # Replace with your actual email address
    $toEmail = "firstname.lastname@example.com"
    $subject = "Error(s) Found in SQL Query Results"
    $body = "The following faults were found:`r`n`r`n$faultsFound"
    Send-MailMessage -From $fromEmail -To $toEmail -Subject $subject -Body $body -SmtpServer $smtpServer -Port $smtpPort -UseSsl -Force

}

else {
    Write-Host "No faults found."
}