Schema Compare in SqlPackage and the Data-Tier Application Framework (DACFx)

In the most recent version of SqlPackage.exe and the Data-Tier Application Framework (DACFx), we have introduced some new functionality: Schema Compare! A couple of years ago we added MSBuild support for Schema Compare.  We’ve now extended that support to SqlPackage.exe and DACFx.

SqlPackage.exe

SqlPackage.exe’s Publish and Script actions now accept Schema Compare (.scmp) files created by Visual Studio. The Schema Compare file specifies the source and target, which can be either a database or a dacpac file. Note: Schema Compare files that have a database project as the source or target are not supported at this time. Your schema compare file can, though, reference the dacpac file from your database project’s build output.

The primary advantage of publishing or scripting with a Schema Compare (.scmp) file instead of a dacpac file is that you can specify individual objects to exclude from the update.

Example usage:
SqlPackage.exe /Action:publish /SourceFile:c:\project\schema_compare.scmp
SqlPackage.exe /Action:script /SourceFile:c:\project\schema_compare.scmp

Data-Tier Application Framework (DACFx)

We’ve created a new public API with the namespace Microsoft.SqlServer.Dac.Compare in the Microsoft.SqlServer.Dac.Extensions.dll assembly. Using this API you can programmatically:

  • Load or create a Schema Compare (.scmp) file
  • Compare schemas and view results
  • Exclude specific differences
  • Create a script or publish to a target database

Example usage:

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dac.Compare;

class Program
{
    static void Main(string[] args)
    {
        var sourceDacpac = new SchemaCompareDacpacEndpoint(@"C:\project\mydb.dacpac");

        var csb = new SqlConnectionStringBuilder();
        csb.DataSource = "SERVER";
        csb.InitialCatalog = "DATABASE";
        csb.IntegratedSecurity = true;
        var targetDatabase = new SchemaCompareDatabaseEndpoint(csb.ToString());
        
        var comparison = new SchemaComparison(sourceDacpac, targetDatabase);
        // Persist comparison file to disk in Schema Compare (.scmp) format
        comparison.SaveToFile(@"C:\project\mycomparison.scmp");

        // Load comparison from Schema Compare (.scmp) file
        comparison = new SchemaComparison(@"C:\project\mycomparison.scmp");
        SchemaComparisonResult comparisonResult = comparison.Compare();

        // Find the change to table1 and exclude it.
        foreach (SchemaDifference difference in comparisonResult.Differences)
        {
            if (difference.TargetObject.Name != null && 
                difference.TargetObject.Name.HasName && 
                difference.TargetObject.Name.Parts[1] == "table1")
            {
                comparisonResult.Exclude(difference);
                break;
            }
        }

        // Publish the changes to the target database
        SchemaComparePublishResult publishResult = comparisonResult.PublishChangesToTarget();

        Console.WriteLine(publishResult.Success ? "Publish succeeded." : "Publish failed.");
    }
}