How to compare and synchronize databases programmatically

ApexSQL Diff and ApexSQL Data Diff provide a user friendly GUI for SQL comparison and synchronization of database schemas and database objects. They both include the command line interface which provides the exact same options, and which you can use to schedule unattended comparisons and synchronizations

But, what can be done when the features that the GUI and CLI provide are simply not enough? In such cases a more flexible solution is needed and the good news is – there’s a programmable API

The scenarios where such custom solutions are recommended are:

• Automating database comparison and synchronization when both structure and data must be compared. Without the API, you would have to run ApexSQL Diff first to compare and synchronize schemas, and then run ApexSQL Data Diff to compare and synchronize data. Not the automation you’re looking for
• A database with a large number of records in its tables – e.g. tens of millions of records in a couple of tables. Comparing and synchronizing such a large number of records takes a long time. The solution would be to use the API to customize the comparison to subsets of records and compare them one by one
• Database comparison and synchronization using complex comparison criteria
• Database synchronization with complex record changes
• Integrating the ApexSQL Diff and ApexSQL Data Diff functionalities into your solutions
• When complex actions should be taken before or after the synchronization, which can’t be done using the pre and post processing scripts that can be added through the GUI
• Exporting comparison results into more than one report type for the same comparison process

ApexSQL Diff API is a SQL Server database comparison and synchronization API, which allows .NET applications to detect differences between database objects and data, and resolve them without errors. It provides custom .NET solutions with the ability to generate comprehensive reports on the found differences, and can automate the synchronization process between live and versioned databases, backups, snapshots, and script folders

The API enables specify data sources, synchronization script properties, generated reports, issues like whether to create a database backup before the synchronization or not, etc. ApexSQL Diff API has a C# working example shipped along with the API installation, located in the Demo subfolder of the installation folder, which you can open and modify in Visual Studio

Example 1:

Create two different types of reports and a synchronization script, and compare a database and database backup

public void Run(){
   // Create a data project with databases as datasources
 DataProject data = 
                    new DataProject(new ConnectionProperties(SERVER1,DATABASE1),
                    new ConnectionProperties(SERVER2, DATABASE2));
 data.SetProgressCallback(cps);

 // Create a CSV report
 MakeDataCsvReport(data);

 // Create an HTML report
 MakeDataHtmlReport(data);

 // Create a synchronization script
 bool executeDataScript = false;
 MakeDataSyncScript(data, SYNC_SCRIPT_PATH, executeDataScript);
 data.SaveToDisk(PROJECT_PATH);

 // Compare database vs backup
 DataProject backup =
                 new DataProject(new ConnectionProperties(SERVER1,DATABASE1),
                 new string[] { BACKUP_PATH });
   backup.SetProgressCallback(cps);
   backup.Compare();

   Console.WriteLine("Press [Enter]");
   Console.ReadLine();}

Example 2:

Compare only data within tables. The tables, the names of which start with “sales” and “title”, will be excluded from comparison

private void SetDataObjectTypesToCompare()
   {
     data.ObjectTypeFilter = SqlObjectType.Table;//load tables only
   }
     private void ApplyDataExclude()
   {
     data.MappedTables.ExcludeFromComparison(new string[] { 
          "^sales", "^title" });
   }

Example 3:

Compare only the tables named ProductionMaterials and ProductionStats – exclude all objects from comparison first, and then include only specific tables

data.ObjectTypeFilter = 
    SqlObjectType.Table;data.MappedTables.ExcludeAllFromComparison();
   //Add "^" before the first character and "$" after the last in table names
    string[] tablesToComapre = new string[] 
       { "^ProductionMaterials$", "^ProductionStats$" };
    data.MappedTables.IncludeInComparison(tablesToComapre);
    data.SynchronizationOptions.SynchronizationDirection 
= SynchronizationDirection.SourceToDestination;
   data.Compare();
   try
       {
       //data.ComparedObjects.IncludeAllInSynchronization();
       foreach(ComparedDataObject<Table> table in data.ComparedTables)
       {
           if(string.Equals(table.SourceName, " ProductionMaterials"))
           {
              //do anything with this table      
              table.IncludeInSynchronization = true;
              Console.WriteLine(
    string.Format("[{0}].[{1}] should be synced: {2}", 
    table.SourceOwner, table.SourceName, table.IncludeInSynchronization));
           }else if(string.Equals(table.SourceName, " ProductionStats"))
           {
              //do anything with this table
              table.IncludeInSynchronization = true;
              Console.WriteLine(
    string.Format("[{0}].[{1}] should be synced: {2}", 
    table.SourceOwner, table.SourceName, table.IncludeInSynchronization));
           }
       }

These were just a few simple examples that show only a small number of possible customizations when comparing and synchronizing databases

With ApexSQL Diff API there’s no need to write custom comparison and synchronization solutions from scratch. Use ready-made code to add complex ApexSQL Diff and ApexSQL Data Diff features into your solutions.

Unfortunately, ApexSQL Diff API has been discontinued. More information on this can be found in the article from this link.