Azure Devops DACPAC deployment to Azure SQL overwrites database properties

We have a solution, where we use DACPAC deploy in an Azure Devops pipeline to maintain the Azure SQL database.

Apart from the normal issues (like adding not-null columns), this works ok.

But recently, the Query Store ran full, without SQL-server deleting old plans (as it was configured to)

I therefore increased the “Max Size” of the Query Store which solved the problem, but some time later, the “Max Size” value was reverted back to its default value (a measly 100MB)

It turns out, it was the DACPAC deployment that overwrites this setting and constantly reverts it.

The default value is specified in the database project settings in the VS project – under “Project settings” / “Database Settings” / “Optional”.

Here all the default values can be found, including 6 for the Query Store

It seems to be possible to avoid the DACPAC deployment to overwrite database-options as a whole.

Unchecking the “Deploy database properties” in the database project publish does not have any effect.

But here, they mention a “ScriptDatabaseOptions” which can be set to false when using a programmatic deploy using “DacServices“.

Checking the SqlPackage options (which can also be specified in an ‘Azure SQL Database deployment‘ task), this property is found

/p: ScriptDatabaseOptions=(BOOLEAN 'True') Specifies whether target database properties should be set or updated as part of the publish action.

So, it should be enough to add this in the “Additional SqlPackage.exe Arguments” box in Azure Devops


To me, it does not make sense to have the deployment-task overwrite these kinds of settings – at least you would then need to plan your deployments appropriately – expecially “emergency” like deployments to update values.

One thought on “Azure Devops DACPAC deployment to Azure SQL overwrites database properties”

  1. Update:
    Even though the ‘ScriptDatabaseOptions’ parameter to SqlPackage is by default set to true, the Azure Devops release task did not update the query store settings on the database (that was specified in the project file)
    Very strange, when it has been seen earlier, that this Devops release task gladly updated the query store settings back to their default values….. (when they were not specified in the project file)

    The reason for the query store settings not being deployed, could seem to be that the model.xml in the DACPAC file does not contain a “SqlDatabaseOptions” element like this

    <Element Type=”SqlDatabaseOptions”>

    If i build the same solution in VS 2019, the resulting .dacpac DOES contain the specified section. However, when built in Azure Devops, using VS2017, it does not.

    The reason it keeps overwriting with default values when publishing the DACPAC is unknown (but must be some default – perhaps because of the missing “SqlDatabaseOptions”)

    I tried a lot of building in Azure Devops
    Bottom line. I cannot make my test-pipeline output the (logically) identical dacpac that out current pipeline does.
    However I tweak the deploy, the resulting DACPAC (built on SAME project/same git commit) will always include a “SqlDatabaseOptions” in the model.xml file.
    This is confusing. Could it be because the current pipeline does not rebuild from scratch?

    However, the content of the “SqlDatabaseOptions” differs between how i build it in Devops.
    Whenever the database project ends up being built with VS2017, the “SqlDatabaseOptions” will not contain any “QueryStore…” properties.
    But when the database project ends up being built with VS2019 (by choice or fallback), the “SqlDatabaseOptions” DO contain (some) “QueryStore…” properties.

    The logical conclusion is that VS2017 (SSDT?) does not know of query store.
    The remaining difference is a puzzle.

    When deploying the dacpac produced by a VS2019 build, the query store settings were updated on the database without issue and the deployment-log also contained this line
    2021-08-23T12:05:23.3129236Z ‘QUERY_STORE=O***’ is not supported in this version of SQL Server.
    showing that on Azure SQL, Query store cannot be disabled.

    It does not log the query store parameters as being updated (but I can see that they were updated on the database itself)

Leave a Reply

Your email address will not be published. Required fields are marked *