Edited Migration Scripts are not Run in CI

moesother

Problem:
Changes to migrations scripts are not being applied to the continuous integration environment.

How to reproduce:
1. Create a migration script to update a row on any table with un-versioned data in your versioned database
2. Push the change
3. Confirm that the data automatically was updated in your CI database
4. Edit the migration script to update the same row with slightly different data
5. The change does not get applied to the CI database

Comments:
I can understand why this might happen. One cannot assume that all migrations scripts can safely be run multiple times on the same database. Therefor some users may not want edited migration scripts to be rerun by default.

Developers will make mistakes that have to be corrected. We don’t want to run the incorrect script in production and then have a second script to correct it. Therefor automation must have some way to rerun a migration script without someone having to manually delete records from the DeploymentMetadata table. What is the solution / workaround?

We are using the latest version of SQL Source Control and DLM Automation as of Dec, 2016.

Rob C

As you correctly point out, migration scripts are designed to be run only once because we can't know if it is safe to run a script multiple times. This means that we have two options, and you have mentioned both of them in your post. You can either add a second script to update the data or you can remove the entry from the RedGateLocal.DeploymentMetadata table on the server in order to trick it into executing the script for a second time. There isn't currently any other way to do it. Sorry.

I notice that there is a suggestion for this exact thing on our UserVoice site - https://redgate.uservoice.com/forums/39 ... on-scripts - if this wasn't posted by you then I recommend that you vote for this suggestion and add any comments you may have.

moesother

Thanks for confirming this for me. It seems like quite the oversight since work in agile teams is an iterative process.

Here's a possible workaround:

Except for very special cases all of our database scripts are re-runnable. They can be run multiple times on the same database without causing harm or errors.

Perhaps there's a way I can automate the deletion recent records from the DeploymentMetadata table every time a build is triggered. At least then I don't have to ask my developers to manually go in to tables on the different environments and find records to delete. (Also note that it not possible to copy the migration script id in SQL Source Control to the clipboard)

That feature request is mine

Rob C

Thanks for the feedback.

If you're using a continuous integration server like TeamCity for instance, then you could add a step to the build process that runs a "DELETE FROM RedGateLocal.DeploymentMetadata WHERE..." query on the database, perhaps using sqlcmd.exe. Then when you deploy the build the migration script will run again. Is that feasible?

moesother

I added a step in Jenkins (our build server) in both our dev and test environments to run SQLCMD with "DELETE FROM RedGateLocal.DeploymentMetadata". It wipes the table every build. So far it hasn't caused any problems but I would be interested to know if it has drawbacks other than that the scripts are reran each time.