title: AutoDeploy SQL changes with TeamCity (alternative to Redgate?)
body: <p>I'm creating CI and Auto Deployment for .NET applications (source controlled with SVN) for our testing environment with TeamCity and all is going well. I am at the stage where I want to automate the execution of db scripts. I've done some searching and read several articles, such as <a href="http://www.troyhunt.com/2011/02/automated-database-releases-with.html" rel="nofollow">http://www.troyhunt.com/2011/02/automated-database-releases-with.html</a> (troyhunt's blogs have been invaluable!), and many seem to point at using RedGate software to manage and deploy changes. I am a bit apprehensive to let a new tool handle script generation so am wondering if there are other accepted methods out there worth investigating.</p>
  
  <p>Our current standards for solution design include a Project containing DB related files like our orm db. It also contains a series of folders which contain our change scripts as illustrated below.</p>
  
  <pre><code>MySolution
   &gt; MySolution.Data
      &gt; 3.0 Scripts
         * 01 - UpdateUserTable.sql
         * 02 - UpdateRoleTable.sql
      &gt; 3.1 Scripts
         * 01 - CreateJobTable.sql
         * 02 - InsertJobTypes.sql
         * 03 - AndSoOn.sql
  </code></pre>
  
  <p>I believe this structure is important as although we want to auto deploy to our testing environment upon checkin, we deploy actual release far less frequently and I'm not sure if RedGate's compare tools could handle the level of changes that sometimes happen. That's why I'm much more comfortable using version folders for the scripts and then incrementing the minor upon release to keep the script versions neatly contained.</p>
  
  <p>Having this pre-existing structure, I was inclined to keep it and add a TeamCity build definition which could apply db scripts upon successful deployments. I imagined I could get TeamCity to identify newly added .sql files to SVN within the expected path and then execute these on the server, but perhaps I was expecting too much out of the box. I have implemented this sort of solution in the past with TFS and MSBUILD and custom build steps, but am totally new to the TeamCity way.</p>
  
  <p>Is there already a process of achieving something like this? It seems to me to be quite a normal implementation, I've just not been able to find out how yet. Or is the RedGate solution the standard we should be going for?</p>