3

I have a database project in Visual Studio 2017. Our database project is managed just like any other library of code where multiple developers can update the project as necessary. To ease the pain of deployments, I have built a custom deployment task in our TFS 2018 (vNext) Build process that is a powershell script that calls sqlPackage.exe. SqlPackage compares our compiled database project (*.dacpac file) to our target database (in Dev, QA, etc.). I have the custom step configured so that it will write the expected changes to disk so I have a record of what was changed, then sqlPackage runs a second pass to apply the changes to the intended target database.

My DBA enabled the Query Store in our SQL 2016 Database. During my sqlPackage deployment, one of the initial steps is to turn the query store off, this makes my DBA unhappy. He wants the ability to compare pre and post deployment changes but if the query store gets turned off, we lose the history.

I have tried several of the switches in the documentation (https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx#Publish%20Parameters,%20Properties,%20and%20SQLCMD%20Variables) but I can't seem to find the magic parameter.

How do I stop SqlPackage from turning off the query store?

My current script:

sqlPackage.exe /Action:Script /SourceFile: myPath\MyDatabaseName.dacpac" /OutputPath:"myPath\TheseAreMyChangesThatWillBeApplied.sql" /TargetConnectionString:"Integrated Security=true;server=MyServer;database=MyDatabase;" /p:DropObjectsNotInSource=false /p:DropPermissionsNotInSource=false /p:DropRoleMembersNotInSource=false /p:BlockOnPossibleDataLoss=True /Variables:"CrossDatabaseRefs=CrossDatabaseRefs

Is there a better way? I am surprised that I had to write a custom TFS Build Task to do this. Which makes me think that I might be doing it the hard way. (But this has worked pretty well for me for the last several years). I love database projects, I love that they enforce references and ensure that we don't break other objects when a column is dropped (for instance).

Any insight would be greatly appreciated.

grizzthedj
  • 7,131
  • 16
  • 42
  • 62
J. Erik Thompson
  • 95
  • 2
  • 7
  • You may check this extension: https://marketplace.visualstudio.com/items?itemName=TamasTIPost.SSISCompareMergeTool-18170 – Cece Dong - MSFT May 11 '18 at 09:58
  • That extension is to compare SSIS packages. I am deploying a database project to a database. SqlPackage.exe generates and executes all the necessary alter/drop/create scripts necessary to make the target database have the same schema of the database project. – J. Erik Thompson May 12 '18 at 13:23

2 Answers2

7

Either disable the scripting of database properties using /p:ScriptDatabaseOptions=false, or update the database properties in the project to reflect the desired Query Store settings.

To set the Query Store settings in the project, right-click the database project in Solution Explorer and open Properties. From there, in the Project Settings tab find the "Database Settings..." button. In the Database Settings dialog, click the Operational tab and scroll down to find the Query Store settings.

Steven Green
  • 3,387
  • 14
  • 17
1

Apparently, all we needed to do was add a post deployment script to re-enable the Query Store. Hope this helps someone out there...

USE Master ALTER DATABASE [MyDbName] SET QUERY_STORE = ON

J. Erik Thompson
  • 95
  • 2
  • 7