{"id":804,"date":"2021-07-05T08:51:23","date_gmt":"2021-07-05T07:51:23","guid":{"rendered":"https:\/\/wp.bizoir.dk\/?p=804"},"modified":"2021-07-05T09:52:39","modified_gmt":"2021-07-05T08:52:39","slug":"azure-devops-dacpac-deployment-to-azure-sql-overwrites-database-properties","status":"publish","type":"post","link":"https:\/\/wp.bizoir.dk\/?p=804","title":{"rendered":"Azure Devops DACPAC deployment to Azure SQL overwrites database properties"},"content":{"rendered":"

We have a solution, where we use DACPAC deploy<\/a> in an Azure Devops pipeline to maintain the Azure SQL database.<\/p>\n

Apart from the normal issues (like adding not-null columns), this works ok.<\/p>\n

But recently, the Query Store<\/code> ran full, without SQL-server deleting old plans (as it was configured to)<\/p>\n

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

It turns out, it was the DACPAC deployment that overwrites this setting and constantly reverts it.<\/p>\n

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

Here all the default values can be found, including 6 for the Query Store<\/p>\n

It seems to be possible to avoid the DACPAC deployment to overwrite database-options as a whole.<\/p>\n

Unchecking the “Deploy database properties<\/code>” in the database project publish does not have any effect.<\/p>\n

But here<\/a>, they mention a “ScriptDatabaseOptions<\/code>” which can be set to false when using a programmatic deploy using “DacServices<\/code>“.<\/p>\n

Checking the SqlPackage options<\/a> (which can also be specified in an ‘Azure SQL Database deployment<\/code>‘ task), this property is found<\/p>\n

\/p: ScriptDatabaseOptions=(BOOLEAN 'True') Specifies whether target database properties should be set or updated as part of the publish action.\r\n<\/pre>\n
So, it should be enough to add this in the “Additional SqlPackage.exe Arguments<\/code>” box in Azure Devops<\/p>\n
\/p:ScriptDatabaseOptions=False<\/code><\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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” … Continue reading “Azure Devops DACPAC deployment to Azure SQL overwrites database properties”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[30,33],"class_list":["post-804","post","type-post","status-publish","format-standard","hentry","category-guides","tag-azure","tag-sql-server"],"_links":{"self":[{"href":"https:\/\/wp.bizoir.dk\/index.php?rest_route=\/wp\/v2\/posts\/804","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wp.bizoir.dk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wp.bizoir.dk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wp.bizoir.dk\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wp.bizoir.dk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=804"}],"version-history":[{"count":6,"href":"https:\/\/wp.bizoir.dk\/index.php?rest_route=\/wp\/v2\/posts\/804\/revisions"}],"predecessor-version":[{"id":810,"href":"https:\/\/wp.bizoir.dk\/index.php?rest_route=\/wp\/v2\/posts\/804\/revisions\/810"}],"wp:attachment":[{"href":"https:\/\/wp.bizoir.dk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=804"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wp.bizoir.dk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=804"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wp.bizoir.dk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=804"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}