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

This morning, I have been working on a support case where our client was not able to see certain queries when querying the Query Data Store. We have observed that the cause is due to the Query Data Store changing to a read-only mode due to the volume of data and the limitation our client had on the QDS database space. Therefore, I would like to share the following PowerShell script that can be executed at regular intervals to check and retrieve when the state of QDS has changed. Unfortunately, in Azure SQL Database, we cannot use the Extended Event 'qds.query_store_db_diagnostics'.

Query Data Store (QDS) in Azure SQL Database is a powerful feature that allows you to capture and analyze query performance data. It helps you identify performance issues, track query execution plans, and optimize query performance. Monitoring the state of the Query Data Store is crucial to ensure its proper functioning. In this article, we will explore how to monitor and track changes in the Query Data Store state using PowerShell and SQLClient.

Step 1: Configure SQLClient Connection

Before we begin, we need to set up the SQLClient connection string. Update the $serverName variable in the script below with the name of your Azure SQL Database server.

Step 2: Retrieve Database List

Next, we will establish a connection to the master database of the Azure SQL Database server and retrieve the list of online databases. This will ensure that we monitor the Query Data Store state for all active databases. You could customize the list.

Step 3: Retrieve Query Data Store State for Each Database
Now, we will iterate over the list of database names and establish a connection to each database individually. We will execute a query against each database to fetch the current Query Data Store state.

Step 4: Compare Current and Previous States
To track changes in the Query Data Store state, we will compare the current state with the previous state stored in a JSON file. We will read the previous state from the file, compare it with the current state, and log any changes.

Script

# 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=UserName;Password=Password;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

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

# 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()

$FileCurrentStatus = "C:\temp\CurrentStatus.Json"
$ChangesFound = "C:\temp\ChangesFound.Json"

# 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=Password;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, actual_state_desc FROM sys.database_query_store_options;"
    $command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $databaseConnection)
    $databaseReader = $command.ExecuteReader()

    while ($databaseReader.Read()) {
        $stateDesc = $databaseReader["actual_state_desc"]
        $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"

        $result = @{
            ServerName = $serverName
            DatabaseName = $databaseName
            StateDesc = $stateDesc
            Timestamp = $timestamp
        }
        $results += $result
    }

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

# Retrieve previous state from the JSON file
$previousData = @()
if (Test-Path -Path $FileCurrentStatus) {
    $previousData = Get-Content -Raw -Path $FileCurrentStatus | ConvertFrom-Json
}

# Compare current and previous states
foreach ($result in $results) {
    $databaseName = $result.DatabaseName
    $previousState = $previousData | Where-Object { $_.DatabaseName -eq $databaseName } | Select-Object -ExpandProperty StateDesc
    $currentState = $result.StateDesc
    $timestamp = $result.Timestamp

    if ($currentState -ne $previousState) {
        If($previousState -eq $null) {$previousState='NewDatabase'}
        $changeDetails = @{
            ServerName = $result.ServerName
            DatabaseName = $databaseName
            PreviousState = $previousState
            CurrentState = $currentState
            Timestamp = $timestamp
        }
        Write-Output (ConvertTo-Json -InputObject $changeDetails) | Out-File -FilePath $ChangesFound -Append
    }
}

# Store the current state in the JSON file
Write-Output (ConvertTo-Json -InputObject $results) | Out-File -FilePath $FileCurrentStatus

Example of ChangesFound.Json

{
    "CurrentState":  "READ_WRITE",
    "ServerName":  "ServerName.database.windows.net",
    "Timestamp":  "2023-06-29 19:40:05",
    "DatabaseName":  "DBName1",
    "PreviousState":  "READ_ONLY"
}

Example of CurrentStatus.Json

[
    {
        "StateDesc":  "READ_WRITE",
        "ServerName":  "servername.database.windows.net",
        "Timestamp":  "2023-06-29 19:40:05",
        "DatabaseName":  "DB1"
    },
    {
        "StateDesc":  "READ_WRITE",
        "ServerName":  "servername.database.windows.net",
        "Timestamp":  "2023-06-29 19:40:05",
        "DatabaseName":  "DB2"
    },
    {
        "StateDesc":  "READ_WRITE",
        "ServerName":  "servername.database.windows.net",
        "Timestamp":  "2023-06-29 19:40:05",
        "DatabaseName":  "DB3"
    },
    {
        "StateDesc":  "READ_WRITE",
        "ServerName":  "Servername.database.windows.net",
        "Timestamp":  "2023-06-29 19:40:05",
        "DatabaseName":  "DB4"
    }
]

Conclusion
In this article, we have explored how to monitor and track changes in the Query Data Store state in Azure SQL Database using PowerShell and SQLClient. By regularly executing the script, you can keep track of the Query Data Store state for all databases and detect any changes that occur. This helps you ensure the optimal functioning of the Query Data Store and proactively address any issues.

Feel free to customize the script to meet your specific requirements and scheduling preferences. Remember to run the script in a secure environment and protect sensitive information such as usernames, passwords, and file paths.

Enjoy!