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

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:

Click To See Full Image.

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 synchro"