This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
In the dynamic realm of database management, particularly with Azure SQL Database, professionals often encounter unique challenges. One such issue arises while executing scripts using SQLCMD, where prolonged execution of prior commands can lead to disconnections, rendering subsequent commands without an available connection. This article aims to address this challenge by sharing an innovative script that significantly enhances the reliability of script execution.
The Problem:
When working with SQLCMD to execute scripts, a common hurdle is the potential loss of connection, especially if preceding commands take an extended time to execute. This disconnection can disrupt the execution flow, leading to incomplete transactions and potentially inconsistent data states across multiple databases.
The Solution:
To counteract this problem, we present a C# script that operates under a unique methodology. The script functions by reading a JSON file containing connection details and queries. This approach ensures that every aspect of the script execution, including connections to different databases in Azure SQL Database, is handled within a single transaction. The key here is the incorporation of retry mechanisms for both connections and query executions. This dual-layer of retries ensures that commands are executed as a single, cohesive unit, dramatically increasing reliability and consistency.
Understanding the Script:
The script is designed with simplicity and efficiency in mind. It begins by deserializing a JSON file, which contains the necessary details for connecting to various databases and the respective queries to be executed. Using the TransactionScope in C#, the script ensures that all database interactions are encapsulated within a single transaction. This approach is particularly beneficial when dealing with distributed databases, as it maintains data integrity across all involved databases.
Key Features of the Script:
-
Connection Retry Mechanism: The script attempts to establish a database connection multiple times (default is three retries). This feature is crucial for handling transient network issues or temporary unavailability of a database.
-
Query Execution Retry: Post successful connection, the script executes the provided SQL query. Similar to the connection, the query execution also has a retry mechanism. This is particularly useful for handling issues like query timeouts or momentary service disruptions.
-
Transaction Management: By using TransactionScope, the script ensures that all database operations either complete successfully together or roll back in unison, thus maintaining the atomicity of the entire operation.
-
Logging: The script logs important milestones and errors, offering insights into the execution process and aiding in troubleshooting if needed.
Conclusion:
This C# script, with its robust design leveraging JSON for configuration, offers a significant improvement in the reliability of executing SQL scripts via SQLCMD. The dual retry mechanisms for both connections and queries, combined with the transactional integrity offered by TransactionScope, make it an invaluable tool for database administrators and developers working with Azure SQL Database. We hope this script proves beneficial in your SQL endeavors, ensuring smooth and consistent database operations.
Closing Note:
Always test scripts in a safe, non-production environment first to ensure they meet your specific requirements and conform to your system's configurations. Happy scripting!
C# Code
using System;
using System.Collections.Generic;
using Microsoft.Data.SqlClient;
using System.IO;
using System.Transactions;
using Newtonsoft.Json;
namespace SqlCmd
{
public class ConnectionInfo
{
public string ConnectionString { get; set; }
public string Query { get; set; }
}
public class ConnectionConfig
{
public List<ConnectionInfo> Connections { get; set; }
}
class Program
{
static void Main(string[] args)
{
var config = JsonConvert.DeserializeObject<ConnectionConfig>(File.ReadAllText("C:\\temp\\SqlCMD\\SqlCmd.Json"));
using (var scope = new TransactionScope())
{
int iConnection = 0;
foreach (var connection in config.Connections)
{
iConnection++;
Log($"Process {iConnection}...");
bool success = TryConnection(connection);
if (!success)
{
Log($"Process {iConnection} failed...");
return; // End the program if any operation fails
}
Log($"Process {iConnection}...Success");
}
scope.Complete(); // Complete the transaction if all operations are successful
}
}
static bool TryConnection(ConnectionInfo connection)
{
int maxRetries = 3;
int retryCount = 0;
bool bQueryExecute = false;
bool bConnectionStatus =false;
while (retryCount < maxRetries)
{
TryOpenConnection(connection.ConnectionString, connection.Query, ref bQueryExecute, ref bConnectionStatus);
if( bConnectionStatus && !bQueryExecute)
{
return false;
}
if (bConnectionStatus && bQueryExecute)
{
return true;
}
Log("Failed to open connection. Retrying...");
retryCount++;
System.Threading.Thread.Sleep(5000); // Wait before retrying
}
return false; // Failed after all retries
}
static void TryOpenConnection(string connectionString, string sQuery, ref bool bQueryExecuteReturn, ref bool bConnectionMade)
{
try
{
using (var conn = new SqlConnection(connectionString))
{
Log("Opening the connection...");
conn.Open();
bQueryExecuteReturn = TryExecuteQuery(conn, sQuery);
bConnectionMade = true;
return; // Connection opened successfully
}
}
catch (Exception ex)
{
Log($"Connection error: {ex.Message}");
return;
}
}
static bool TryExecuteQuery(SqlConnection connection, String sQuery)
{
int maxRetries = 3;
int retryCount = 0;
int commandTimeout = 30; // Initial timeout in seconds
while (retryCount < maxRetries)
{
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery;
cmd.CommandTimeout = commandTimeout;
Log("Executing the command...");
cmd.ExecuteNonQuery();
return true; // Success in execution
}
}
catch (Exception ex)
{
Log($"Execution error: {ex.Message}. Retrying...");
retryCount++;
commandTimeout *= 2; // Double the timeout for the next attempt
}
}
return false; // Failed after all retries
}
static void Log(string message)
{
var now = DateTime.Now;
string logMessage = $"{now.ToString("yyyy-MM-dd HH:mm:ss.fff")}: {message}";
Console.WriteLine(logMessage);
}
}
}
JSON Configuration with connectionstring and query to execute
{
"Connections": [
{
"ConnectionString": "Server=tcp:servername.database.windows.net,1433;User Id=username;Password=Pwd!;Initial Catalog=DB1;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Pooling=true;Max Pool size=100;Min Pool Size=1;ConnectRetryCount=3;ConnectRetryInterval=10;Application Name=ConnTest",
"Query": "insert into T1 values(1)"
},
{
"ConnectionString": "Server=tcp:servername.database.windows.net,1433;User Id=username;Password=Pwd;Initial Catalog=DB2;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Pooling=true;Max Pool size=100;Min Pool Size=1;ConnectRetryCount=3;ConnectRetryInterval=10;Application Name=ConnTest",
"Query": "insert into T2 values(2)"
},
{
"ConnectionString": "Server=tcp:servername2.database.windows.net,1433;User Id=username;Password=pwd!;Initial Catalog=DB1;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Pooling=true;Max Pool size=100;Min Pool Size=1;ConnectRetryCount=3;ConnectRetryInterval=10;Application Name=ConnTest",
"Query": "insert into T3 values(3)"
}
]
}
