search

microsoft / azuredatastudio

Azure Data Studio is a data management and development tool with connectivity to popular cloud and on-premises databases. Azure Data Studio supports Windows, macOS, and Linux, with immediate capability to connect to Azure SQL and SQL Server. Browse the extension library for more database support options including MySQL, PostgreSQL, and MongoDB.
https://learn.microsoft.com/sql/azure-data-studio
MIT License
7.59k stars 909 forks source link

Publishing dacpac will fail if table has CHANGE_TRACKING ON #25592

Open GardarG opened 7 months ago

GardarG commented 7 months ago

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 (4970733324ef8254b7c22a5dc55af7f8a1dea93f, 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 commented 7 months ago

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

arkdvd commented 7 months ago

@GardarG : Things worked for me by setting below options in publish file & I no longer see _ALTER DB SET CHANGETRACKING = 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 commented 1 month ago

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 commented 1 month ago

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