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.scmpSqlPackage.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."); } }
Comments
- Anonymous
October 04, 2016
Hi Steven,Can you elaborate on what the possibilities when it comes to viewing schema comparison results when using the DACFx API? I have run the code you have provided and I can see that I can get the names and the object types (stored procedure, table, etc) as well as the SchemaUpdateAction (Add, Change, Delete) that are stored in the SchemaComparisonResult Differences object. Is there anyway that I can examine what the difference actually is? Adds and Deletes aren't an issue but when the SchemaUpdateAction value is Change how am I able to determine what that change actually is?Thanks- Anonymous
October 04, 2016
Here is how I am looking for differences that are changed:foreach (SchemaDifference difference in comparisonResult.Differences){ if (difference.UpdateAction == SchemaUpdateAction.Change) { var diff = difference; } }and I've been looking through the diff object to see if I can see what the change actually is.- Anonymous
October 04, 2016
Sorry for all the spam.Eventually found it: SourceObject.GetScript()
- Anonymous
- Anonymous
- Anonymous
November 09, 2016
I am investigating the use of these tools and nuget to help database schema reuse between projects. A trivial example is a logging framework that can be dropped into multiple projects.In an ideal world, I would have a Logger solution that consists of a database project and a logging library project (DLL) accessing the database. The nuget package would contain the DLL and a dacpac for the logger schema.When I have a reference to the nuget package from a DB project, I would expect the schema to be applied to the referenced database when the reference is included or updated.Here is the crunch: when I do a schema comparison between the DB project and the referenced database, I would expect the logger objects to be ignored.- Anonymous
November 09, 2016
The DLL is optional. I expect the logger functionality to be used by the referencing application, as if it was part of its own code.The Logger solution should have something like a unit test project used by the developer and the CI process, but not to be included in the nuget package. - Anonymous
November 09, 2016
I know specifying objects to be compared is the point of this blog. I want to avoid having to maintain .scmp file for this purpose. We currently have an excessively baroque CI process with lots of custimzations like this. My mandate is to find standard tools that support our needs in a standard way.
- Anonymous
- Anonymous
December 14, 2016
Is there a way to programmatically update a Database Project from a database or from a dacpac? What is schema compare "Update Target" in visual studio using under the hood when a project is set as the target. - Anonymous
January 12, 2017
I attempted doing a comparison based on this post using connection strings and SchemaCompareDatabaseEndpoint objects, but the comparison doesn't seem to do any comparing. The ComparisonResult object throws a null ref exception:public SchemaComparisonResult Compare(string sourceConnectionString, string targetConnectionString) { var sourceEndPoint = new SchemaCompareDatabaseEndpoint(sourceConnectionString); var targetEndPoint = new SchemaCompareDatabaseEndpoint(targetConnectionString); var comparision = new SchemaComparison(sourceEndPoint, targetEndPoint); var result = comparision.Compare(); return result; }System.ArgumentNullException: Value cannot be null.Parameter name: source at System.Linq.Enumerable.Count[TSource](IEnumerable`1 source)Help is appreciated!- Anonymous
November 07, 2017
I have exactly the same problem. Even when doing a DacPac to DB comparison I always get an object back who "IsValid" property is false with null differences.
- Anonymous
- Anonymous
March 01, 2017
Hi for all, good article, but I am needing to make an application that allows me to perform the comparison of schemas in two databases.Currently by the Visual Studio 2013 wizard, I can select the objects that I want to compare, this generates a file of type *.scmp, it happens that when I want to re-run the comparison the selected objects are lost, and I have to select them again.This is why I want to create an application that allows me to enter the names of the tables and / or other objects that I want them to be compared. Both in Schema and Data.Currently for comparer these two databases the wizard in visual studio generate two files one for project schema comparer and other for dataCompare this last with extension file dcmpAny suggestions or help? - Anonymous
January 04, 2018
These APIs are total garbage. Citing the schema compare dacpac being able to do a script generation/publish does not work against a target database. The reason? "error sql0: the operation could not continue because the source model contains errors". When I can use that same source model in visual studio to run compares, updates, publishes ect. Why to drop the ball on this one.