How to automatically compare and synchronize multiple databases on different SQL Server instances

Challenge

It’s often quite a challenge to keep all SQL databases located on different SQL Servers in sync. As time goes by, a lot of schema and data changes are made on QA databases on daily basis that require to be in sync with Production databases.

To keep everything in sync, there should a system that would be either triggered or scheduled to run the comparison of all SQL databases and synchronize the ones were changes are detected. This system should be also aware of any dependencies during the synchronization in order to keep the SQL database integrity.

Solution

A third-party tool, ApexSQL Diff can be used as a solution for this challenge, as it can compare and synchronize live SQL databases, source control repositories, snapshots, backups, and script folders. Also, ApexSQL Data Diff can be used for data comparison and synchronization of databases, backups, and source control projects.

During one process, both ApexSQL Diff and ApexSQL Data Diff can compare one source against one destination SQL database, but if it’s combined with a PowerShell script, a system can be created that will compare and synchronize a list of SQL databases on different servers, along with creating reports, outputs and summaries for each comparison.

In the following example, the whole process will be shown how to automatically compare and synchronize schemas for multiple SQL Server databases, but both scripts for setting up the system for schema and data comparison and synchronization can be downloaded from our GitHub repository.

Defining folders

The first thing that will be also the start of the PowerShell script is creating the function that will check if the folders that will contain created reports, outputs, and summaries exist, and if these folders don’t exist it will create them:

#existence check for Reports, Outputs, or Summaries folders, and if they don’t exist they will be created
function FolderCheckAndCreate
{
    param 
    (
        [string] $folderRoot, 
        [switch] $reports, 
        [switch] $outputs,
        [switch] $summaries
    )

    $locations = $rootFolder

    #location will be set using the corresponding switch
    if ($reports -eq $true)
    {
        $locations += "\Reports"
    }
    if ($outputs -eq $true)
    {
        $locations += "\Outputs"
    }
    if ($summaries -eq $true)
    {
        $locations += "\Summaries"
    }
    #create none existing folder and return folder’s path
    if (-not (Test-Path $locations))
    { 
        New-Item -Path $locations -ItemType Directory -Force | Out-Null
    }
    return $locations
}

Defining location variables

The next part is to define variables that will recognize the current path and set it as the root folder.

#variables for the current path recognition 
$pathCurrent = (Split-Path $SCRIPT:MyInvocation.MyCommand.Path)

#system’s root folder 
$folderRoot = "pathCurrent"

In this case, if a folder is created on desktop and this PowerShell script is run from it, the current path will use the root of that folder as a starting point for creating all needed folders.

Next, let’s define variable for ApexSQL Diff installation location. In example below, default installation location is used:

#location where ApexSQL Diff is installed
$appLocation = "ApexSQLDiff.com"

The following variable defines the execution summary file, that will be shown when all SQL databases are compared and synchronized:

$executionSummary = "$folderRoot\ExecutionSummary.txt"
Clear-Content -Path $executionSummary

The next variable defines the location of the txt file that contains the list of all SQL Server and database names that will be compared:

#location of the txt file with server and database names
$locationServersDatabases = "$pathCurrent\servers_databases.txt"

In one line, this txt file contains source server and database name, and destination server and database. The system will go through each line and run the comparison and synchronization for each one:

In this case, Windows authentication was assumed, however SQL Server authentication can be used as well and you can find more about ways of handling database/login credentials.

Creating the schema synchronization system

The next phase in the PowerShell script is creating function that will go through each line of the previously mentioned txt file:

