0

We currently use Database projects during automated builds to ensure our test and development databases are in sync with source code.

During the publish profile, we have noticed a problem which isn't totally covered by the workflow we're currently using and are curious the best way to manage this.

Problem:

We have a re-working of the database schema and decide that a column (name) from a table (user) should be moved to a new table (userdetail). The new userdetail table, requires the existing content of name column on table user, but we also want to drop the column name from table user. To ensure our database project is up to date.

The problem is that if we try to populate the userdetail table in pre-deployment, the userdetail table doesn't exist. We can create the table in pre-deploy but then the db project tries to create it again during deployment and fails. If we try to populate userdetail post deployment, the name column has been dropped and we can't use this to populate userdetail.

At present we have two possible solutions which I can see.

Possible Solution 1

Leave the name column in the user table, which means the population of userdetails runs ok, but we're left with the column needing to be removed manually post deployment, which is prone to error.

Possible Solution 2

Create a temporary table to capture the information from user during pre-deployment and store it for use during a post-deployment script. Drop the temporary table during post deployment.

Option 2 seems the best at the moment, but it's a pain to have to manually store and use data later in this way. Is there a recommended method to cater for this eventuality?

dougajmcdonald
  • 19,231
  • 12
  • 56
  • 89
  • It sounds like your publish process _updates_ the database schema (rather than recreating it). Is that right? Does your post-deployment insert any test data or was the existing data added by your developers & testers? – Keith Sep 15 '14 at 13:12
  • The publish profile updates rather than creates yes, this is a legacy system so we're only after applying incremental changes during the publish. Our publish for test typically starts with a copy of the live data as the time of the last release, updates the schema to the latest version and then in post-deploy, inserts any seeding type data we'd expect to be present after a release. – dougajmcdonald Sep 15 '14 at 13:43

1 Answers1

0

I think your best bet is going to be option #2. The "DROP" statement would be generated when you run the publish command. That wouldn't take into account any pre or post deploy scripts so the column would be dropped before you had a chance to run your conversion script. You really only have the option of doing this in two different builds or creating some sort of staging table pre-deploy and dropping it post-deploy.

Peter Schott
  • 4,521
  • 21
  • 30
  • This is my thinking too, at the moment I have that process working as I've described, but was hoping there was a more sensible way of setting this up. May submit a request to MS as it would be nice to be able to tag columns / tables with a 'post-deployment-dependency' to perhaps have it automate the creation of temp table and retrieval of data from it. – dougajmcdonald Sep 16 '14 at 10:30