Built-in migrations feature deprecated in SQL Source Control 7.1.9

LubosMLubosM Posts: 14 Bronze 1
edited January 23, 2020 8:49PM in SQL Source Control
As of version 7.1.9 of SQL Source Control, we have replaced the existing built-in Migrations feature with the superior migrations capability of SQL Change Automation.

Why we’re making this change
The previous built-in migrations feature worked by combining custom migration scripts with SQL Source Control’s native state-based deployments. User feedback has indicated that this approach was confusing to grasp and any issues were tricky to troubleshoot.

The approach taken in SQL Change Automation for SSMS is far simpler as it uses migration scripts exclusively in the deployment process. These migration scripts are auto-generated by SQL Change Automation and can, of course, be customized.

If you have any existing projects that are using the built-in migrations capability, they will not be affected, but there will be no further updates to the feature and we recommend that you use SQL Change Automation to generate migration scripts from SQL Source Control instead.
Tagged:
· Share on Twitter

Comments

  • TheoLTheoL Posts: 17 Bronze 1
    Does this mean those on Toolbelt Essentials essentially lose this functionality completely (for new projects)?  
    · Share on Twitter
  • MayaMalakovaMayaMalakova Posts: 14 Bronze 1
    TheoL said:
    Does this mean those on Toolbelt Essentials essentially lose this functionality completely (for new projects)?  
    We left a backdoor for those that really want to continue using it - you can set AlwaysEnableMigrationsPage to True in the UI options in %LOCALAPPDATA%\Red Gate\SQL Source Control 7\RedGate_SQLSourceControl_CommonUI_UIOptions.xml
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    @TheoL - I have emailed you separately about this.

    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • TheoLTheoL Posts: 17 Bronze 1
    MayaMalakova said:
    TheoL said:
    Does this mean those on Toolbelt Essentials essentially lose this functionality completely (for new projects)?  
    We left a backdoor for those that really want to continue using it - you can set AlwaysEnableMigrationsPage to True in the UI options in %LOCALAPPDATA%\Red Gate\SQL Source Control 7\RedGate_SQLSourceControl_CommonUI_UIOptions.xml
    Unless the feature actively maintains inter-operability with future versions of SQL Compare  and SQL Change Automation, then this is only a temporary solution.
    · Share on Twitter
  • SebazzzSebazzz Posts: 7 New member
    David Atkinson said:
    @TheoL - I have emailed you separately about this.

    Can we keep this discussion public, please?

    This also affects us. We have the toolbelt license, and I'm afraid this means our entire license for SQL Source Control useless for us as well. The backdoor method is only a temporary solution of course.


    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    @Sebazzz - SQL Source Control is still very much supported. If you're using SQL Source Control the obvious option is to carry on using this and simply use SQL Change Automation to generate migrations scripts. Have you got any specific questions on this approach? 

    (We would recommend against using the back door except as a temporary measure)

    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • SebazzzSebazzz Posts: 7 New member
    David Atkinson said:
    @Sebazzz - SQL Source Control is still very much supported. If you're using SQL Source Control the obvious option is to carry on using this and simply use SQL Change Automation to generate migrations scripts. Have you got any specific questions on this approach? 

    (We would recommend against using the back door except as a temporary measure)


    I'm unclear what the value of SQL Source Control is when we would have SQL Change Automation. Apparently from SQL Change Automation you primarily work from the project (apparently it is the SSDT equivalent from Redgate), but other than that you can use manual migrations and version the database.

    --
    The problem is that we currently use the manual migration feature. Not only for data migrations, but for every migration. The resulting migration scripts we stitch together and hand over to MSDeploy, which deploys these changes to environments where no direct SQL connection from the build server is possible. These changes are also deployed together with data migrations from an internally developed tool that allows our clients to translate records in our database.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    @Sebazzz - The workflow in SQL Change Automation is to create a migration script for every change you save into the project. Although this is mitigated the "programmable objects" feature, this can cause a large number of sometimes unnecessary migration scripts that end up running when you eventually deploy. Another difference if you use SQL Change Automation is that changes are shared between developers using these migration scripts. When used with care this is fine, but it's brittle in situations where developers may have made changes to their dev database that have dependencies with the changes being made by the migration script. If a migration script fails to apply to a dev environment, it can be tricky to troubleshoot and to work around. SQL Source Control uses a state-based approach for developer collarboration, which means that the scripts that are applied are custom-generated to work on each development environment, regardless of changes that have been made. Of course if you're a single database developer, or within teams where developers never work on objects that affect each other, this won't be a consideration.

    We plan to publish more help on the differing approaches, outlining the pros and cons of each.
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • SebazzzSebazzz Posts: 7 New member
    David Atkinson said:
    We plan to publish more help on the differing approaches, outlining the pros and cons of each.

    Do you have an update on this, David? Thank you.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    @Sebazzz - this is a good starting point: https://www.red-gate.com/hub/product-learning/sql-toolbelt/a-hybrid-approach-to-database-devops
    Is there any specific information you were hoping for that isn't covered in this article? 
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • SebazzzSebazzz Posts: 7 New member
    My concern is that you now force customers on a SQL Toolbelt Essentials license to a much more expensive license (+$1500 per seat) license. Is that observation correct?
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    @Sebazzz - If you use SQL Source Control, and you intend to use the migrations feature please DM or email me your serial key and we will ensure that SCA migrations is available to you.

    David Atkinson
    Product Manager
    Redgate
    David.Atkinson@red-gate.com

    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • SebazzzSebazzz Posts: 7 New member
    David Atkinson said:
    @Sebazzz - If you use SQL Source Control, and you intend to use the migrations feature please DM or email me your serial key and we will ensure that SCA migrations is available to you.

    David Atkinson
    Product Manager
    Redgate
    David.Atkinson@red-gate.com


    Hi David,
    We were never contacted. This is even more frustrating because on some databases we can't create any migrations at all. SQL Source Control keeps crashing - even when creating a blank script.
    · Share on Twitter
  • AdamYAdamY Posts: 55 Bronze 3
    edited September 30, 2020 7:50PM
    Redgate gave us a 1-year license for SCA to try it out, but afterward we have to pay more $$ since SQL Source Control doesn't allow custom migration script without the backdoor work-around (which might not work for long?). Seems sneaky to move a feature from one product to a more expensive product. And I'm having problems using/understanding SCA with SSC - but that's a different topic.
    · Share on Twitter
  • sgtwickoolsgtwickool Posts: 2 New member
    edited April 14, 2021 11:11AM
    MayaMalakova said:
    TheoL said:
    Does this mean those on Toolbelt Essentials essentially lose this functionality completely (for new projects)?  
    We left a backdoor for those that really want to continue using it - you can set AlwaysEnableMigrationsPage to True in the UI options in %LOCALAPPDATA%\Red Gate\SQL Source Control 7\RedGate_SQLSourceControl_CommonUI_UIOptions.xml

    I tried this and the backdoor appears to have been deprecated. The setting didn't exist in the xml file, so I added it, but it is automatically removed when I open ssms and I cannot access migration scripts.

    So has Red Gate basically removed the ability to use migration scripts with a state-based deployment method? It is now not possible to use the "instead of" migration scripts for changes which require a script, such as adding mandatory columns to tables... These can't be resolved using before/after scripts, along with loads of other changes (e.g. setting up Change Data Capture on tables, with an auditing job).

    Bit of a nuclear change there, don't you think Red Gate? Can you reinstate the backdoor so we can have a bit of time to consider moving to a migrations-based deployment approach (required to use SQL Change Automation)?

    Edit: I tried the backdoor approach again and it seems to have worked (it didn't delete the setting from the xml file this time)! Hopefully this backdoor will be around for a while though, as we might not have time to move to a migration-based approach for a while. Still seems like a shame to force users to adopt one deployment approach, after providing flexibility in previous versions.
    · Share on Twitter
  • Kendra_LittleKendra_Little Posts: 139 Gold 3
    Hi @sgtwickool,

    Thanks for getting in touch with your concerns. I've emailed you some options for proceeding outside of this thread. 

    Cheers,
    Kendra
    · Share on Twitter
Sign In or Register to comment.