foreach($line in [System.IO.File]::ReadAllLines($locationServersDatabases))
{

Let’s now define variables for source and destination servers, along with location of all output files and their names:

#defining variables for source and destination servers and databases
    $server1   = ($line -split ",")[0]    
    $database1 = ($line -split ",")[1]
    $server2   = ($line -split ",")[2]    
    $database2 = ($line -split ",")[3]

    #defining variables for location of all output files
    $locationReports   = FolderCheckAndCreate "$folderRoot\$server2" -Reports
    $locationOutputs   = FolderCheckAndCreate "$folderRoot\$server2" -Outputs
    $locationSummaries = FolderCheckAndCreate "$folderRoot\$server2" -Summaries 

    #defining variables for date stamp and names for all output files
    $cleanServerName1   = ($server1 -replace "\\",".")
    $cleanServerName2   = ($server2 -replace "\\",".")
    $stampDateTime      = (Get-Date -Format "MMddyyyy_HHMMss")

    $nameReport   = "$cleanServerName2.$database2.SchemaReport_$stampDateTime.html"
    $nameOutput   = "$cleanServerName2.$database2.SchemaLog_$stampDateTime.txt"
    $nameSummary  = "$cleanServerName2.$database2.SchemaSummary_$stampDateTime.txt"

The following variable will define all needed ApexSQL Diff CLI switches, such as source and destination servers and databases, output file, HTML report, and summary report:

#defining variable for ApexSQL Diff CLI switches
    $schemaSwitches = "/s1:""$server1"" /d1:""$database1"" /s2:""$server2"" 
/d2:""$database2"" /ot:html /hro:d e s t is /on:""$locationReports\$nameReport"" 
/suo:""$locationSummaries\$nameSummary"" /out:""$locationOutputs\$nameOutput"" /sync 
/f /v /rece

In the following part, expression in PowerShell will start the schema comparison and synchronization process for the first line:

#initiation of the schema comparison and synchronization process     
    (Invoke-Expression ("& `"" + $appLocation +"`" " +$schemaSwitches))
    $returnCode = $lastExitCode

The next part is “responsible” to utilize the “no difference code”, remove all output files if there are no differences and to provide, and to provide corresponding error return code if any error is encountered:

#differences in schema are detected
    if($returnCode -eq 0)
    {
        #synchronize databases and create a report
        "`r`nSchema differences are found and a report is created. Return code is: $lastExitCode" >> "$locationOutputs\$nameOutput"
    }
    elseif($returnCode -eq 102)
    {
        #the newly created report will be removed, as no differences are found
        if(Test-Path "$reportsLocation\$reportName")
        { 
            Remove-Item -Path "$locationReports\$nameReport" -Force:$true -Confirm:$true 
            Remove-Item -Path "$locationSummaries\$nameSummary" -Force:$true -Confirm:$true
            Remove-Item -Path "$locationOutputs\$nameOutput" -Force:$true -Confirm:$true
        }
        "`r`nDifferences are not detected and the latest output files are deleted. Return code is: $lastExitCode" >> "$locationOutputs\$nameOutput"
    }
    #an error is encountered
    else   
    {
        "`r`nAn error is encountered. Return error code is: $lastExitCode" >> "$locationOutputs\$nameOutput"
        "Failed for server: $server2 database: $database2. Return error code is: $lastExitCode" >> $executionSummary
        #the output file will be opened, as an error is encountered         
    }
}

At the end, the Execution summary file will be shown with a message that everything was successfully synchronized or with corresponding messages for each destination server and database that was not successfully synchronized:

if ([System.IO.File]::ReadAllLines($executionSummary).Count -eq 0)
{
    "Synchronization was successful for all data sources or no differences were detected" > $executionSummary
}
Invoke-Item -Path $executionSummary

Setting up e-mail alert

In addition to previously described system of schema comparison and synchronization, we can set up an e-mail alert system, so that e-mail with corresponding results are sent to notify about the execution summary, upon the finished execution process.

System scheduling

After the whole system is automatized by using a PowerShell script and ApexSQL Diff, it can be run with by executing the PowerShell script with a click. The next step is to schedule the PowerShell script to run this system each night at 2AM for example. To find out more about scheduling, check out the article about the ways of scheduling ApexSQL tools.

Reviewing system results

Now, when everything is set up, scheduled, let’s review what was created during the execution process:

1. Script will run the comparison and synchronization for all databases that were set in txt file, one by one, as specified in lines
2. Three folders will be automatically created in the root folder: Reports, Outputs, Summaries

   

3. The following output files will be created in corresponding folder:
   1. Named (destination server and database will be in the name) and data stamped HTML reports that will contain comparison differences.

      Example of the HTML report name:

      DestinationServer1.DestinationDatabase1.SchemaReport_03302017_170352.html

      

      When opened, HTML report will contain the Comparison summary and Comparison results by object, where scripts can be reviewed as well:

      

   2. Named (destination server and database will be in the name) and data stamped synchronization summaries that will contain how many objects were updated/added/deleted.

      Example of the summary name:

      DestinationServer1.DestinationDatabase1.SchemaSummary_03302017_170445.txt

      

      When any of these schema summaries is opened for reviewing, it will contain a table with changes that were made and in this example, one procedure and DDL trigger were added to the destination database, while one table was deleted from the destination database:

      

   3. Named (destination server and database will be in the name) and data stamped output logs that will contain the whole process from the CLI.

      Example of the output name:

      DestinationServer1.DestinationDatabase1.SchemaLog_03302017_170452.txt

      

      If the ExecutionSummary.txt provided an error for specific DestinationServer. DestinationDatabase, we can easily track down that specific output file open it and review what was the issue:

      

      After checking the “ShopQA” database on server “CRAZY”, it’s determined that database was wrongly named, as it should be “ShopProd” and therefore we got the above shown message.

      To learn more about common return error codes, check out article about General usage and the common CLI switches for ApexSQL tools.

4. For each comparison, 3 potential outcomes are possible and handled:
1. There are differences – data sources will be synchronized and all output files will be created
2. No differences are detected – all created output files will be removed, as there is nothing to be shown
3. An error occurred – error will be available for reviewing in the output file and in the Execution summary

5. Once the whole process is completed, ExecutionSummary.txt will be opened and it will contain two possible information:
   1. An error was encountered during the execution and corresponding message is shown:

      

      After analyzing this issue in corresponding log file and fixing it, re-run the process.

   2. If there were no issues encountered and everything was successfully compared and synchronized, the following message will be shown at the end:

      

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.