How to increase performance when working with Migration Scripts (TFS)?
AMP_Dev
Posts: 9 New member
Hi,
we have a shared database linked to our Team Foundation Server with Red Gate Source Control 5.6.1.5788. 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.
Matthias
we have a shared database linked to our Team Foundation Server with Red Gate Source Control 5.6.1.5788. 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.
Matthias
Best Answer
-
AlexYates
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
Answers
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.
Matthias
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.
Redgate Software
Redgate Software