Configure running the comparison and synchronize processes again in case the server connection fails using the PowerShell script

While comparing and synchronizing schemas of two databases via the CLI, you may face server connection failures. These connection failures could result from various factors such as network instability, server downtime, or configuration issues. It’s crucial to be prepared to handle such scenarios effectively to ensure uninterrupted database operations and maintain data integrity.

In this topic, we will provide you with a PowerShell script that automates the process of comparing and synchronizing database schemas using dbForge Schema Compare. It is designed to handle scenarios where server connections may fail, ensuring that the comparison process continues until it is successful or a maximum number of attempts is reached.

1. Save the script: Save the following PowerShell script with the .ps1 extension to your desired folder.

#region Variables

$rootFolder = "<script_folder>"
$databasesTxtPath = "<path_to_txt_file_with_databases>"
#Declare $diffToolLocation variable for dbForge Schema Compare for SQL Server
$diffToolLocation = "C:\Program Files\Devart\Compare Bundle for SQL Server\dbForge Schema Compare for SQL Server\schemacompare.com"

#endregion


foreach ($line in [System.IO.File]::ReadAllLines($databasesTxtPath)) {
   # Read the connection parameters for the current database from the configuration file
   $server = ($line -split ",")[0]
   $database = ($line -split ",")[1]
   $isWindowsAuthentication = ($line -split ",")[2]
   $userName = ($line -split ",")[3]
   $password = ($line -split ",")[4]
   $today = (Get-Date -Format "dd-MM-yyyy_HH_MM_ss")
   $ReportsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "DiffReports") 
   $logsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "\" + "Logs") 
   $srvCleanName = ($server -replace "\\", "")
   $currentReportFile = Join-Path $ReportsLocation "$srvCleanName.$database.$today"
   $logName = Join-Path $logsLocation "$srvCleanName.$database.$today.txt"
   $maxTries = 3
   $tryCount = 0
                    

   Write-Host "Server: $server; Database: $database; isWindowsAuthentication: $isWindowsAuthentication"

    
# Create connection string
   if ($isWindowsAuthentication -eq 'True') {
      $SourceConnectionString = "Data Source=$server;Initial Catalog=AW2022Dev;Integrated Security=True;"
      $TargetConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=True;"
           
                                            }
   else {
      $SourceConnectionString = "Data Source=$server;Initial Catalog=AW2022Dev;Integrated Security=False;User ID=$userName;Password=$password;"
      $TargetConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=False;User ID=$userName;Password=$password;"
                                
   
   
      do {
    $tryCount++


    # Log information about checking the database 
   New-Item -ItemType File -Force -Path $logName
   # Initiate the comparison of the Source Database with the multiple Targer Databases and generate a report   
   $process = Start-Process -FilePath $diffToolLocation -ArgumentList "/schemacompare /source connection:`"$SourceConnectionString`" /target connection:`"$TargetConnectionString`" /report:`"$currentReportFile`" /reportformat:html /includeobjects:All /log:`"$logName`"" -PassThru -Wait -windowstyle hidden 

    if ($process.ExitCode -eq 40) {
        Write-Host "Connection failed, retrying in 1 minute (attempt $tryCount of $maxTries)..."
        Start-Sleep -Seconds 60
    } elseif ($process.ExitCode -eq 101) {
        Write-Host "Connection successful!The source and the target are not identical.More details in DiffReports and in Log."
        break
    }
} while ($tryCount -lt $maxTries)

if ($tryCount -ge $maxTries) {
    Write-Host "Connection failed after $tryCount attempts."
}   
         
}
}

2. Create a configuration file: In the same folder, create a .txt file that will contain the comparison configurations, including server names, database names, login information, and passwords.

For example, the file should contain:

demo\SQLEXPRESS02,AW2022Dev,False,Username1,Password1
demo\SQLEXPRESS02,AW2022Prod1,False,Username2,Password2

Note

If the Windows authentication mode is enabled, you do not need to type your login(s) and password(s) in the file.

3. Edit the script: Run Windows PowerShell ISE as Administrator, open the script file by navigating to File > Open and adjust the values of $rootFolder and $databasesTxtPath to match your setup.

If dbForge Schema Compare isn’t located at C:\Program Files\Devart\, specify the correct path in the #$diffToolLocation variable.

Open the file