Lesson Learned #450: Automating Alert Notifications for Query Data Store Usage in Azure SQL Database

[Jose_Manuel_Jurado]

In this article, we will introduce a PowerShell script that helps monitor the usage of Query Data Store (QDS) in SQL Server databases. The script automatically sends an email alert when the storage space used by the Query Data Store reaches or exceeds 80% of its maximum allocated space. This can be particularly useful for database administrators to proactively manage and optimize their database storage.



Before we dive into the details, it's essential to note that the script provided is a recommendation and should be adapted according to your specific environment and requirements. The responsibility of ensuring its compatibility and the impact on your system lies solely with the user.



The PowerShell Script: The provided PowerShell script does the following:

1. Connects to the SQL Server instance and retrieves a list of all online databases.
2. For each database, it establishes a connection and queries the Query Data Store options to get the current storage size and maximum allowed storage size.
3. Calculates the percentage of used storage space.
4. If the used storage space is greater than or equal to 80%, it sends an email alert to the specified recipient.

You can find the complete script and instructions on how to use it in the previous messages of this chat.



Adapting the Script to Your Environment:



Here are some important points to consider when adapting the script to your environment:

1. Update the SQL Server connection details, including the server name, user ID, and password.
2. Configure the email parameters, such as the sender's and recipient's email addresses, SMTP server, username, and password.
3. Test the script in a non-production environment before deploying it to production.

Conclusion:



This PowerShell script is a handy tool for database administrators to monitor the usage of Query Data Store in SQL Server and receive automated alerts when the storage space usage exceeds a specified threshold. However, as with any script or tool, it's crucial to carefully review, test, and adapt it to your environment before using it in a production setting. By doing so, you ensure that the script works as expected and mitigates any potential risks to your system.




# Configure the SQLClient connection details
$serverName = "servername.database.windows.net"

# Create the SQLClient connection string for the server
$connectionString = "Server=tcp:$serverName,1433;Database=master;User ID=user;Password=pwd;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

# Create a SQLClient connection for the server
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)

$emailFrom = "EmailFrom@XXX.com"
$emailTo = "EmailTo@NNN.com"
$smtpServer = "smtpserver.com"
$smtpUsername = "NNNN@XXXX.com"
$smtpPassword = "pwd"
$smtpPort=25

# Open the connection
$connection.Open()

# Execute a query to retrieve the list of databases from the server
$query = "SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND Database_id >= 5;" # Modify the condition as per your requirements
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
$reader = $command.ExecuteReader()

# Store the database names in an array
$databaseNames = @()
while ($reader.Read()) {
$databaseNames += $reader["name"]
}

# Close the reader and the connection
$reader.Close()
$connection.Close()

# Initialize the results array
$results = @()

foreach ($databaseName in $databaseNames) {
# Create the SQLClient connection string for the current database
$databaseConnectionString = "Server=tcp:$serverName,1433;Database=$databaseName;User ID=username;Password=pwd;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

# Create a SQLClient connection for the current database
$databaseConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection($databaseConnectionString)

# Open the connection
$databaseConnection.Open()

# Execute a query to retrieve the Query Data Store state for the current database
$query = "SELECT DB_NAME() AS DatabaseName, current_storage_size_mb as XCurrentX, max_storage_size_mb as XMaxX FROM sys.database_query_store_options;"
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $databaseConnection)
$databaseReader = $command.ExecuteReader()

while ($databaseReader.Read()) {
$databaseName = $databaseReader["DatabaseName"]
$currentStorageSize = $databaseReader["XcurrentX"]
$maxStorageSize = $databaseReader["XmaxX"]
$percentUsed = ($currentStorageSize / $maxStorageSize) * 100
$timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"

$result = @{
ServerName = $serverName
DatabaseName = $databaseName
CurrentStorageSize = $currentStorageSize
MaxStorageSize = $maxStorageSize
PercentUsed = $percentUsed
Timestamp = $timestamp
}
$results += $result
}

# Close the reader and the connection for the current database
$databaseReader.Close()
$databaseConnection.Close()
}

# Check if the usage has reached 80% and send an email in that case
foreach ($result in $results) {
$percentUsed = $result.PercentUsed
if ($percentUsed -ge 80) {
$serverName = $result.ServerName
$databaseName = $result.DatabaseName
$timestamp = $result.Timestamp
$subject = "Alert: Query Data Store usage has reached 80% in database $databaseName"
$body = "Query Data Store usage has reached $percentUsed% in database $databaseName on server $serverName at $timestamp."
$smtp = New-Object Net.Mail.SmtpClient($smtpServer, $smtpPort)
$smtp.EnableSsl = $true
$smtp.Credentials = New-Object System.Net.NetworkCredential($smtpUsername, $smtpPassword)
$mailMessage = New-Object Net.Mail.MailMessage($emailFrom, $emailTo)
$mailMessage.Subject = $subject
$mailMessage.Body = $body
$smtp.Send($mailMessage)
}
}



Articles related:



Lesson Learned #387:Monitoring Query Data Store State Changes in Azure SQL Database using PowerShell - Microsoft Community Hub

Lesson Learned #427:BUFFER IO in Azure SQL Database (Query Data Store): An Expert's Guide - Microsoft Community Hub

Lesson Learned #386: Can I log when Query Data Store (QDS) fails to capture a query? - Microsoft Community Hub

Continue reading...