specific changeset change automation migration script

Apologies if this has already been asked but I have not been able to find any information on the following topic. I am investigating whether or not SQL Change Automation is a good fit for my project. The issue I have is that I require the ability to only move over specific changesets with a migration script. So instead of comparing dev to prod and moving all changes , I only want to take the change sets related to the feature I want to deploy between environments. That is, if there are three changesets: 123,124,210,211,212 and I only want to deploy 210,211,212 and NOT the other two , how would I accomplish this? Is automation possible in this manner (in terms of CI/CD with SQL server changes)? Any assistance or pointers to links would be most appreciated. 

Best Answer

  • David AtkinsonDavid Atkinson Posts: 1,406 Rose Gold 2
    edited October 21, 2019 1:00PM Answer ✓
    @MarcoCoro - By the wording of your question I am going to assume that you're using state-based deployments (and not migrations).

    Assuming this is the case, the pattern above is indeed possible by using the right branching strategy. You haven't descibed what your VCS is nor your current branch strategy is, so I'll do my best without this information.

    If the database changes (made by your developers) are being pushed to the "Main" branch, and your intent is to release a subset of the changesets, then you create a branch (say, Release1) from Main. By default this will be an exact copy of Main, so you'll need to revert the changesets that you don't want to include in your release.

    Then when you release, you simply use the Release1 branch as the source, rather than Main (which includes all changes).

    You can also create Release1 based on a historic version of Main, and merge in (ie, cherry pick) the changesets that you want. Either way, you end up with the same result, which is a branch that represents the changes that you want to deploy.
    David Atkinson
    Product Manager
    Redgate Software


  • way0utwestway0utwest CO, USA Posts: 309 Rose Gold 1
    Second vote for David's strategy. With either the state (SQL Source Control) or migrations ( SQL Change Automation in VS or SSMS), there isn't an easy way to break out the features and ensure things work. This is really a VCS task, where you branch off with just certain changes and verify they will compile and deploy.
  •  @way0utwest - thanks for the endorsement. You're right that this isn't just a strategy for state-based deployments. It's just that there are additional complexities to consider when branching and merging migration scripts.

    David Atkinson
    Product Manager
    Redgate Software
Sign In or Register to comment.