Add support for "Prevent saving changes that require table re-creation" option in SqlPackage / SSDT

[EitanBlumin]

In SQL Server Management Studio, there's a designer option called "Prevent saving changes that require table re-creation".

It would be extremely useful to have a similar option in SqlPackage.exe as well, as it could stop dangerous deployments that could potentially re-create very large tables and severely impact production environments while doing so.

NOTE: This is a re-submission of the Azure feedback item:

https://feedback.azure.com/forums/908035-sql-server/suggestions/43852278-add-support-for-prevent-saving-changes-that-requi

[Charles-Gagnon]

That link seems to be invalid, is it cut off at the end?

It sounds like this is a feature request for SqlPackage/SSDT though, not something specifically in ADS. Is that the case?

@udeeshagautam @kisantia @Benjin

[EitanBlumin]

Hi Charles.

Yes you're right, the link is invalid. But not because it's cut off, but because the SQL server Azure feedback site was taken down. It can be recovered using the wayback machine in text only mode.

You're also correct that this is a feature request for ssdt/sqlpackage and you're right to ask me why I'm posting it here. Which brings me back to the answer above: the SQL server Azure feedback site was taken down. But what's worse is that a clear alternative was not provided.

I was directed here from Microsoft docs page about SQL server feedback.

[brettpostin]

We are currently tackling this problem and would welcome help to explicitly prevent table rebuilds by sqlpackage.

Our situation is that we have Change Tracking enabled on a number of tables. However when a table rebuild occurs, all of the changetable data is lost. This means that data does not get synchronised through our data integrations and requires costly reconciliation.

It seems sqlpackage/ssdt is very dangerous / incompatible when it comes to Change Tracking.

We would like to prevent table rebuilds at all cost. An explicit option would help prevent costly changes at development time and ultimately deployment.

[brettpostin]

Alternatively, support for #31 would be even better.

[ssreerama]

Have done some investigation and this will be a future enhancement. Will make this addition in our future releases depends on the priority of tasks and this can't be for all scenarios of table re-building. For now we considered one scenario is changeTracking and we can do that first and add any support for other scenarios in coming future! Thanks

[EitanBlumin]

I wonder why the hesitation with this feature, as it is already implemented and built-in in SSMS natively. Why not use the same code base?

BTW Azure Feedback is back, so I re-submitted the item here:

https://feedback.azure.com/d365community/idea/0ce9a177-1b47-ec11-a819-0022484bf651

[dzsquared]

@EitanBlumin - we're investigating this feature for dacfx. The functionality you're referencing in SSMS is relative to the table designer, which doesn't leveraging DacFx so the code isn't portable to this project.

[EitanBlumin]

@EitanBlumin - we're investigating this feature for dacfx. The functionality you're referencing in SSMS is relative to the table designer, which doesn't leveraging DacFx so the code isn't portable to this project.

It "isn't portable"? Are both projects not written in C# behind the scenes? Is there no access to the source code?

Do you mean by this that the other features in SSMS are leveraging DacFx instead of its own code?

Sorry, I just don't see why would this be a problem if you have access to the SSMS source code. I mean, regardless of whether it's leveraging DacFx or not, wouldn't it be useful as a good "starting point"?

[EitanBlumin]

Hi @dzsquared , any news on this?

[dzsquared]

Hi @EitanBlumin and others - doing a bit of further exploration on this feature - interested in feedback if the below proposal satisfies your scenarios:

• for publish operations, an additional property /p:AllowTableRecreation can be set to false (defaults to true). When disabled, SqlPackage will block the publish operation from proceeding if a deployment plan is calculated that includes any table re-creation.
• when /p:AllowTableRecreation is set to false and the publish operation is blocked by this check, an error message is output that references the table schema.name for any table that had a recreation detected

Acknowledging that the change tracking scenario (#31) is only helped by adding this option, but not safely solved entirely if the parameter isn't added.

[EitanBlumin]

@dzsquared , this /p:AllowTableRecreation parameter is not listed in the official Microsoft documentation:

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish?view=sql-server-ver16#parameters-for-the-publish-action

Is this a new feature that wasn't released yet? Or it's simply undocumented?

[ErikEJ]

@EitanBlumin It is a proposal:

if the below proposal satisfies your scenarios

And I think it is a good one...

[EitanBlumin]

Oh! Yes, of course! 😅

Thank you, @ErikEJ for the clarification.

Sorry, @dzsquared , yeah the proposal sounds good to me.

Alternatively, for the sake of consistency, perhaps it would be preferable to make it similar to the "Block..." parameters such as BlockOnPossibleDataLoss and BlockWhenDriftDetected.

So, something like /p:BlockOnTableRecreation, maybe?

[ErikEJ]

• for publish operations, an additional property /p:BlockOnTableRecreation can be set to true (defaults to false). When enabled, SqlPackage will block the publish operation from proceeding if a deployment plan is calculated that includes any table re-creation.

• when /p:BlockOnTableRecreation is set to true and the publish operation is blocked by this check, an error message is output that references the table schema.name for any table that had a recreation detected

[brettpostin]

This would be a very welcome enhancement! Whilst fixing the other issue surrounding change tracking would be ideal, this would at least allow us to catch potential issues at dev time.

[dzsquared]

@ErikEJ - the antithesis properties (BlockOnTableRecreation) with the default false absolutely makes sense as well - we have a good number of properties that default to false at this point. The general inclination is that the boolean properties default to true, but the clarity from the property name is likely.

@brettpostin - you mention "at dev time" - if this is a publish property on sqlpackage the dev interactions would probably be in CI tests, such as on a PR. Do you already have CI tests setup, or do you have an alternative route to using sqlpackage publish during dev processes?

[brettpostin]

We have separate publish profiles for dev and prod. We would likely add this parameter to block on table recreation to both profiles.

Developers deploy to their local db using SSDT + dev publish profile in VS.

I'd imagine developers hitting any issues locally during development, and make necessary adjustments. The production profile would block when deploying to production as a safety net.

[ErikEJ]

@llali @dzsquared Small typo in the help text from sqlpackage:

/p:AllowTableRecreation=(BOOLEAN 'True')
     Specifies wheather to allow the table recreation in Publish Action

should be:

/p:AllowTableRecreation=(BOOLEAN 'True')
     Specifies whether to allow the table recreation in Publish Action

[dzsquared]

@ErikEJ - eesh good catch, that's not the right string even without the typo

[brettpostin]

Has this been released?

[dzsquared]

@brettpostin - yes, this was released with 162.1

add the property /p:AllowTableRecreation=false to a publish command

more info: https://learn.microsoft.com/en-us/sql/tools/sqlpackage/release-notes-sqlpackage?view=sql-server-ver16#1621167-sqlpackage

[brettpostin]

Great thanks! I have tested this on the latest version of SqlPackage (162.1.167.1) and it works as expected.

However SSDT built into latest Visual Studio 2022 (17.7.7) currently seems to bundle SqlPackage (16.0.9021.0) which seems really old?

As there is no standalone installer for SSDT, how or when might this be updated?

[ErikEJ]

@brettpostin VS 17.9 preview 1?

[brettpostin]

Yes, SqlPackage 162.1.162.1 is included there, and I can now see the option reflected in SSDT too!

Thanks!