Migration Script Challenges in a Large, Shared Environment
We are a customer, and have just recently implemented SQLCI for our entire database landscape (have successfully deployed to production using this new process for all of our major databases). The tool is great; however, we’re experiencing some challenges that I wanted to share with you and see if you had any advice or other customers who have had to overcome similar challenges.
We have about 100 full-time developers + offshore/contract developers. We have a pretty complicated database infrastructure with lots of triggers, massive amounts of logic in stored procs, heavy use of link servers and distributed transactions. The biggest issue we’re having is with migration scripts, many developers are having a learning curve in how to properly code the migration scripts. When deploying a new sql build or even trying to check in changes to source control (we use SSC) if there is an error in a migration script we have to run a profiler trace to get a meaningful error (the only thing redgate gives is “error creating temporary databaseâ€) so that the development team can troubleshoot. In addition because of our complex environment it makes our situation trickier because we frequently have deployment/build failures getting escalated as an ‘environment’ issue when it’s really not. This slows everything down and makes a lot of people unhappy. With a team as large as we have there is a huge amount of effort being spent just trying to troubleshoot errors that are related to migration scripts.
I’m considering abandoning using migration scripts with redgate because of this, and am exploring using a different process to apply changes that need to be done with a migration script. If we could get more meaningful error messages from redgate this wouldn’t be as big of a problem for us but as it sits today its painful (running traces just adds to an already heavy load on our dev database environments). Before I started looking at other options (like using powershell to automate the execution of migration scripts prior to deploying a new SQL build with SQLCI), I wanted to reach out and see if anyone had any thoughts on the matter, and perhaps suggestions on how we could perhaps keep our current process in place and make things easier.
We have about 100 full-time developers + offshore/contract developers. We have a pretty complicated database infrastructure with lots of triggers, massive amounts of logic in stored procs, heavy use of link servers and distributed transactions. The biggest issue we’re having is with migration scripts, many developers are having a learning curve in how to properly code the migration scripts. When deploying a new sql build or even trying to check in changes to source control (we use SSC) if there is an error in a migration script we have to run a profiler trace to get a meaningful error (the only thing redgate gives is “error creating temporary databaseâ€) so that the development team can troubleshoot. In addition because of our complex environment it makes our situation trickier because we frequently have deployment/build failures getting escalated as an ‘environment’ issue when it’s really not. This slows everything down and makes a lot of people unhappy. With a team as large as we have there is a huge amount of effort being spent just trying to troubleshoot errors that are related to migration scripts.
I’m considering abandoning using migration scripts with redgate because of this, and am exploring using a different process to apply changes that need to be done with a migration script. If we could get more meaningful error messages from redgate this wouldn’t be as big of a problem for us but as it sits today its painful (running traces just adds to an already heavy load on our dev database environments). Before I started looking at other options (like using powershell to automate the execution of migration scripts prior to deploying a new SQL build with SQLCI), I wanted to reach out and see if anyone had any thoughts on the matter, and perhaps suggestions on how we could perhaps keep our current process in place and make things easier.
Comments
Sorry that migration scripts are causing you some issues. Yes, we're aware that the error handling is not ideal. The error messages are genuine because they reflect what is happening - namely that when deploying the scripts there was a problem and the temporary database could not be created successfully. Although clearly if you have a complicated script then it's going to be difficult to fault find with a vague error message like that.
At the moment we're reviewing how we handle migration scripts so for the short term, there probably won't be an update to fix this. Slightly longer term, there may well be changes as there are probably several areas where we can improve migrations. I wish I could be more helpful by giving you something more certain, but I don't have any concrete information at the moment.
Sorry. I hope this helps a little.
Redgate Software