Granularity of "Block incremental deployment if data loss might occur"

Question

In SQL Server data Tools you have the deployment option "Block incremental deployment if data loss might occur", which I'd wager is a best practice to keep checked.

Lets say we have a table foo, and a column bar which is now redundant - has no dependencies, foreign keys etc etc, and we have already removed references to this column in our data layer and stored procedures as it's simply not used. In other words, we are satisfied that dropping this column will have no adverse effects.

There are a couple of flies in the ointment:

1. The column has data in it
2. The database is published to hundreds of distributed clients, and it could take months for the change to ripple out to all clients

As the column is populated, publishing will fail unless we change the "Block incremental deployment if data loss might occur" option. This option is at the database level, not table level however, and so due to the distributed nature of the clients, we'd have to turn off the "data loss" option for months before all databases were updated, and turn it back on once all clients have updated (our databases have version numbers set by our build).

You may think we could solve this with a pre-deployment script such as

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 unless we turn the "data loss could occur" option off.

I'm simply interested as to what others have done in this scenario as I'd like to have granularity which doesn't currently seem possible.

Tollefson

I've just set that option to False and been aware that it's done and know the consequences. I don't know how you're pushing releases. If you're pushing incrementally (apply package 1, then package 2, etc.) you could turn it off for the one release to drop the column, then turn it back on. If just on an "as needed" basis, I'd consider turning it off and watching the DB changes carefully. – Menzies

Thanks @PeterSchott. Our build creates dacpacs which are downloaded and applied on the client end, but due to the nature of fixes and clients (some clients may not use their machines over winter for instance and get the latest release downloaded when they switch on) the windows to switch the setting off and on for a specific release is a little difficult. If the consensus is not to have the data loss setting set, then that's something I'll have to consider. I've always thought it should be on. – Tollefson

I'd generally agree that having it on can be useful, but especially in your situation you will likely want to have it off. If you understand the general risks it's not a big deal. You can always choose the "generate script" option to review changes if you're concerned about other changes. You can also use an option to view the change report command line or part of the script option to see what could be affected. – Menzies

Answer 1

So I've been accomplishing this task via the following steps:

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

And 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

post-deployment script

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

    drop table #Foo
end

Caveat: Depending on your environment, it might be wiser to depend on versions rather than column & temp table existence in your conditionals

Pentomic

thanks for the response. I've been trying this out and have now got this to work in the way you describe. The key point here is that the table has to have zero rows in it in order for it to be able to modify the table. In the end I didn't check versions, but used information schema views on the table structures to determine whether anything should be changed. Nice one :) – Tollefson

Just read your comment below "This obviously means copying data out and potentially dropping and recreating foreign keys and check constraints etc depending on your structure" This is true - and although that detail may deviate from your original question, is good to know for any future readers. – Pentomic

Doing it this way, you will not be able to rollback the changes – Evaginate

Answer 2

The PreDeployment script doesn't work the way you are hoping to use it because of the order of operations for SSDT:

1. Schema Comparison
2. Script generation for schema difference
3. Execute PreDeployment
4. Excecute generated script
5. Execute PostDeployment.

So of course, the schema difference is identified as part of #2 and appropriate SQL is generated to drop the column (including the check to block on data loss), before your manual pre-deployment script can 'get rid of it'.

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

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 the simple existence of rows will stop the column being dropped. We haven't found any way around this other than manually dealing with the problem outside (and before) the SSDT deployment, using conditional deployment steps based on version numbers.

You mention distributed clients, which implies you have some sort of automated publication/update mechanism. You also mention version numbers as part of the database - could you include in your deploy (before the sqlpackage.exe command I assume you are running) a manual SQL script? This is akin 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 that valid SQL, it's simply pseudo code to imply an idea!

Abominate

Thanks for the reply hanzworld. Your comment "We haven't found any way around this other than manually dealing with the problem outside (and before) the SSDT deployment" pretty much sums up my progress on this, so though I have yet to actually implement this, as it stands I think it's the only option. It's a shame as I really like ssdt, but I guess there are rarely, if ever, any silver bullets :) – Tollefson

Just read that back and I deserve to be down-voted for using the term "silver bullet". Lingo bingo ftw! – Tollefson

do you have a link for the "order of operations" you have stated above, please? – Tollefson

@Fetchezlavache I originally wrote that from experience, but here's a nice walk through of behind the scenes and answers the 'why doesn't a predeployment script affect deployment script' better than I summarised it - blogs.msdn.com/b/gertd/archive/2009/09/14/…. – Abominate

I've now got this to work based on Phills answer. The key is to clear the table of any data in the pre step. This obviously means copying data out and potentially dropping and recreating foreign keys and check constraints etc depending on your structure, but it is do-able, after all. You've been v. helpful in filling in the blanks in the process. – Tollefson