Lesson Learned #409:Resilient Azure SQL DB: Powering Performance with OpenAsync and ExecuteAsync
Published Jul 29 2023 09:11 AM 1,839 Views

Efficiently managing Azure SQL Database and Managed Instance operations is essential for developers and DBAs seeking high-performing, reliable applications in the cloud. This article introduces a powerful PowerShell script leveraging the OpenAsync technique, designed to optimize these Azure database solutions by implementing a resilient connection policy and enabling asynchronous query execution. Today, I would like to share several topics about OpenAsync and ExecuteAsync learned about several support cases worked on previously.

 

The Script: Harnessing the Power of OpenAsync

 

The provided PowerShell script showcases two fundamental functions: Open-ConnectionAsync and Execute-QueryAsync. These functions capitalize on the OpenAsync technique to revolutionize the way connections are established and queries are executed in Azure SQL Database and Managed Instance environments.

 

1. Open-ConnectionAsync Function

This function establishes an asynchronous connection to the target Azure database and incorporates the following key features:

  • Asynchronous Connection: The use of OpenAsync enables the script to asynchronously establish connections, preventing application blocking and ensuring high responsiveness.

  • Exponential Backoff: The function gracefully handles transient connectivity issues with an exponential backoff strategy, intelligently delaying connection retries and allowing the infrastructure to recover from potential disruptions.

 

2. Execute-QueryAsync Function

This function revolutionizes query execution by leveraging the OpenAsync technique and includes the following essential elements:

  • Asynchronous Query Execution: By utilizing the OpenAsync approach, queries are executed asynchronously, maximizing application performance and responsiveness.

  • Dynamic Timeout Adjustment: The function applies a backoff mechanism to dynamically adjust the CommandTimeout property during query execution retries. This intelligent approach extends query execution time, significantly reducing the likelihood of timeouts due to transient performance fluctuations.

 

Empowering Developers and DBAs

 

The script offers developers and DBAs significant advantages:

1. Resilience in Connectivity: The OpenAsync technique combined with exponential backoff ensures that connection attempts are smartly retried, minimizing the impact of temporary connection issues and promoting application stability.

2. Optimized Query Performance: Asynchronous query execution and dynamic CommandTimeout adjustment guarantee queries have sufficient time to complete successfully, enhancing application performance even during peak database activity.

3. Tailored Customization: The script's adjustable parameters, including $totalQueries, $maxConnectionAttempts, and $maxQueryAttempts, provide developers and DBAs with the flexibility to tailor the script to their specific use cases.

 

Conclusion

By leveraging the power of OpenAsync, developers and DBAs can unlock the full potential of Azure SQL Database and Managed Instance. Our PowerShell script empowers them to optimize connection handling and query execution, ensuring a resilient and high-performing experience in the Azure cloud.

 

Disclaimer: The PowerShell script is intended for educational purposes only. Prior to applying any script to production systems, developers and DBAs should thoroughly test it in a controlled environment and adhere to security and performance best practices.

 

function Open-ConnectionAsync {
    param (
        [string]$connectionString,
        [int]$maxAttempts
    )

    $attempts = 1

    while ($attempts -le $maxAttempts) {
        try {
            $connection = New-Object System.Data.SqlClient.SqlConnection
            $connection.ConnectionString = $connectionString

            Write-Host "$(Get-Date) - Attempting to establish connection..."
            $openTask = $connection.OpenAsync()

            # Esperar hasta 0.5 segundos (500 milisegundos) para la conexión.
            Start-Sleep -Milliseconds 50

            if ($openTask.IsCompleted) {
             If( -not $openTask.IsFaulted -and -not $openTask.IsCanceled)
              {
                Write-Host "$(Get-Date) - Connection established successfully."
                return $connection
              }
            }
        }
        catch {
            $attempts++
            $backoffTime = [math]::Pow(2, $attempts) * 1000
            Write-Host "$(Get-Date) - Error establishing connection: $_.Exception.Message. Retrying in $backoffTime milliseconds..."
            Start-Sleep -Milliseconds $backoffTime
        }
    }

    Write-Host "$(Get-Date) - Maximum connection attempts reached. Could not establish connection."
    return $null
}

function Execute-QueryAsync {
    param (
        [System.Data.SqlClient.SqlConnection]$connection,
        [string]$query,
        [int]$queryNumber,
        [int]$maxAttempts
    )

    for ($i = 1; $i -le $maxAttempts; $i++) {
        $queryTimer = [System.Diagnostics.Stopwatch]::StartNew()

        try {
            $command = $connection.CreateCommand()
            $command.CommandText = $query

            Write-Host "$(Get-Date) - Attempt $i to execute Query $queryNumber..."
            $command.CommandTimeout = 30 + ([math]::Pow(2, $i) * 10)  # Increase CommandTimeout with backoff value

            $openTask = $command.ExecuteNonQueryAsync()

            # Esperar hasta 0.5 segundos (500 milisegundos) para la ejecución de la consulta.
            Start-Sleep -Milliseconds 50

            if ($openTask.IsCompleted) {
             If( -not $openTask.IsFaulted -and -not $openTask.IsCanceled)
              {
                $queryTimer.Stop()
                Write-Host "$(Get-Date) - Query $queryNumber, attempt $($i + 1) executed successfully. Execution time: $($queryTimer.Elapsed.ToString())"
                return
              }
             else 
             {
                Write-Host "$(Get-Date) - Query $queryNumber, attempt $($i + 1) execution failed. Retrying..."
                throw "- Query $queryNumber, attempt $($i + 1) execution failed. Retrying..."
             }
            }
        }
        catch {
            $queryTimer.Stop()
            Write-Host "$(Get-Date) - Error executing query $queryNumber, attempt $($i + 1): $_.Exception.Message. Execution time: $($queryTimer.Elapsed.ToString())"
            if ($i -lt $maxAttempts - 1) {
                $backoffTime = [math]::Pow(2, $i) * 1000
                Write-Host "$(Get-Date) - Retrying query $queryNumber in $backoffTime milliseconds..."
                Start-Sleep -Milliseconds $backoffTime
            }
            else {
                Write-Host "$(Get-Date) - Maximum query attempts reached. Moving to the next query."
            }
        }
    }
}

$connectionString = "data source=tcp:servername.database.windows.net,1433;initial catalog=DBName;User ID=UserName;Password=Password; ConnectRetryCount = 3; ConnectRetryInterval = 10; Connection Timeout = 30; Max Pool Size = 100; MultipleActiveResultSets = false; Min Pool Size = 1; Application Name = Testing by JMJD -SQL; Pooling = True;Connection Timeout=5" 

$totalQueries = 300
$maxConnectionAttempts = 10
$maxQueryAttempts = 10

for ($i = 1; $i -le $totalQueries; $i++) {
    $connection = Open-ConnectionAsync -connectionString $connectionString -maxAttempts $maxConnectionAttempts

    if ($connection) {
        $query = "SELECT 1"
        Execute-QueryAsync -connection $connection -query $query -queryNumber ($i + 1) -maxAttempts $maxQueryAttempts
        $connection.Close()
    }
}

Write-Host "$(Get-Date) - End of the script."

 

Version history
Last update:
‎Jul 29 2023 09:11 AM
Updated by: