Sql-server – Visual Studio SSDT Ignore Query Store Options in Publish Profile

query-storesql serversql-server-2016ssdtvisual studio

Is there any way in Visual Studio SSDT Publish Profile, to Totally Ignore Query Store Options Completely?

It keeps asking us to Rerun publish code below. I go to Database advanced settings, and turn it off. Even after its set off, it keeps asking to be in publish profile. Is there any way to Just Ignore Query Store in Publish profile, rather than trying to just turn it off?

I am looking through advanced options in publish profile.

enter image description here

BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET QUERY_STORE (QUERY_CAPTURE_MODE = NONE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367)) 
        WITH ROLLBACK IMMEDIATE;
END

Best Answer

There does not seem to be a way to ignore individual database properties. However, you can ignore them all by adding the following to your publish profile XML file:

<ScriptDatabaseOptions>False</ScriptDatabaseOptions>

I have tested this and it does indeed leave out the QUERY_STORE statements.

Then, if there are any database-level properties that you do want set, you can add them back in manually via a Post Deploy SQL script that is added to the project and tagged as being PostDeploy in the file properties, under the "Build Action" property drop-down.

You can also add that XML element to your .sqlproj file or your .sqlproj.user file.

This option needs to go within a <PropertyGroup>, regardless of which of the three files you put it in. The order of them items within the <PropertyGroup>...</PropertyGroup> don't matter.

Another option, if you are merely generating the "Create" or "Publish" SQL script and are not deploying it immediately, is to use a utility to find and replace the two SET QUERY_STORE... lines with something like:

SET {some other DB property} = {its current setting}; --

For example:

SET TRUSTWORTHY OFF; --

Which should result in those two lines being turned into:

SET TRUSTWORTHY OFF; -- (QUERY_CAPTURE_MODE = NONE, ...

You might want to look into the following dos-based RegEx processor:

JREPL.BAT - regex text processor with Unicode and XRegExp support

Related Question