I am a (windows) developer trying to determine the best approach for publishing DB schema changes requiring data transformations as part of my code releases. I have tried googling but did not find quite my question answered. (My google might be weak, though.) (Also, if this question belongs elsewhere, please let me know.)

We are using automated deployments with Octopus and I would like to have a foolproof way of deploying DB changes as part of this. At the moment, we are using a VS DB project/dacpac and while that works in most cases, we occasionally encounter situations where we would like to transform data/execute scripts as part of the DB deployment and that cannot be handled with dacpac deployments.

Example: We have a SubmittedApplication table with foreign keys to users, suppliers, etc. Most of this data is imported from other systems and is not 'ours' as such. Now stuff is changing upstream and data can be deleted from these tables at the source. As the data is not ours, we have decided to reflect these deletions in our imported tables. Which presents a problem wrt. the foreign keys from the data we do own; the applications. We can accept that the application table contains copies of the data instead of FKs and so would like, in a deployment, to not only change the DB schema 'destructively' as part of the deployment but, interweaved with that, copy data from the existing FKs into the new DB schema, to migrate the existing applications.

As far as I can tell, if we stay with dacpacs, we have do this in two steps: Deploy an expansion of the existing schema, run a post-deployment script to copy data, and, in a subsequent deployment, remove the vestigial schema parts. It grates a bit, having the old columns hanging around, but this seems doable.

Otherwise, we could go with SQL scripts that the deployment process rolled onto the DB. As these would be both schema changes and transforms/data migrations, they would be manually created and curated, as far as I can tell. As I am quite good at forgetting things I dont automate, I am worried about the manual part of this. My present setup, where the dacpac just Does The Right Thing, seems more robust. But, alas, also lacking..

So, my question is whether there is some tool or best practice for handling these situations?

  • If you are a windows developer, have you ever used software storing data in sql server, like sharepoint or tfs or wsus? I am not snippy here, but the way those handle schema changes are a good example how at least those significant software projects handle the issue. – TomTom May 8 '16 at 8:23
  • I might be bad at google, but I cannot seem to find any information about this at a level that would help me out. Can you point me at something that would inform me, as a developer, how to do this? Without even having seen this, I would also be concerned whether these enterprise systems' angle on this would be too cumbersome for me to leverage productively. But I am willing to be convinced otherwise. – R Andersen May 16 '16 at 5:54
  • You mean teaching programming? Because if you ever install sharepoint or maintain TFS you get a good idea how that is handled in a professional setup (i.e. a separate program does the updating) Tools are third party (not done here) - but there is not a lot, all I have ever seen is self built. Maintain db version numbers (extended property) then have folders with update scripts and a program determining which to execute. – TomTom May 16 '16 at 6:12
  • Hm. Ok then. I have never maintained TFS or installed SP, so I have no experience with what they do. I'll look at rolling my own logic for this, then. (Apologies for the late respones, I receive the notification emails with days lapse.) – R Andersen May 19 '16 at 6:50
  • Grab a copy of TFS eval, install it on a virtual machine. Look how the installer works with the database. Copy that approach. – TomTom May 19 '16 at 6:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.