如何使用msbuild来完成DB project的SchemaCompare的运行

近日，苦于不断需要使用schema compare来更新数据库的繁冗操作，决定潜心学习下如何使用msbuild来完成SchemaCompare的运行，初学版本为：

1，首先构建你的Database Project，会生成dacpac文件

2，再使用手动模式进行SchemaCompare在VS中的操作，保存此操作，生成scmp文件

3，运行命令：msbuild E:\SolutionFolder\DatabaseProjFolder\Database.sqlproj /t:SqlSchemaCompare /p:target="dacpac file path" /p:SqlScmpFilePath="e:\DB.scmp" /p:XmlOutput="d:\1.xml" /p:Deploy="true"

出现错误：SchemaCompare error: The source participant is invalid or empty

Google答案，有些说需要增加VS版本号：/p:VisualStudioVersion=15.0

更改之后：msbuild E:\SolutionFolder\DatabaseProjFolder\Database.sqlproj /t:SqlSchemaCompare /p:target="dacpac file path" /p:SqlScmpFilePath="e:\DB.scmp" /p:XmlOutput="d:\1.xml" /p:Deploy="true" /p:VisualStudioVersion=15.0

运行出现同样的错误。

辗转之后，发现问题在于souce，而一直在纠结target，更改如下命令：

msbuild E:\SolutionFolder\DatabaseProjFolder\Database.sqlproj /t:SqlSchemaCompare /p:source="dacpac file path" /p:SqlScmpFilePath="e:\DB.scmp" /p:XmlOutput="d:\1.xml" /p:Deploy="true" /p:VisualStudioVersion=15.0

运行成功，并且直接更新上去，如下为解释：

/p:source：定义对比的source源

/p:SqlScmpFilePath="e:\DB.scmp"：定义对比的scmp文件，其中文件定义已经包含了target的定义，此例中为DB的ConnectionString

/p:Deploy="true" ：直接将差异更新到target的数据库中

至此，确认SqlSchemaCompare的命令可以正常在msbuild端运行并且能更新数据库。

此后需要探索的问题为：如何参数化使对比结果能排除登陆用户对比。

 

Error: SchemaCompare error: Login failed for user 'sa'

Solution: 更改scmp文件里的connection为在VS里使用的带有user id和password的

Error: The source participant <.dacpac> is invalid.

Solution: 更改/p:source为绝对路径而非相对路径。

Error: The schema update is terminating because data loss might occur

Solution: 更改SCMP中配置项BlockOnPossibleDataLoss值为False, UI配置如下

Error: The database principal owns a schema in the database, and cannot be dropped.

Solution: 在SCMP中增加

      <PropertyElementName>
        <Name>Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlUser</Name>
        <Value>ExcludedType</Value>
      </PropertyElementName>

Error: Missing values for the following SqlCmd variables:customvariable

Solution: 搜寻多遍，得出的结论为：msbuild在schemacompare后会执行sqlcmd，而因为db project中有db reference到另一个数据库，需要将名称作为参数传递给sqlcmd。msbuild无法制定此参数，答案都为转战sqlpackage.

sqlpackage

基本命令

SqlPackage.exe /Action:Publish /SourceFile:generated.dacpac /TargetServerName:TestServer\TestInstance /TargetDatabaseName:TestDB /Variables:varname=varvalue /P:BlockOnPossibleDataLoss=False

Error: Windows NT user or group '' not found. Check the name again.

Solution: 加上设置项/P:ExcludeObjectTypes=RoleMembership;Users;Logins

Error: Cannot find the user '', because it does not exist or you do not have permission. in statement "grant execute..."

Solution: 加上设置项/P:IgnorePermissions=True

Error:

Unable to connect to master or target server 'TestDB2'. You must have a user with the same password in master or target server 'TestDB2'.

Solution: 加上设置项/TargetUser:sa /TargetPassword:sapwd

最终命令：

SqlPackage.exe /Action:Publish /SourceFile:generated.dacpac /TargetServerName:TestServer\TestInstance /TargetDatabaseName:TestDB /TargetUser:sa /TargetPassword:sapwd /Variables:varname=varvalue /P:BlockOnPossibleDataLoss=False /P:ExcludeObjectTypes=RoleMembership;Users;Logins;DatabaseRoles /P:IgnorePermissions=True

 

【参考】

1, https://blogs.msdn.microsoft.com/ssdt/2014/07/15/msbuild-support-for-schema-compare-is-available/

2, https://social.msdn.microsoft.com/forums/sqlserver/en-US/302587bf-ff9c-4f22-94dc-a96587f6b304/how-do-we-perform-sqlcmd-variable-substitution-at-deploytime

3, https://stackoverflow.com/questions/21330938/exclude-ojects-object-types-from-sqlpackage-is-it-possible

4, https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-2017

5, http://sqlblog.com/blogs/jamie_thomson/archive/2012/12/10/editing-sqlcmdvariable-nodes-in-ssdt-publish-profile-files-using-msbuild.aspx

6, https://stackoverflow.com/questions/10897316/how-to-make-visual-studio-schema-comparison-igonore-database-references

7, http://sqlblog.com/blogs/jamie_thomson/archive/2012/02/12/sql-server-data-tools-does-support-required-variables.aspx

8, https://docs.microsoft.com/en-us/sql/ssms/scripting/sqlcmd-use-with-scripting-variables?view=sql-server-2017