title: SQL Server database change workflow best practices
body: <p><strong>The Background</strong></p>
  
  <p>My group has 4 SQL Server Databases: </p>
  
  <ul>
  <li>Production</li>
  <li>UAT</li>
  <li>Test</li>
  <li>Dev</li>
  </ul>
  
  <p>I work in the Dev environment. When the time comes to promote the objects I've been working on (tables, views, functions, stored procs) I make a request of my manager, who promotes to Test. After testing, she submits a request to an Admin who promotes to UAT. After successful user testing, the same Admin promotes to Production. </p>
  
  <p><strong>The Problem</strong></p>
  
  <p>The entire process is awkward for a few reasons.</p>
  
  <ol>
  <li>Each person must manually track their changes. If I update, add, remove any objects I need to track them so that my promotion request contains everything I've done. In theory, if I miss something testing or UAT should catch it, but this isn't certain and it's a waste of the tester's time, anyway.</li>
  <li>Lots of changes I make are iterative and done in a GUI, which means there's no record of what changes I made, only the end result (at least as far as I know).</li>
  <li>We're in the fairly early stages of building out a data mart, so the majority of the changes made, at least count-wise, are minor things: changing the data type for a column, altering the names of tables as we crystallize what they'll be used for, tweaking functions and stored procs, etc.</li>
  </ol>
  
  <p><strong>The Question</strong></p>
  
  <p>People have been doing this kind of work for decades, so I imagine there have got to be a much better way to manage the process. What I would love is if I could run a diff between two databases to see how the structure was different, use that diff to generate a change script, use that change script as my promotion request. Is this possible? If not, are there any other ways to organize this process? </p>
  
  <p>For the record, we're a 100% Microsoft shop, just now updating everything to SQL Server 2008, so any tools available in that package would be fair game.</p>
  
  <hr>
  
  <p>I should clarify I'm not necessarily looking for diff tools. If that's the best way to sync our environments then it's fine, but if there's a better way I'm looking for that.</p>
  
  <p>An example doing what I want really well are migrations in Ruby on Rails. Dead simple syntax, all changes are well documented automatically and by default, determining what migrations need to run is almost trivially easy. I'd love if there was something similar to this for SQL Server. </p>
  
  <p>My ideal solution is 1) easy and 2) hard to mess up. Rails Migrations are both; everything I've done so far on SQL Server is neither.</p>