How to increase performance when working with Migration Scripts (TFS)?


we have a shared database linked to our Team Foundation Server with Red Gate Source Control Our workflow is as follows: We make changes to the database, then generate a migration script out of these changes - or set up a blank script for data changes - to include in our setup. We develop in a closed environment so rollout through automatic deployment mechanisms is not possible. We then commit the generated script to the TFS. That's the point where we run into performance issues. Commiting a script takes up to 10 minutes. Tasks "Determining database changes" and "Scripting migration changes" are the ones taking the most time to complete. This was not the case when we first set up our environment, so the question is does commit time increase with more migration scripts being added and is there a way to optimize the process? As of now we have 188 migration scripts already commited. Additionally Red Gate Source Control crashes on a regular basis with "Red Gate App Host stopped working" error, could those problems be connected? We tried optimizing our TFS database, increasing disk space and setting up some maintenance tasks but the issues remain.

Any help is greatly appreciated. Thanks in advance.


Best Answer

  • AlexYatesAlexYates Posts: 264 Rose Gold 2
    Migration scripts are not optimised for your use case. Migration scripts are designed for occasional use, for example when you need to override the default behaviour. (e.g. to handle a column split which SQL Compre can't work out by itself). If you have lots of migration scripts you will hit peformance issues.

    If you want to script out *every* change for deployment you should look at ReadyRoll instead. This is designed to serve your specific use case and will perform much better for you.
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn


  • AMP_DevAMP_Dev Posts: 9 New member
    Hi Alex,

    thanks for you reply. We'll look into that. Indeed it seems to be a better fit to our use case. But with SQL Source Control we had full integration in SSMS. Looks like with ReadyRoll we need to create a Visual Studio solution to handle creating scripts and commiting them to our TFS, right? This could eventually be a problem with some of our developers who are working only in SSMS right now...

    Also, is ReadyRoll only available as part of the SQL Toolbelt or can we license it seperately as a stand alone product?

    Thanks again.

  • AMP_DevAMP_Dev Posts: 9 New member
    Also, is there a way to, kind of, clean up all existing migration scripts, e.g. removing them from the existing scripts view but leaving the objects history intact??
  • ReadyRoll is indeed only available as part of the SQL Toolbelt.

    As for cleaning up migration scripts (in SQL Source Control) - you can delete the most recent migration script through the UI (and you can do that repeatedly, so you can go back as far as you want). This does not affect the object history, because that comes from the version control system for each object's definition (rather than from the migration scripts). If you want to delete all migration scripts, you can just delete the entire `Custom Scripts` folder - it will be recreated next time you create a migration script.

    Having said that, SQL Source Control migration scripts really aren't designed for this use case, and you will continue to run into problems with them - I would strongly recommend investigating moving to ReadyRoll. You can continue making your database changes in SSMS (and indeed, many ReadyRoll users do exactly that), and Alt-Tab to ReadyRoll when you're ready to import those changes into a migration script.
    Development Lead
    Redgate Software
  • By the way, in your first post you also mention crashes with "Red Gate App Host stopped working" - you'll be glad to know that that issue is fixed in the latest frequent updates version of SQL Source Control, 5.8.3.
    Development Lead
    Redgate Software
Sign In or Register to comment.