SSDT/SqlPackage 丢弃统计信息 [英] SSDT/SqlPackage drops statistics

问题描述

在构建期间，我们基于 SSDT .sqlproject 生成数据库的 dacpac 文件.此 dacpac 稍后会使用 sqlpackage 部署到生产环境中.尽管使用/p:DropStatisticsNotInSource=False 开关，sqlpackage 将删除所有统计信息，这些统计信息是在 sqlproject 与生产数据库的最后一次同步之后添加的.

During build we generate dacpac files of our database based on a SSDT .sqlproject. This dacpac later gets deployed to production using sqlpackage. Despite using the /p:DropStatisticsNotInSource=False switch, sqlpackage will drop all statistics, that were added after the last sync of the sqlproject with the production database.

我们还可以使用发布配置文件和 SSDT 的生成脚本选项来重现这一点:

We can also reproduce this using a publish profile and the generate script option of SSDT:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>hotel</TargetDatabaseName>
    <DeployScriptFileName>Database.sql</DeployScriptFileName>
    <TargetConnectionString>connectionstring</TargetConnectionString>
    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
    <DropObjectsNotInSource>True</DropObjectsNotInSource>
    <DoNotDropDatabaseRoles>True</DoNotDropDatabaseRoles>
    <DoNotDropDatabaseScopedCredentials>True</DoNotDropDatabaseScopedCredentials>
    <DoNotDropUsers>True</DoNotDropUsers>   
    <DoNotDropServerRoles>True</DoNotDropServerRoles>
    <DoNotDropSecurityPolicies>True</DoNotDropSecurityPolicies>
    <DoNotDropSearchPropertyLists>True</DoNotDropSearchPropertyLists>    
    <DoNotDropPermissions>True</DoNotDropPermissions>
    <DoNotDropPartitionSchemes>True</DoNotDropPartitionSchemes>
    <DoNotDropPartitionFunctions>True</DoNotDropPartitionFunctions>
    <DoNotDropExternalFileFormats>True</DoNotDropExternalFileFormats>
    <DoNotDropExternalTables>True</DoNotDropExternalTables>
    <DoNotDropErrorMessages>True</DoNotDropErrorMessages>
    <DoNotDropDefaults>False</DoNotDropDefaults>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <DropStatisticsNotInSource>False</DropStatisticsNotInSource>
    <ScriptRefreshModule>False</ScriptRefreshModule>
  </PropertyGroup>
</Project>

如何强制sqlpackage不丢弃统计信息?

How can we force sqlpackage not to drop the statistics?

推荐答案

问题是使用了DropObjectsNotInSource=True，它覆盖了DropStatisticsNotInSource=False 选项.这是一个错误或未在 sqlpackage.exe 文档中指定.

The problem is the use of DropObjectsNotInSource=True, it overwrites the DropStatisticsNotInSource=False option. This is either a bug or is not specified on the sqlpackage.exe documentation.

一种可能的解决方法是使用 Ed 的 AgileSqlClub SSDT 过滤器Elliott 如本博客 中所述.在这种情况下，您需要使用 AgileSqlClub.SqlPackageFilter.dll 并添加以下选项:

One possible workaround is to use AgileSqlClub SSDT filter by Ed Elliott as explained in this blog. In this case you would need to use the AgileSqlClub.SqlPackageFilter.dll and add following option:

/p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor/p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreType(Statistics)"

这篇关于SSDT/SqlPackage 丢弃统计信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！