What are the challenges you face when working across database platforms? Take the survey
Options

DevOps process solutions: Stale migrations scripts are biting us

SumantaSumanta Posts: 12 Bronze 1
edited October 1, 2018 4:47PM in SQL Source Control
Due to some issues that we cannot solve for about 6 months, we have a delay between developers creating migration scripts and the code getting merged into master. Further, we are using the SQL scripts generated by DLM Automation Sync step to propagate changes from build server 
Stale migrations scripts are biting us now. We discovered an issue where a developer wrote a migration script that touched a table that had a constraint. However we had merged a different commit that dropped the constraint and pushed in release (x). Then we merged the aforesaid migration script so the constraint was added back in the release (x+1).

We are debating what are the best practices in using migration scripts: Some options are:
1.  Dev does not generate the real migrations scripts. They create the script, and then before merge we create a migration script to autogenerate the dependencies. 
2. We ask the developers to merge into a staging branch first, do reviews later. 
3. ...

There are many other ideas each wilder than the other.

Questions are:
* What are the best practices in this area?
*  If we were to delay migration script generation till the moment that we are ready to merge, is there a way to automate the generation of the migration script?
Tagged:

Answers

  • Options
    The best practice would be to rather than generate the migration script before, do the whole Sync process when you deploy. That way the deployment script will reflect the actual state of your sync target.
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • Options
    SumantaSumanta Posts: 12 Bronze 1
    That is good advice indeed. But @Sergio R that does not save us from the issue of stale migration scripts. How do you overcome the issues posed by delay between developer committing the code and some time later the merge happens and might happen and out of order. 
  • Options
    SumantaSumanta Posts: 12 Bronze 1
    1. developer A develops a migration script to update a row of data (NOT Static)
    2. developer B changes removes, say, a trigger.
    3. B's code is merged.
    4. Release synced.
    5. A's code is merged.
    6. Release synced.

Sign In or Register to comment.