Continuous integration of database development-SQL Server database structure comparison

This series of articles
Continuous integration of database development-SQL Server deployment and upgrade Tool
Continuous integration of database development-SQL Server database structure comparison
Continuous integration of database development-Methods and Procedures
Continuous integration of database development-Liquibase introduction and application
Continuous integration of databases-project configuration of CruiseControl. Net

Last time, I talked about the general intention of continuous integration in database development, provided a database deployment and upgrade tool, and then discussed how to compare databases.

In my development, database comparison tools mainly need two forms: desktop tools and MsBuid tasks.

Open-source DaBCoS3 is recommended for desktop tools, which is basically enough. A friend recommends using VS2005 directly, but I prefer something smaller, because it is not only used during database development.

The MsBuild task is very important and can be used to verify whether the upgrade script works normally during continuous integration. Add the SqlDeply task (MsBuild) to CC. Net to upgrade the copy of the product system database, and then use the MsBuild database to compare the structure of the upgraded task with that of the development database. To verify the upgrade script, it is equivalent to automatic testing of the upgrade script (and regression testing, of course), which can greatly save manpower.
Search for a large circle on the Internet and find that the Assembly provided by Red Gate is good, but $ is required. Give up. After a large circle, return to M $, and PowerTools is selected. You must first install Team Edition for Database.

MsBuild tasks for Data Structure comparison are as follows:

<Project DefaultTargets = "SchemaCompare" xmlns = "http://schemas.microsoft.com/developer/msbuild/2003">
<UsingTask TaskName = "SqlSchemaCompareTask" AssemblyName = "Microsoft. VisualStudio. TeamSystem. Data. PowerTools. Tasks, Version = 2.0.0.0, Culture = neutral, PublicKeyToken = token"/>

<PropertyGroup>
<UpdateSql/>
</PropertyGroup>

<Target Name = "SchemaCompare">
<SqlSchemaCompareTask
SourceConnectionString = "server = product; user id = sa; password = mypass"
SourceDatabaseName = "Northwind"
TargetConnectionString = "Data Source =.; Integrated Security = True; Pooling = False"
TargetDatabaseName = "Northwind"
OutputPath = "."
OutputFileName = "update. SQL"

ForceColumnOrder = "true"
IgnoreExtendedProperties = "true"
IgnoreStatistics = "true"
IgnoreConstraintNames = "true"
IgnoreQuotedIdentifiersAndAnsiNullSettings = "true"
IgnoreTriggerOrder = "true"
IgnoreUsers = "true"
IgnoreWhiteSpace = "true"

DoNotOutputCommentHeader = "true"
NoTransactionalChangeScript = "true"
SkipSETStatements = "true"
ScriptCollationWhenDifferentFromDefault = "true"
/>

<ReadLinesFromFile File = "update. SQL">
<Output TaskParameter = "Lines" PropertyName = "UpdateSql"/>
</ReadLinesFromFile>

<! -- NOTE: If update. SQL is emtpy, the two databases are same -->
<Message Text = "$ (UpdateSql)" Condition = "'$ (UpdateSql )'! = ''"/>
<Error Text = "Soure database is different from the target" Condition = "'$ (UpdateSql )'! = ''"/>
</Target>
</Project>

Note: In this example, the attributes of SqlSchemaComapreTask have been optimized to ensure that the update. SQL statements generated simultaneously in the database are empty and to determine whether the two databases are equal. This task is used to generate the upgrade script. Here, it is only used to compare whether the two databases have the same structure.

MsBuild task for database data comparison example: <Project DefaultTargets = "DataCompare" xmlns = "http://schemas.microsoft.com/developer/msbuild/2003">
<UsingTask TaskName = "SqlDataCompareTask" AssemblyName = "Microsoft. VisualStudio. TeamSystem. Data. PowerTools. Tasks, Version = 2.0.0.0, Culture = neutral, PublicKeyToken = b03f5f7f11d50a3a"/>

<Target Name = "DataCompare">
<SqlDataCompareTask
SourceConnectionString = "server = product; user id = sa; password = mypass"
SourceDatabaseName = "Northwind"
TargetConnectionString = "Data Source =.; Integrated Security = True; Pooling = False"
TargetDatabaseName = "Northwind"
OutputPath = "."
OutputFileName = "TestDataCompare. SQL"

TrimTrailingSpaces = "true"

DisableTriggers = "true"
DisableKeys = "false"
DoNotOutputCommentHeader = "true"
DoNotUseTransactions = "true"/>
</Target>
</Project>

Update 20060617
Finally, I chose liquibase under Java as a tool for database comparison, upgrade, and deployment. It is already applied to the database continuous integration development project (see methods and procedures, the only defect is that it is not supported yet. net