SSDT/SqlPackage drops statistics

问题:

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.

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>

How can we force sqlpackage not to drop the statistics?

回答1:

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.

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)"