SSDT/SqlPackage 丢弃统计信息

问题描述

在构建期间，我们基于 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 EdElliott 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 丢弃统计信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！