This article is contributed. See the original author and article here.
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
CommandTimeoutproperty 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."
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments