MSBuild support for Schema Compare is available

MSBuild support for Schema Compare is available

  • Comments 8

Schema compare is one of the most important Visual Studio SQL Server tooling components. As of our July release the schema compare functionality is available via MSBuild. It can be run from the command line or as an integrated part of automated project build systems to detect changes and generate reports.

Supported versions

  • SQL Server 2005, 2008, 2008 R2, 2012, 2014, and Microsoft Azure SQL Databases
  • Dacpac files targeting SQL Server 2005, 2008, 2008 R2, 2012, 2014, and Microsoft Azure SQL Databases

Installation and Usage

Environment Setup

Schema Comparison requires a supported version of Visual Studio. This version must include the latest version of the SQL Server tooling. Note that this is required in all cases, whether running on a local machine or on a build server.

Functionality

The core schema compare engine has been totally redesigned in this latest release. One benefit is improved functionality and configurability. MSBuild integration was at the top of our list for new functionality and we’re very happy to add this much-requested feature.

You can now embed schema compare into your daily build process and easily trace your schema changes. Databases and .dacpac files are supported and all the settings and options you are familiar with are included. Two report formats are supported in this version, plain text and XML. The report contains exactly the same information you would see in Visual Studio. You can even customize your report by providing your own XSD when generating an XML report.

Supported Features

Participant Type (Source/Target for Schema Comparison)

Supported?

Database

Yes

Dacpac

Yes

Project

No. The generated .dacpac file from a build must be used instead

 

Action

Supported?

Generate Text Report

Yes

Generate XML Report

Yes

Update Database

Yes

Generate DB Update script

No

Update Project

No (Projects not directly supported in this release)

Sample: Running Schema Compare from the command line

Note:

Because Schema Compare is run via MSBuild,  a valid project file is required. This can be a .sqlproj file, in which case all necessary targets will be defined, but can also be any project file as long as it imports the SSDT target file. If you are using a .sqlproj file remember that MSBuild will default to a Visual Studio version associated with the .NET Framework on your machine. If you have both Visual Studio 2012 and Visual Studio 2013 installed, running MSBuild from the command line will default to running the SQL Server Data Tools components installed inside Visual Studio 2012. To overcome this add /p:VisualStudioVersion=12.0 to your MSBuild statement if you wish to run using the SQL Server Data Tools components installed inside the VS2013 install directory. Here is the simplest possible project file you might need. Creating a “MinSchemaCompare.proj” file and copying this into the file allows you to run schema compare against any of the supported targets : 

<?xml version="1.0" encoding="utf-8"?> <Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0"> <Import Project="$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props" Condition="Exists('$(MSBuildExtensionsPath)\$(MSBuildToolsVersion)\Microsoft.Common.props')" /> <PropertyGroup> <!-- The version of the Visual Studio in which you installed SSDT July release. Related dlls will be loaded based on Visual Studio Version --> <VisualStudioVersion>12.0</VisualStudioVersion> </PropertyGroup> <!-- Replace the target file location --> <Import Project="$(SqlTaskTargetPath)\Microsoft.Data.Tools.Schema.SqlTasks.targets" /> </Project>

If you are not working on a SQL Project, make sure you set VisualStudioVersion and have Microsoft.Data.Tools.Schema.SqlTasks.targets imported in your project file. You can find it under %ProgramFiles(x86)%\MSBuild\Microsoft\VisualStudio\{VisualStudioVersion}\SSDT.

Here is what the command looks like:

msbuild "d:\sample.csproj" /t:SqlSchemaCompare /p:source="d:\source.dacpac" /p:target="d:\target.dacpac" /p:XmlOutput="d:\1.xml" /p:XsdPath="d:\SampleXsd.xsd"

This command line compares source and target .dacpac files and generates the comparison result to an XML file based on the XML schema provided.

If you don’t want to write long command line strings (especially when you are comparing databases), you can save a .scmp file and use it in the schema compare MSBuild task as follows

D:\SampleProject > msbuild /t:SqlSchemaCompare /p:SqlScmpFilePath="d:\sc.scmp" /p:XmlOutput="d:\1.xml" /p:Deploy="true"

Notice that schema compare uses default XSD file if the file is not specified.

The settings inside the .scmp file can be overwritten by specifying values from command line.

C:\SampleProject > msbuild /t:SqlSchemaCompare /p:SqlScmpFilePath="d:\sc.scmp" /p:target="d:\target.dacpac" /p:TextOutput="d:\1.out" /p:Deploy="true"

Schema Compare Parameters

Source and Target

Database: The input is the connection string

.dacpac file: The input is the file location.

Schema Compare Options

There are a lot of comparison and deployment settings. Please see the Default Settings section at the end for a list with default values. Here are how some of the buttons in the Schema Compare tool bar map to command line arguments.

 

Parameter

Supported?

Default Value

Note

clip_image005[4]

Deploy

Yes

False

.dacpac file is not deployable.

clip_image007[4]

GroupBy

Yes

Action

 
 

Timeout

Yes

5*60*1000 ms (5 minutes)

 
image ShowEqualObjects Will be supported in a future release    
image ShowUndeployableObjects Will be supported in a future release    

Output Format
  • Plain text output (Indicated by parameter TextOutput)

The default output format for each difference entry (DiffEntry) is: entry name, entry type, source value, update type, target value. To illustrate the output, let’s look at a difference in the Visual Studio UI and see how this will be output by the text formatter:

Here are the comparison result text format output corresponding to what we get from the UI.

clip_image012[4]

by action(Root): NotSpecified Change(Folder): Change Table(TopLevelElement): dbo.Table(Table) Change dbo.Table(Table) Columns(Folder): Delete Column(Element): Delete dbo.Table.c1 Properties(Folder): Delete Collation(Property): Delete null IdentityIncrement(Property): Delete 1 IdentityIsNotForReplication(Property): Delete False IdentitySeed(Property): Delete 1 IsFileStream(Property): Delete False IsIdentity(Property): Delete False IsMax(Property): Delete False IsNullable(Property): Delete True IsRowGuidColumn(Property): Delete False IsSparse(Property): Delete False Length(Property): Delete 0 Precision(Property): Delete 0 Scale(Property): Delete 0

When running from the command line it’s possible to generate extra information that isn't shown in the UI. This is configurable using the following command line parameters:

Parameter

Default Value

Note

OutputOrdering

False

If set to true, it shows OrderChanged, Ordinal, ContainsOrderChanged, SiblingOrderChanged in order

OutputRefactoring

False

If set to true, it shows Refactored and ChildRefactored in order

OutputInclusionState

False

If set to true, it shows InclusionState. This corresponds to the checkbox of each entry line in the UI which indicates whether this difference will be included in future deployments.

OnlyTopLevelItems

False

 

IgnorePropertyFileNameValue

False

If set to true, the value of property named “filename” will be set to the default fixed value “File_Name”. It is useful if the file name keeps changing for every compare operation and you want to ignore this difference.

If you choose to show all information of a DiffEntry, the sequence will be: Default output values, Ordering values, Refactoring values, Inclusion values.

  • XML file output (Indicated by parameter XmlOutput)

Unlike plain text format result which is clean but not easy to read, XML format is human readable and easier to manipulate. We provide a default XML schema file as well as Common Type XSD in case you wish to create your own custom report format.

 

Parameter

Note

Use default XSD

N/A

You don’t need to specify the XSD file

Use your own XSD

XsdPath

Indicates the path of your XSD file. Multiple paths are separated by semicolon.

clip_image014[4]
We pre-define a few types corresponding to each DiffEntry information, such as

The schema compare task populates the Inclusion State value when it finds an attribute or element associated with this type. You can find the Pre-defined types in %ProgramFiles(x86)%\MSBuild\Microsoft\VisualStudio\{version}\SSDT\Microsoft.Data.Tools.Tasks.SchemaCompare.CommonTypes.xsd .

At bottom of this file, there are four types you need to extend besides creating a ResultType element.

clip_image015[4]

You have the flexibility to create your own format but you also need to follow some rules. Results have a hierarchical layout format, whether in the UI or when output on the command line. This is something that must be preserved in any customized XSD you create – the XML also needs to have the same hierarchy, which means the root is on top followed by the group, with DiffEntry nested inside the group.

Here is a sample XSD:

<?xml version="1.0" encoding="utf-8"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://schemas.microsoft.com/SSDT/SqlTasks/SchemaCompare.xsd" xmlns="http://schemas.microsoft.com/SSDT/SqlTasks/SchemaCompare.xsd"> <!--Redefine ResultType, GroupType, ChildrenEntiesType, and DiffEntryType--> <xs:redefine schemaLocation="Microsoft.Data.Tools.Tasks.SchemaCompare.CommonTypes.xsd"> <xs:complexType name="DiffEntryType"> <xs:complexContent> <xs:extension base="DiffEntryType"> <xs:sequence> <xs:element name="MyResult"> <xs:complexType> <xs:sequence> <xs:element name ="MySource" type="SourceValueType"/> <xs:element name ="MyTarget" type="TargetValueType"/> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="MyOrderChanged" type ="OrderChangedType"/> <xs:element name="MyOrdinal" type ="OrdinalType"/> <xs:element name="MySiblingOrderChanged" type ="SiblingOrderChangedType"/> <xs:element name="MyRefactored" type ="RefactoredType"/> <xs:element name="MyChildRefactored" type ="ChildRefactoredType"/> <xs:element name="MyInclusionState" type ="InclusionStateType"/> <!-- Make sure ChildrenEntiesType element is nested inside a DiffEntryType element, otherwise it will not include all DiffEntry recursively. --> <xs:element name="MyChildren" type="ChildrenEntiesType"/> </xs:sequence> <xs:attribute name="MyUpdate" type="UpdateCategoryType"/> <xs:attribute name="MyName" type="DisplayNameType"/> <xs:attribute name="MyType" type="EntryTypeType"/> </xs:extension> </xs:complexContent> </xs:complexType> <xs:complexType name="ChildrenEntiesType"> <xs:complexContent> <xs:extension base="ChildrenEntiesType"> <xs:sequence> <xs:element name="MyEntry" type="DiffEntryType" minOccurs="0" maxOccurs="unbounded"/> </xs:sequence> </xs:extension> </xs:complexContent> </xs:complexType> <xs:complexType name="GroupType"> <xs:complexContent> <xs:extension base="GroupType"> <xs:sequence> <xs:element name="MyEntry" type="DiffEntryType" minOccurs="0" maxOccurs="unbounded"/> </xs:sequence> <xs:attribute name="Value" type="GroupByVauleType"/> </xs:extension> </xs:complexContent> </xs:complexType> <xs:complexType name="ResultType"> <xs:complexContent> <xs:extension base="ResultType"> <xs:sequence> <xs:element name="MyGroup" type="GroupType" minOccurs="0" maxOccurs="unbounded"/> </xs:sequence> <xs:attribute name="MyGroupBy" type="GroupByCategoryType"/> </xs:extension> </xs:complexContent> </xs:complexType> </xs:redefine> <xs:element name="Result" type="ResultType"/> </xs:schema>

The output looks like:

<Result MyGroupBy="by action"> <MyGroup Value="Change"> <MyEntry MyUpdate="Change" MyName="Table" MyType="TopLevelElement"> <MyResult> <MySource>dbo.Table</MySource> <MyTarget>dbo.Table</MyTarget> </MyResult> <MyOrderChanged>False</MyOrderChanged> <MyOrdinal>0</MyOrdinal> <MyContainsOrderChanged>False</MyContainsOrderChanged> <MySiblingOrderChanged>False</MySiblingOrderChanged> <MyRefactored>False</MyRefactored> <MyChildRefactored>False</MyChildRefactored> <MyInclusionState>Included</MyInclusionState> <MyChildren> <MyEntry MyUpdate="Delete" MyName="Columns" MyType="Folder"> <MyResult> <MySource /> <MyTarget /> </MyResult> <MyOrderChanged>False</MyOrderChanged> <MyOrdinal>2147483647</MyOrdinal> <MyContainsOrderChanged>False</MyContainsOrderChanged> <MySiblingOrderChanged>False</MySiblingOrderChanged> <MyRefactored>False</MyRefactored> <MyChildRefactored>False</MyChildRefactored> <MyInclusionState>None</MyInclusionState> <MyChildren> <MyEntry MyUpdate="Delete" MyName="Column" MyType="Element"> <MyResult> <MySource /> <MyTarget>dbo.Table.c1</MyTarget> </MyResult> . . . . </MyEntry> </MyChildren> </MyEntry> </MyChildren> </MyEntry> </MyGroup> </Result>
Deployment

If you want to deploy differences to the target, just use /p:Deploy=”true”. When you deploy from the UI you can choose which differences to deploy. This is also supported from the command line if the SelectedObjectsFilePath property is set. The SelectedObjectsFile looks like:

1 <?xml version="1.0" encoding="utf-8"?> 2 <root> 3 <!-- Include dbo.table1 --> 4 <Set Included="true"> 5 <SelectedItem Type="Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlTable, Microsoft.Data.Tools.Schema.Sql, Version=12.0.0.0 , Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"> 6 <Name>dbo</Name> 7 <Name>table1</Name> 8 </SelectedItem> 9 </Set> 10 11 <!-Toggle the inclusion status of dbo.table2 --> 12 <Toggle> 13 <SelectedItem Type="Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlTable, Microsoft.Data.Tools.Schema.Sql, Version=12.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"> 14 <Name>dbo</Name> 15 <Name>table2</Name> 16 </SelectedItem> 17 </Toggle> 18 19 </root> 20

 

 

Default Settings

AdditionalDeploymentContributorArguments

null

AdditionalDeploymentContributors

null

AllowDropBlockingAssemblies

FALSE

AllowIncompatiblePlatform

FALSE

BackupDatabaseBeforeChanges

FALSE

BlockOnPossibleDataLoss

TRUE

BlockWhenDriftDetected

FALSE

CommentOutSetVarDeclarations

FALSE

CompareUsingTargetCollation

FALSE

CreateNewDatabase

FALSE

DeployDatabaseInSingleUserMode

FALSE

DisableAndReenableDdlTriggers

TRUE

DoNotAlterChangeDataCaptureObjects

TRUE

DoNotAlterReplicatedObjects

TRUE

DropConstraintsNotInSource

TRUE

DropDmlTriggersNotInSource

TRUE

DropExtendedPropertiesNotInSource

TRUE

DropIndexesNotInSource

TRUE

DropObjectsNotInSource

TRUE

DropPermissionsNotInSource

FALSE

DropRoleMembersNotInSource

FALSE

DropStatisticsNotInSource

TRUE

ExcludedTypes

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlServerDdlTrigger"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlRoute"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlEventNotification"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlEndpoint"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlErrorMessage"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlFile"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlLogin"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlLinkedServer"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlCredential"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlDatabaseEncryptionKey"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlMasterKey"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlDatabaseAuditSpecification"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlServerAudit"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlServerAuditSpecification"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlCryptographicProvider"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlUserDefinedServerRole"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlEventSession"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlDatabaseOptions"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlLinkedServerLogin"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlServerRoleMembership"

 

"Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlAssemblyFile"

GenerateSmartDefaults

FALSE

IgnoreAnsiNulls

TRUE

IgnoreAuthorizer

FALSE

IgnoreColumnCollation

FALSE

IgnoreComments

FALSE

IgnoreCryptographicProviderFilePath

TRUE

IgnoreDdlTriggerOrder

FALSE

IgnoreDdlTriggerState

FALSE

IgnoreDefaultSchema

FALSE

IgnoreDmlTriggerOrder

FALSE

IgnoreDmlTriggerState

FALSE

IgnoreExtendedProperties

FALSE

IgnoreFileAndLogFilePath

TRUE

IgnoreFilegroupPlacement

TRUE

IgnoreFileSize

TRUE

IgnoreFillFactor

TRUE

IgnoreFullTextCatalogFilePath

TRUE

IgnoreIdentitySeed

FALSE

IgnoreIncrement

FALSE

IgnoreIndexOptions

FALSE

IgnoreIndexPadding

TRUE

IgnoreKeywordCasing

TRUE

IgnoreLockHintsOnIndexes

FALSE

IgnoreLoginSids

TRUE

IgnoreNotForReplication

FALSE

IgnoreObjectPlacementOnPartitionScheme

TRUE

IgnorePartitionSchemes

FALSE

IgnorePermissions

FALSE

IgnoreQuotedIdentifiers

TRUE

IgnoreRoleMembership

FALSE

IgnoreRouteLifetime

TRUE

IgnoreSemicolonBetweenStatements

TRUE

IgnoreTableOptions

FALSE

IgnoreUserSettingsObjects

FALSE

IgnoreWhitespace

TRUE

IgnoreWithNocheckOnCheckConstraints

FALSE

IgnoreWithNocheckOnForeignKeys

FALSE

IncludeCompositeObjects

FALSE

IncludeTransactionalScripts

FALSE

NoAlterStatementsToChangeCLRTypes

FALSE

PopulateFilesOnFileGroups

TRUE

RegisterDataTierApplication

FALSE

TargetDatabaseName

null

TreatVerificationErrorsAsWarnings

FALSE

UnmodifiableObjectWarnings

TRUE

VerifyCollationCompatibility

TRUE

VerifyDeployment

TRUE

There is the default XSD for XML output.
<?xml version="1.0" encoding="utf-8"?> <xs:schema targetNamespace="http://schemas.microsoft.com/SSDT/SqlTasks/SchemaCompare.xsd" xmlns="http://schemas.microsoft.com/SSDT/SqlTasks/SchemaCompare.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:simpleType name="SourceValueType"> <xs:restriction base="xs:string" /> </xs:simpleType> <xs:simpleType name="SourceValueWithoutSchemaType"> <xs:restriction base="xs:string" /> </xs:simpleType> <xs:simpleType name="TargetValueType"> <xs:restriction base="xs:string" /> </xs:simpleType> <xs:simpleType name="TargetValueWithoutSchemaType"> <xs:restriction base="xs:string" /> </xs:simpleType> <xs:simpleType name="UpdateCategoryType"> <xs:restriction base="xs:string" /> </xs:simpleType> <xs:simpleType name="DisplayNameType"> <xs:restriction base="xs:string" /> </xs:simpleType> <xs:simpleType name="EntryTypeType"> <xs:restriction base="xs:string" /> </xs:simpleType> <xs:simpleType name="OrderChangedType"> <xs:restriction base="xs:boolean" /> </xs:simpleType> <xs:simpleType name="OrdinalType"> <xs:restriction base="xs:integer" /> </xs:simpleType> <xs:simpleType name="ContainsOrderChangedType"> <xs:restriction base="xs:boolean" /> </xs:simpleType> <xs:simpleType name="SiblingOrderChangedType"> <xs:restriction base="xs:boolean" /> </xs:simpleType> <xs:simpleType name="RefactoredType"> <xs:restriction base="xs:boolean" /> </xs:simpleType> <xs:simpleType name="ChildRefactoredType"> <xs:restriction base="xs:boolean" /> </xs:simpleType> <xs:simpleType name="InclusionStateType"> <xs:restriction base="xs:string" /> </xs:simpleType> <xs:simpleType name="GroupByCategoryType"> <xs:restriction base="xs:string" /> </xs:simpleType> <xs:simpleType name="GroupByVauleType"> <xs:restriction base="xs:string" /> </xs:simpleType> <xs:complexType name="DiffEntryType"> <xs:sequence> <xs:element name="Source" type="SourceValueType"/> <xs:element name="Target" type="TargetValueType"/> <xs:element name="OrderChanged" type ="OrderChangedType"/> <xs:element name="Ordinal" type ="OrdinalType"/> <xs:element name="ContainsOrderChanged" type ="ContainsOrderChangedType"/> <xs:element name="SiblingOrderChanged" type ="SiblingOrderChangedType"/> <xs:element name="Refactored" type ="RefactoredType"/> <xs:element name="ChildRefactored" type ="ChildRefactoredType"/> <xs:element name="InclusionState" type ="InclusionStateType"/> <xs:element name="Children" type="ChildrenEntiesType"/> </xs:sequence> <xs:attribute name="Update" type="UpdateCategoryType"/> <xs:attribute name="Name" type="DisplayNameType"/> <xs:attribute name="Type" type="EntryTypeType"/> </xs:complexType> <xs:complexType name="ChildrenEntiesType"> <xs:sequence> <xs:element name="Entry" type="DiffEntryType" minOccurs="0" maxOccurs="unbounded"/> </xs:sequence> </xs:complexType> <xs:complexType name="GroupType"> <xs:sequence> <xs:element name="Entry" type="DiffEntryType" minOccurs="0" maxOccurs="unbounded"/> </xs:sequence> <xs:attribute name="Value" type="GroupByVauleType"/> </xs:complexType> <xs:complexType name="ResultType"> <xs:sequence> <xs:element name="Group" type="GroupType" minOccurs="0" maxOccurs="unbounded"/> </xs:sequence> <xs:attribute name="GroupBy" type="GroupByCategoryType"/> </xs:complexType> <xs:element name="Result" type="ResultType"/> </xs:schema>
Leave a Comment
  • Please add 8 and 5 and type the answer here:
  • Post
  • This looks like a very interesting feature but just curious as to why the lack of script generation support?

    In many cases it is useful to able to review the actual SQL to be executed before proceeding.

  • Excellent news, I can't wait to take a play.

    All we need now is support for SSIS and SSAS projects.... (hint hint)

  • Nice feature, although the VS dependency is still there :( We are still forced to have VS (SSDT) installed on our general build server. We have it now and use plain CMD Exec tasks to run silent builds with VS (for all BI project types). Anyway I'm really glad to see MSBuild is on your list. :)

  • This is brilliant. It's exactly the feature that I've been waiting for.

    I must however agree with Daniel: It would be very useful to just generate the SQL script and not apply the update automatically.

  • I am also voting for generate SQL script functionality. Would be very useful and allow to automatically generate change scripts on commit

  • Why can we not generate the reports from within Visual Studio when running a compare?  I think would be most useful to run a schema compare and review the results in a report format.  Being able to export the results for a team to review, seems like a no-brainer?

  • If you can update the database, why can't you generate the SQL script?

  • I'm not sure if I'm talking about something completely different, but schema compare has been able to generate migration scripts for over a year now?

Page 1 of 1 (8 items)