Granularity "Block incremental deployment if data loss can occur"

Question

In SQL Server data tools, you have the deployment option “Block incremental deployment if data loss can occur”, which in my opinion is the best practice for validation.

Let's say that we have a table foo, and the column row, which is now redundant, has no dependencies, foreign keys, etc. etc., and we have already deleted references to this column in our data layer and stored procedures, because it is simply not used. In other words, we are satisfied that resetting this column will not have negative consequences.

There are several flies in the smear:

• The column has data in it.
• The database is published in hundreds of distributed customers, and it can take months for the change to ripple all customers

As the column fills, the publication will fail if we do not change the option "Phased deployment of the block, if data loss is possible." However, this parameter is at the database level, and not at the table level, and therefore, due to the distributed nature of the clients, we will have to disable the "data loss" parameter a few months before all databases are updated, and return them after the update all customers (our databases have version numbers set by our assembly).

You might think that we can solve this problem with a pre-deployed script, for example

if exists (select * from information_schema.columns where table_name = 'foo' and column_name = 'bar') BEGIN alter table foo drop constraint DF_foo_bar alter table foo drop column bar END 

But again this fails if we do not disable the "data loss" option.

I'm just curious what others did in this scenario, as I would like to have a granularity that is not currently possible.

Answer 1

So, I completed this task by following these steps:

1) Since we are going to make the table #Foo, be sure to drop this table before moving forward, if it exists.
2) In a pre-deployment script: if a column exists, create a temporary table #Foo and select all rows from Foo to #Foo.
3) Remove the column from #Foo
4) Delete all lines in Foo (now there will be no data loss, since there is no data)
5) In a post-deployment script: If #Foo exists, select all lines from #Foo to Foo
6) Drop table #Foo

And the code:

pre-deployment script

 if(Object_ID('TempDB..#Foo') is not null) begin drop table #Foo end if exists ( select * from sys.columns where Name = 'Bar' and Object_ID = Object_ID('Foo') ) begin select * into #Foo from Foo alter table #Foo drop column Bar -- Now that we've made a complete backup of Foo, we can delete all its data delete Foo end 

after deploying the script

 if(Object_ID('TempDB..#Foo') is not null) begin insert into Foo select * from #Foo drop table #Foo end 

Caution: depending on your environment, it may be wiser to rely on the version rather than the existence of columns and temporary tables in your conditional expressions

Answer 2

The PreDeployment script does not work the way you hope to use it due to the order of operations for SSDT:

• Circuit comparison
• Script generation for difference schemes
• Performing a preset
• Invoked by excecute script
• Running PostDeployment.

So, the schema difference is identified as part # 2, and the corresponding SQL is generated to remove the column (including checking for data loss locks) before your manual manual deployment script can “get rid” of this.

If you look at the script generated behind the scenes to detect (and therefore block) the possible loss of data, it checks to see if there are any lines, running something line by line:

 IF EXISTS (select top 1 1 from [dbo].[Table]) RAISERROR ('Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) 

This means that the mere existence of rows will stop the column from falling. We did not find any way around this except manually solving the problem outside (and before) deploying SSDT using conditional deployment steps based on version numbers.

You mentioned distributed clients, which implies that you have some kind of automatic publishing / updating mechanism. You also specify version numbers as part of the database — could you include the SQL script guide in your deployment (before the sqlpackage.exe command, which I assume you are running)? This is similar to what we do (ours is in Powershell, but you get the gist):

 IF VersionNumber < 2.8 BEGIN ALTER TABLE X DROP COLUMN Y END 

Disclaimer: In no way is true SQL, it's just pseudo code that implies an idea!