Publishing dacpac will fail if table has CHANGE_TRACKING ON

[GardarG]

Type: Bug

Steps to Reproduce:

1. Create a database with CHANGE_TRACKING enabled and a table with CHANGE_TRACKING enabled
2. Publish the same database without changes and it will fail.

When looking further into the sqlcmd script that is created, we see that the following code is run BEFORE pre-deployment scripts:

IF EXISTS (SELECT 1
FROM [sys].[databases]
WHERE [name] = N'$(DatabaseName)')
BEGIN
ALTER DATABASE [$(DatabaseName)]
SET CHANGE_TRACKING = OFF
WITH ROLLBACK IMMEDIATE;
END
GO

As such it is impossible to have change-tracking on a table when publishing. There should be settings in the .publish.xml file to comment out / disable these lines of code.

Extension version: 1.4.2
Azure Data Studio version: azuredatastudio 1.48.0 (4970733, 2024-02-27T00:05:08.293Z)
OS version: Windows_NT x64 10.0.19045
Restricted Mode: No
Preview Features: Enabled
Modes:

System Info

Item	Value
CPUs	Intel(R) Core(TM) i7-8750H CPU @ 2.20GHz (12 x 2208)
GPU Status	2d_canvas: enabled canvas_oop_rasterization: enabled_on direct_rendering_display_compositor: disabled_off_ok gpu_compositing: enabled multiple_raster_threads: enabled_on opengl: enabled_on rasterization: enabled raw_draw: disabled_off_ok video_decode: enabled video_encode: enabled vulkan: disabled_off webgl: enabled webgl2: enabled webgpu: enabled
Load (avg)	undefined
Memory (System)	15.74GB (2.20GB free)
Process Argv
Screen Reader	no
VM	0%

[arkdvd]

Trying to ask if is this addressed ?. I ran into same issue

[arkdvd]

@GardarG : Things worked for me by setting below options in publish file & I no longer see ALTER DB SET CHANGE_TRACKING = OFF WITH ROLLBACK IMMEDIATE;

DoNotAlterChangeDataCaptureObjects = False
DoNotAlterReplicatedObjects = False
ScriptDatabaseOptions = False
IgnoreTableOptions = False

However I still see a comment in deployment script with PRINT N'Altering table [user_schemaname].[user_tablename]...;' for the table with CDC enabled in my database. But no actual code beneath the comment. Not sure if this is bug or anything to do with my setting.

Please let me know if you see the same on your end.

[gupta-harshy]

I am facing the same problems and am not sure how to solve them. I got the first 2 options DoNotAlterChangeDataCaptureObjects = False, DoNotAlterReplicatedObjects = False
However for the last 2 still struggling.
Please tell if there is any solution for it.

[gupta-harshy]

I found one solution where you can enable change detection on the database level. This would change the initial script and allow the change detection.
Project Settings -> Database Settings -> Operational

[Sovexe]

For any future google searchers that end up here

Note the following from https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/concepts/project-properties?view=sql-server-ver17

If you are deploying your DACPAC to a database with change tracking enabled, you must include the property entry below. Otherwise, sqlpackage.exe will assume that change tracking is disabled and will try to turn it off during deployment (as described in the original issue). If you have any tables using change tracking, this will cause deployment to fail. To keep change tracking enabled, add the following property:

<IsChangeTrackingOn>True</IsChangeTrackingOn>

example implementation....

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="1.0.0-rc1" />
  <PropertyGroup>
    <Name>AdventureWorks</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <ProjectGuid>{00000000-0000-0000-0000-000000000000}</ProjectGuid>
    <RunSqlCodeAnalysis>true</RunSqlCodeAnalysis>
    <CompatibilityMode>130</CompatibilityMode>
    <IsChangeTrackingOn>True</IsChangeTrackingOn>
  </PropertyGroup>
  <PropertyGroup Condition="'$(Configuration)'=='Release'">
    <TreatTSqlWarningsAsErrors>True</TreatTSqlWarningsAsErrors>
  </PropertyGroup>
</Project>