Using a Script to Compare Random Value Samples

To enable the ECS to communicate with the source and destination databases, you need to configure security groups for the source and destination databases.

• Setting the server firewall at the source end

• Configuring a security group at the destination end

• Result

  

For details, see Installing sqlcmd and bcp SQL Server Command Line Tools on Linux.

The ECS check script is as follows:

#!/bin/bash

# Log function
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}

# Error processing function
error_exit() {
    log "Error: $1"
    exit 1
}

# Check parameters.
if [ "$#" -ne 2 ]; then
    error_exit "Usage: $0 <configuration file path> <table information file path>"
fi

config_file=$1
table_info_file=$2

# Check whether the file exists.
[ -f "$config_file" ] || error_exit "The configuration file $config_file does not exist."
[ -f "$table_info_file" ] || error_exit "The table information file $table_info_file does not exist."

# Read the first piece of SQL Server connection information.
server1=$(awk -F'=' '/^server1/ {print $2}' "$config_file" | tr -d ' ')
user1=$(awk -F'=' '/^user1/ {print $2}' "$config_file" | tr -d ' ')
password1=$(awk -F'=' '/^password1/ {print $2}' "$config_file" | tr -d ' ')
database1=$(awk -F'=' '/^database1/ {print $2}' "$config_file" | tr -d ' ')

# Read the second piece of SQL Server connection information.
server2=$(awk -F'=' '/^server2/ {print $2}' "$config_file" | tr -d ' ')
user2=$(awk -F'=' '/^user2/ {print $2}' "$config_file" | tr -d ' ')
password2=$(awk -F'=' '/^password2/ {print $2}' "$config_file" | tr -d ' ')
database2=$(awk -F'=' '/^database2/ {print $2}' "$config_file" | tr -d ' ')

# Check whether the connection information is complete.
if [ -z "$server1" ] || [ -z "$user1" ] || [ -z "$password1" ] || [ -z "$database1" ] ||
   [ -z "$server2" ] || [ -z "$user2" ] || [ -z "$password2" ] || [ -z "$database2" ]; then
    error_exit "The configuration file does not contain the necessary connection information."
fi

# Check whether sqlcmd is available.
command -v sqlcmd >/dev/null 2>&1 || error_exit "The sqlcmd command is not found. Ensure that the SQL Server command line tool has been installed."

# Process the table information file.
total_tables=0
success_tables=0
failed_tables=0

for line in `cat  $table_info_file`
do
    # Remove spaces.
    table1=$(echo $line | awk -F',' '{print $1}' | tr -d ' ')
    table2=$(echo $line | awk -F',' '{print $2}' | tr -d ' ')
    pk_column=$(echo $line | awk -F',' '{print $3}' | tr -d ' ')
    gt_value=$(echo $line | awk -F',' '{print $4}' | tr -d ' ')
    lt_value=$(echo $line | awk -F',' '{print $5}' | tr -d ' ')

    ((total_tables++))
    log "Start comparing tables #$total_tables: $table1 (server 1) and $table2 (server 2)"

    # Build the WHERE clause.
    where_clause=""
    if [ -n "$pk_column" ]; then
        conditions=()

        if [ -n "$gt_value" ]; then
            conditions+=("$pk_column > $gt_value")
        fi

        if [ -n "$lt_value" ]; then
            conditions+=("$pk_column < $lt_value")
        fi

        if [ ${#conditions[@]} -gt 0 ]; then
            where_clause="WHERE $(printf '%s AND ' "${conditions[@]}" | sed 's/ AND $//')"
        fi
    fi

    # Obtain the checksum of table 1.
    sql1="SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS checksum FROM $table1 $where_clause"
    checksum1=$(sqlcmd -S "$server1" -U "$user1" -P "$password1" -d "$database1" -h -1 -Q "SET NOCOUNT ON; $sql1" 2>/dev/null | tr -d ' ' | tr -d '\r\n')

    if [ $? -ne 0 ]; then
        log "Warning: The checksum of $table1 cannot be obtained. Skip this table."
        ((failed_tables++))
        echo "----------------------------------------"
        continue
    fi

    # Obtain the checksum of table 2.
    sql2="SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS checksum FROM $table2 $where_clause"

    checksum2=$(sqlcmd -S "$server2" -U "$user2" -P "$password2" -d "$database2" -h -1 -Q "SET NOCOUNT ON; $sql2" 2>/dev/null | tr -d ' ' | tr -d '\r\n')

    if [ $? -ne 0 ]; then
        log "Warning: The checksum of $table2 cannot be obtained. Skip this table."
        ((failed_tables++))
        echo "----------------------------------------"
        continue
    fi


    # Compare checksums.
    if [ "$checksum1" == "$checksum2" ]; then
        log "Result: The table content is consistent (Checksum: $checksum1)."
        ((success_tables++))
    else
        log "Result: The table content is inconsistent."
        log "Table $table1 (server 1) Checksum: $checksum1"
        log "Table $table2 (server 2) Checksum: $checksum2"
        ((failed_tables++))

    fi

    echo "----------------------------------------"
done

log "Comparison completed."
log "Total tables: $total_tables"
log "Tables that are consistent: $success_tables"
log "Tables that are inconsistent: $failed_tables"

if [ "$failed_tables" -gt 0 ]; then
    exit 1
else
    exit 0
fi

sh chech.sh config.cfg table_info.txt