SSDT/SqlPackage drops statistics

Question

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?

Hutt

The actual verification is to check the generated script, not the parameters passed to it. What does the script do? Is DropObjectsNotInSource intentionally set to True? – Peremptory

Are you running the latest version of sqlpackage.exe (dacfx) on the deployment server? – Abrasive

@PanagiotisKanavos Yes we actually want to drop tables, columns and stored procedures in our database I'm running VS2015 with the latest SSDT installed and also tried VS2017. Both produce the same migration script ( with DROP STATISTICS xyz.abc) – Hutt

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

Nebuchadnezzar