Lesson Learned #436: Implementing Retry Mechanism with sqlcmd in Bash (Linux)

Introduction:

Efficiently managing temporary failures and timeouts is crucial in production environments when connecting to databases. In this article, we’ll explore how to implement a retry mechanism with sqlcmd in a Bash script, dynamically increasing timeouts with each failed attempt.

Problem Statement:

Operations can fail due to network issues, overloaded servers, or other temporary problems when interacting with databases. Implementing a retry mechanism helps address these temporary issues without manual intervention.

Solution Overview:

Below is a Bash script utilizing sqlcmd to interact with an SQL Server database. The script attempts to execute an SQL command and, upon failure, retries the operation up to a maximum number of times, incrementing timeouts with each attempt.

#!/bin/bash

# Variable declaration
MAX_RETRIES=5
COUNT=0
SERVER="server.database.windows.net"
DATABASE="dbname"
USER="username"
PASSWORD="Password123!"
INPUT_FILE="/home/user/sql.sql"
LOG_FILE="/home/user/logfile.txt"
LOGIN_TIMEOUT=15  # Timeout for login, in seconds
QUERY_TIMEOUT=30  # Timeout for query, in seconds

# Main loop for retry mechanism
while [ $COUNT -lt $MAX_RETRIES ]; do
  # Execute sqlcmd and capture the return value
  sqlcmd -S $SERVER -d $DATABASE -U $USER -P $PASSWORD -i $INPUT_FILE -l $LOGIN_TIMEOUT -t $QUERY_TIMEOUT 2>>$LOG_FILE
  RETVAL=$?
  
  # Get current date and time
  timestamp=$(date +"%Y-%m-%d %H:%M:%S")

  # Check the return value
  if [ $RETVAL -eq 0 ]; then
    message="$timestamp - sqlcmd executed successfully with login timeout $LOGIN_TIMEOUT and query timeout $QUERY_TIMEOUT!"
    echo $message
    echo $message >> $LOG_FILE
    break
  else
    message="$timestamp - Error with sqlcmd, retrying with increased timeouts..."
    echo $message
    echo $message >> $LOG_FILE
    
    # Increase timeouts by fixed amounts
    LOGIN_TIMEOUT=$((LOGIN_TIMEOUT + 15))
    QUERY_TIMEOUT=$((QUERY_TIMEOUT + 30))

    ((COUNT++))
    # Wait for 5 seconds before retrying; adjust as needed
    sleep 5
  fi
done

# Get current date and time
timestamp=$(date +"%Y-%m-%d %H:%M:%S")

# Check if the maximum number of attempts was reached
if [ $COUNT -eq $MAX_RETRIES ]; then
  message="$timestamp - Failed after $MAX_RETRIES attempts with login timeout $LOGIN_TIMEOUT and query timeout $QUERY_TIMEOUT."
  echo $message
  echo $message >> $LOG_FILE
fi

Script Explanation:

• Variable Declaration: The script begins by declaring variables like the maximum number of retries, counter, server, database, user, password, input file, and timeouts.

• Main Loop: A while loop serves as the main retry mechanism. The script runs sqlcmd and captures its return value.

• Return Value Check: If sqlcmd executes successfully (return value 0), the script prints a success message and terminates. Otherwise, it prints an error message, increments timeouts and the retry counter, and sleeps for a while before retrying.

• Timeout Increment: On each failed retry, login and query timeouts are increased by 15 and 30 seconds respectively, providing more time for subsequent operations.

Conclusion:

This Bash script is a valuable tool for managing database operations that might face temporary issues. With a retry mechanism and dynamically adjusted timeouts, you can enhance the robustness and reliability of your database interactions in unstable or high-load environments. We hope this article provided insightful information on effectively implementing a retry mechanism with sqlcmd in Bash. For optimal results, adjust variable values and timeouts to cater to your specific needs and production environment.