title: How should you build your database from source control?
body: <p>There has been some discussion on the SO community wiki about whether database objects should be version controlled. However, <strong>I haven't seen much discussion about the best-practices for creating a build-automation process for database objects.</strong> </p>
  
  <p>This has been a contentious point of discussion for my team - particularly since developers and DBAs often have different goals, approaches, and concerns when evaluating the benefits and risks of an automation approach to database deployment.</p>
  
  <p><strong>I would like to hear some ideas from the SO community about what practices have been effective in the real world.</strong></p>
  
  <p>I realize that it is somewhat subjective which practices are really best, but I think a good dialog about what work could be helpful to many folks.</p>
  
  <p><strong>Here are some of my teaser questions about areas of concern in this topic. These are not meant to be a definitive list - rather a starting point for people to help understand what I'm looking for.</strong></p>
  
  <ol>
  <li>Should both test and production environments be built from source control?<br/>
  
  <ul>
  <li>Should both be built using automation - or should production by built by copying objects from a stable, finalized test environment?</li>
  <li>How do you deal with potential differences between test and production environments in deployment scripts?</li>
  <li>How do you test that the deployment scripts will work as effectively against production as they do in test?</li>
  </ul></li>
  <li>What types of objects should be version controlled? 
  
  <ul>
  <li>Just code (procedures, packages, triggers, java, etc)?</li>
  <li>Indexes?</li>
  <li>Constraints?</li>
  <li>Table Definitions?</li>
  <li>Table Change Scripts? (eg. ALTER scripts)</li>
  <li>Everything?</li>
  </ul></li>
  <li>Which types of objects shouldn't be version controlled?
  
  <ul>
  <li>Sequences?</li>
  <li>Grants?</li>
  <li>User Accounts?</li>
  </ul></li>
  <li>How should database objects be organized in your SCM repository?
  
  <ul>
  <li>How do you deal with one-time things like conversion scripts or ALTER scripts?</li>
  <li>How do you deal with retiring objects from the database?</li>
  <li>Who should be responsible for <em>promoting</em> objects from development to test level?</li>
  <li>How do you coordinate changes from multiple developers?</li>
  <li>How do you deal with branching for database objects used by multiple systems?</li>
  </ul></li>
  <li>What exceptions, if any, can be reasonable made to this process?
  
  <ul>
  <li>Security issues?</li>
  <li>Data with de-identification concerns?</li>
  <li>Scripts that can't be fully automated?</li>
  </ul></li>
  <li>How can you make the process resilient and enforceable?
  
  <ul>
  <li>To developer error?</li>
  <li>To unexpected environmental issues?</li>
  <li>For disaster recovery? </li>
  </ul></li>
  <li>How do you convince decision makers that the benefits of DB-SCM truly justify the cost?
  
  <ul>
  <li>Anecdotal evidence? </li>
  <li>Industry research?</li>
  <li>Industry best-practice recommendations?</li>
  <li>Appeals to recognized authorities?</li>
  <li>Cost/Benefit analysis?</li>
  </ul></li>
  <li>Who should "own" database objects in this model?
  
  <ul>
  <li>Developers?</li>
  <li>DBAs?</li>
  <li>Data Analysts?</li>
  <li>More than one?</li>
  </ul></li>
  </ol>