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?